MySQL 触发器无法更新触发器分配到的同一个表中的行。建议的解决方法?
MySQL 目前不支持更新触发器分配到的同一个表中的行,因为调用可能会递归。有人对好的解决方法/替代方案有建议吗?现在我的计划是调用一个存储过程来执行我在触发器中真正想要的逻辑,但我很想听听其他人如何解决这个限制。
编辑:根据要求提供更多背景信息。我有一个存储产品属性分配的表。当插入新的父产品记录时,我希望触发器在同一个表中为每个子记录执行相应的插入。这种非规范化对于性能来说是必要的。 MySQL 不支持这一点并抛出:
无法更新存储函数/触发器中的表“mytable”,因为它已被调用此存储函数/触发器的语句使用。
关于该问题的长时间讨论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.
Edit: 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:
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.
Thanks in advance!
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(5)
实际上,您可以在与触发器相同的表中增加行。您链接的线程甚至有解决方案。
例如:
You can actually up the rows in the same table as the trigger. The thread you linked to even has the solution.
For example:
我想你可以在触发器中调用存储过程。然而,如果您想更新正在更改的同一记录中的某些字段(例如updatedby或lastupdated列),那么您可以根据参考手册在beofre触发器中执行此操作。 http://dev.mysql.com/doc/refman/ 5.0/en/trigger-syntax.html
这是触发器的常见操作,我发现很难相信它不受支持。
I suppose you could call the stored proc in your trigger. HOwever, if you want to update some fields in the same records that you are changing (such as an updatedby or lastupdated column) then you can do this in a beofre trigger according to the refernce manual. http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html
This is a common operation for triggers and I find it difficult to believe it isn't supported.
如果您想更新在触发器函数中读取的列,那么作为解决方法,您可以将该列放入单独的表中。
If you want to update column that you don't read in trigger function, then as a workaround, you could put that column into separate table.
你实际上可以这样做
以下是相同的示例
You can actually do that
The below is an example for same
这对我有用:D
在之前/更新上。
This worked for me :D
On Before / Update.