Mysql表不支持创建多个触发器。但是我有三个触发器用于三个更新查询,我如何使用单个触发器运行这些触发器,
错误:#1235-此版本的MySQL还不支持"multiple"一个表的具有相同操作时间和事件的触发器
感谢
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 ;
只需将三个触发器的操作包含在一个触发器中:
CREATE TRIGGER (...)
FOR EACH ROW
BEGIN
SET NEW.yeild = (...) ;
SET NEW.temp = (...) ;
SET NEW.kiln = (...) ;
END ;