Mysql three TRIGGERs to single trigger

Question

My Mysql Table doesn't support create multiple triggers . but i have 3 triggers for three update queries, how can i run these triggers using single trigger,

ERROR :#1235 - This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

thanks

 CREATE TRIGGER `update_yeild2` BEFORE UPDATE ON `today_plan`
 FOR EACH ROW SET NEW.yeild = COALESCE((SELECT kiln_master.yeild 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0)


CREATE TRIGGER `update_yeild1` BEFORE UPDATE ON `today_plan`
 FOR EACH ROW SET NEW.temp = COALESCE((SELECT kiln_master.temp 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0)

CREATE TRIGGER `update_yeild0` BEFORE UPDATE ON `today_plan`
 FOR EACH ROW SET NEW.kiln = COALESCE((SELECT kiln_master.kiln 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0)

UPDATED CODE :

CREATE TRIGGER `update_yeild2` BEFORE UPDATE ON `today_plan`
 FOR EACH ROW 

BEGIN

SET NEW.yeild = COALESCE((SELECT kiln_master.yeild 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);


  SET NEW.temp = COALESCE((SELECT kiln_master.temp 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);

  SET NEW.kiln = COALESCE((SELECT kiln_master.kiln 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);

END

thanks

Working Code : thanks RandomSeed

delimiter //

CREATE TRIGGER `update_yeild2` BEFORE UPDATE ON `today_plan`
 FOR EACH ROW 

BEGIN

SET NEW.yeild = COALESCE((SELECT kiln_master.yeild 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);


  SET NEW.temp = COALESCE((SELECT kiln_master.temp 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);

  SET NEW.kiln = COALESCE((SELECT kiln_master.kiln 
                   FROM kiln_master
                   WHERE NEW.itemno = kiln_master.item  
                   AND  NEW.pattern = kiln_master.pattern
                   LIMIT 1), 0);

END

//
delimiter ;

score 1 · Accepted Answer · answered Sep 03 '13 at 07:38

1

Just include the three triggers' operations in one single trigger:

CREATE TRIGGER (...)
FOR EACH ROW
BEGIN
    SET NEW.yeild = (...) ;
    SET NEW.temp = (...) ;
    SET NEW.kiln = (...) ;
END ;

answered Sep 03 '13 at 07:38

RandomSeed

29,301
6
52
87

Oh now I get it, you were probably looking for the `BEGIN ... END` thingy. – RandomSeed Sep 03 '13 at 07:40
#1064 - 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 '' at line 10 – 9pixle Sep 03 '13 at 08:55
@Prabath This is a new error (syntax error). Please post your current code. – RandomSeed Sep 03 '13 at 08:56
thanks. i have added my current code into main post.. please refer. – 9pixle Sep 03 '13 at 08:59
Have you wrapped your trigger definition between proper `DELIMITER` clauses? When defining multi-statements procedures (or triggers in this case), you need to tell the parser not to interpret the `;` as the end of your defining statement. Check [this question](http://stackoverflow.com/questions/1346637/what-does-delimiter-do-in-a-trigger) for more information. Could this be the cause? – RandomSeed Sep 03 '13 at 09:07
See [this example](http://stackoverflow.com/questions/1102109/mysql-delimiter-syntax-errors) too. – RandomSeed Sep 03 '13 at 09:08
thnks RandomSeed . DELIMITER is the missing part.. now working.. :) :) :) – 9pixle Sep 03 '13 at 09:31

Mysql three TRIGGERs to single trigger

1 Answers1