Mysql 三触发器改为单触发器

Question

我的Mysql表不支持创建多个触发器。但我有 3 个触发器用于三个更新查询，我如何使用单个触发器运行这些触发器，

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

谢谢

 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)

更新代码:

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

谢谢

工作代码:感谢 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 ;

Answer 1

只需将三个触发器的操作包含在一个触发器中即可:

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