sqlite触发器会触发其他触发器吗？ [英] Do sqlite triggers trigger other triggers?

问题描述

我试图在sqlite中实现MySQL功能 ON UPDATE CURRENT_TIMESTAMP。
我的想法是使用这样的触发器：

I am trying to implement the equivalent of the "ON UPDATE CURRENT_TIMESTAMP" MySQL feature in sqlite. My idea it to use a trigger like this:

CREATE TRIGGER last_update_trigger
AFTER UPDATE
ON mytable
FOR EACH ROW
BEGIN
UPDATE mytable SET last_update = CURRENT_TIMESTAMP WHERE id = old.id;
END

但这是有问题的。每次对此表的记录进行更新时，触发器都会在同一记录上触发新的更新。这应该一次又一次触发触发器，导致无限循环更新。

But there's a problem with this. Each time an update occurs on a record of this table, the trigger triggers a new update on this same record. This should trigger the trigger again, and again, leading to an infinite loop of updates.

这真的会发生什么吗？触发器中的更新是否会再次触发触发器？
是否可以避免在触发器内触发触发器？

Is this really what will happen? Will the update in my trigger trigger the trigger again? Can I avoid triggering of triggers within triggers?

推荐答案

实际上，我们在谈论SQLite，是吗？最初，不专门支持递归触发器（上面怀疑有无限循环），以防止出现此问题。现在支持它们，但默认情况下已将其关闭。从理论上讲，您可以使用 PRAGMA recursive-triggers = true 将其重新打开。

Actually, we're talking about SQLite, yes? Originally, recursive triggers (the infinite loop suspected above) were NOT supported specifically to prevent this problem. They're now supported, but turned off by default. You can, in theory, turn them back on with "PRAGMA recursive-triggers = true".

马口在这里： SQLite在线文档

这篇关于sqlite触发器会触发其他触发器吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！