MySQL 触发器不能更新触发器分配到的同一个表中的行.建议的解决方法? [英] MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?

问题描述

MySQL 当前不支持更新触发器分配到的同一个表中的行，因为调用可能会递归.有没有人对一个好的解决方法/替代方法有建议?现在我的计划是调用一个存储过程，在触发器中执行我真正想要的逻辑，但我很想听听其他人是如何解决这个限制的.

MySQL doesn't currently support updating rows in the same table the trigger is assigned to since the call could become recursive. Does anyone have suggestions on a good workaround/alternative? Right now my plan is to call a stored procedure that performs the logic I really wanted in a trigger, but I'd love to hear how others have gotten around this limitation.

应要求提供更多背景信息.我有一个存储产品属性分配的表.当插入新的父产品记录时，我希望触发器为每个子记录在同一个表中执行相应的插入.这种非规范化对于性能来说是必要的.MySQL 不支持这个并抛出:

A little more background as requested. I have a table that stores product attribute assignments. When a new parent product record is inserted, I'd like the trigger to perform a corresponding insert in the same table for each child record. This denormalization is necessary for performance. MySQL doesn't support this and throws:

无法更新存储函数/触发器中的表mytable"，因为它已被调用此存储函数/触发器的语句使用. 关于MySQL 论坛 基本上导致:使用存储过程，这是我现在使用的.

Can't update table 'mytable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. A long discussion on the issue on the MySQL forums basically lead to: Use a stored proc, which is what I went with for now.

提前致谢！

推荐答案

您实际上可以将同一个表中的行作为触发器.您链接的线程甚至有解决方案.

You can actually up the rows in the same table as the trigger. The thread you linked to even has the solution.

例如:

TestTable ( id / lastmodified / random )

create trigger insert_lastmod
before insert on TestTable
for each row
set NEW.lastmodified = NOW();

insert into TestTable ( `random` ) values ( 'Random' );

select * from TestTable;
+----+---------------------+---------------------+
| id | lastmodified        | random              |
+----+---------------------+---------------------+
|  1 | 2010-12-22 14:15:23 | Random              |
+----+---------------------+---------------------+

这篇关于MySQL 触发器不能更新触发器分配到的同一个表中的行.建议的解决方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！