MySQL Forums
Forum List  »  Triggers

Trigger Error
Posted by: Ohad Weiss
Date: May 10, 2014 05:07AM

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;

Options: ReplyQuote


Subject
Views
Written By
Posted
Trigger Error
2196
May 10, 2014 05:07AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.