插入后和更新后的MySQL触发器

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。在t2上更新后,我在t1和tr2上插入后创建了两个触发器tr1。在两个表中我更新表t2。所以,它抛出一个错误,说t2表已经在另一个触发器中得到更新,所以不能再次更新它。

Please let me know if anyone has faced this type of problem and fixed it.

如果有人遇到此类问题并修复它,请告诉我。

Thanks in advance! MySQL DBA.

提前致谢! MySQL DBA。

2 个解决方案

#1

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.

因为在触发器中你无法实际更新表,即已经更新的表,这是正确的。

#2

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

#1