MySQL 插入后和更新后触发器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/843031/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL After Insert and After Update Trigger
提问by MySQL DBA
I have two tables t1 and t2. i have created two triggers tr1 after insert on t1 and tr2 after update on t2. in both the tables i m updating table t2. so, it is throwing an error saying the t2 table is already getting updated in another trigger so can not update it again.
我有两个表 t1 和 t2。我在 t1 上插入后创建了两个触发器 tr1，在 t2 上更新后创建了 tr2。在两个表中，我都在更新表 t2。所以，它抛出一个错误，说 t2 表已经在另一个触发器中更新，所以不能再次更新它。
Please let me know if anyone has faced this type of problem and fixed it.
请让我知道是否有人遇到过此类问题并修复了它。
Thanks in advance! MySQL DBA.
提前致谢！MySQL 数据库管理员。
采纳答案by MySQL DBA
I have fixed this problem, there was a problem as i was trying to update and insert on same table tb1 from two triggers but Mysql does not allowed the same table to be modified i suppose.
我已经解决了这个问题，当我试图从两个触发器更新和插入同一个表 tb1 时出现问题，但我想 Mysql 不允许修改同一个表。
Thanks
谢谢
回答by shylent
If you want to modify the data that is being updated or inserted, you should use a BEFORE UPDATE and/or BEFORE INSERT trigger and then make use of NEW alias (it references the row, that is being inserted or the row as it will look after the update is applied) - you can actually modify the fields that are being inserted. OLD alias references the row before the update is applied or the row before it is deleted (in triggers that fire on delete).
如果您想修改正在更新或插入的数据，您应该使用 BEFORE UPDATE 和/或 BEFORE INSERT 触发器，然后使用 NEW 别名（它引用正在插入的行或行，因为它看起来像应用更新后） - 您实际上可以修改正在插入的字段。OLD 别名引用应用更新之前的行或删除之前的行（在删除时触发的触发器中）。
So probably for trigger tr2 you need to something along the lines of:
因此，对于触发器 tr2，您可能需要执行以下操作：
DELIMITER $$
CREATE TRIGGER tr2 BEFORE UPDATE ON t2
FOR EACH ROW BEGIN
SET NEW.field1 = some_value_you_want_to_set_it_to;
END;
$$
DELIMITER ;
Because in a trigger you can not actually UPDATE a table, that is being already updated, thats right.
因为在触发器中，您实际上无法更新表，该表已被更新，没错。
