CODEWITHSUNDEEP
mysqldatabasetriggers
Ratatouille
Ratatouille

Reputation: 1462

Trigger in mysql complain

This is refrence to my earlier question

rails callbacks not getting executed

Now since the framework is not working some time I rather thinking of writing a trigger to do this

so my trigger look like this 

DELIMITER $$
CREATE TRIGGER sales_earning AFTER INSERT ON sales_transactions  FOR EACH ROW  
BEGIN
   DECLARE earning INT;
   SET earning = (select sales_earning from payouts where id = NEW.payout_id);
   earning = earning + NEW.amount   
   UPDATE payouts SET sales_earning = earning where id = NEW.payout_id ;
END $$
DELIMITER ;

Anyone has a clue as what the issue is with the trigger It complaining

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= earning + NEW.amount ;

FYI there is parent - child relationship between payouts and sales_transactions

Any helps

Upvotes: 1

Views: 78

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

You are missing a ; after earning = earning + NEW.amount

And you should do:

SET earning = (select sales_earning from payouts where id = NEW.payout_id) + NEW.amount;

UPDATE:

My answer points to what is wrong with your trigger, but if that's all you want to do with earnings, you should simply do it like eggyal's answer is pointing out

Upvotes: 0

eggyal
eggyal

Reputation: 125865

CREATE TRIGGER sales_earning AFTER INSERT ON sales_transactions FOR EACH ROW  
  UPDATE payouts
  SET    sales_earning = sales_earning + NEW.amount
  WHERE  id = NEW.payout_id
;

Upvotes: 1

Related Questions