Trigger Error
Hi
i try to write a trigger that according to a value update a record in another table, or create a record in another table. I get an error that the mySQL doesn't support multiple triggers with the same action. What am I doing wrong?
here is the trigger code:
CREATE TRIGGER `xxx`.`test`
BEFORE INSERT
ON xxx.portfolio_transactions
FOR EACH ROW
BEGIN
DECLARE vExist int(1);
DECLARE nQty double;
SET vExist = 0;
SELECT 1
INTO vExist
FROM portfolio_balance
WHERE compid = new.compid
AND portfolio_id = new.portfolio_id
AND share_id = new.share_id
AND qty <> 0;
IF vExist = 1
THEN
CASE trans_type
WHEN 'BUY'
THEN
SET nQty = new.qty;
WHEN 'SEL'
THEN
SET nQty = new.qty * -1;
ELSE
SET nQty = new.qty;
END CASE;
UPDATE portfolio_balance
SET qty = qty + nQty
WHERE compid = new.compid
AND portfolio_id = new.portfolio_id
AND share_id = new.share_id;
-- CASE trans_type
-- WHEN 'BUY'
-- THEN
-- INSERT INTO xxx.portfolio_balance(compid,
-- portfolio_id,
-- share_id,
-- buy_qty,
-- buy_rate,
-- buy_commission,
-- sell_qty,
-- sell_rate,
-- sell_commission,
-- qty_balance,
-- gross_revenue,
-- tax_revenue,
-- dividend_rate,
-- dividend_gross_amount,
-- dividend_tax_rate,
-- dividend_tax_amount,
-- dividend_net_amount,
-- revenue_tax_rate,
-- revenue_tax_amount,
-- revenue_net_amount,
-- insert_date,
-- insert_userid,
-- update_date,
-- update_userid,
-- program,
-- portfolio_rec_no)
-- VALUES (new.compid,
-- new.portfolio_id,
-- new.share_id,
-- new.qty,
-- new.rate,
-- new.commission,
-- 0,
-- 0,
-- 0,
-- new.qty,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- 0,
-- now(),
-- new.insert_userid,
-- '',
-- '',
-- new.program,
-- 99);
-- END CASE;
END IF;
END;