samedi 28 février 2015

error on adding if else statement inside trigger mysql

I am new to triggers in mysql and found them really useful.I want a help regarding the trigger i am creating. First i created trigger on update event as follows:



CREATE TRIGGER `after_mytable_update` BEFORE UPDATE ON `my_table`
FOR EACH ROW SET NEW.total_pts=NEW.pts_1+NEW.pts_2+NEW.pts_3,
NEW.old_pts=OLD.total_pts


It worked fine now I need to add if/else inside my trigger so i dropped and recreated trigger as follows:



CREATE TRIGGER `after_mytable_update` BEFORE UPDATE ON `my_table`
FOR EACH ROW SET NEW.total_pts=NEW.pts_1+NEW.pts_2+NEW.pts_3,
NEW.old_pts=OLD.total_pts;
IF (NEW.total_pts>=0) THEN
UPDATE 'my_table' SET NEW.level='my_value';
END IF;


But it is giving error #1064. I need to set total_pts value outside if/else statement but I am new to triggers so can not figure out my mistake.


Aucun commentaire:

Enregistrer un commentaire