有没有一种方法可以防止在删除和/或插入触发器中触发更新触发器? [英] Is there a way to prevent update triggers from being triggered within a delete and/or insert trigger?

问题描述

长话短说，在SQLite中，我有一个sortOrder字段，该字段负责处理表中项目的可变排序顺序.我有触发条件:

Long story short, in SQLite, I have a sortOrder field that takes care of, well, a changeable sort order of items in a table. I have triggers that:

在Inesrt之后:如果所有sortOrder字段大于所插入字段的+1，则触发器会将其更新+1.

After Inesrt: the trigger updates all of the sortOrder fields by +1 if they are > than the field for the one inserted.

删除后:如果所有sortOrder字段比被删除的字段多> -1，则触发器将其更新为-1.

After Delete: the trigger updates all of the sortOrder fields by -1 if they are > than the field for the one deleted.

更新时(sortOrder).这将根据数字是比其上一个位置升高还是降低而从sortOrder字段中增加或减少.

On Update (sortOrder). This adds or subtracts from the sortOrder field depending upon whether the number was moved higher or lower than its previous spot.

现在是问题所在.所有更新表集sortOrder =触发更新时"的任何事件.

那么有没有办法在其他触发功能期间关闭更新?

So is there a way to turn the updating off during other trigger functions?

推荐答案

好吧，我找到了一种方法.我敢肯定，有些人不喜欢它，但是它正在做必须在代码中同时完成的同一件事.可能需要设置一个布尔变量，说我们正在填充一个框，以便事件OnChange可以检查它，而不执行任何操作.

Well, I found a way. Some folks won't like it, I'm sure, but it's doing the same thing that must be done in code at time. One might want to set a Boolean variable to say that we're filling a box so that the event OnChange can check it and not do anything.

因此，由于这只是一个测试，因此该表称为testTbl，并且ID行还有一个名为sortOrder的Integer行.这是称为AFTER INSERT的触发器.它允许插入sortOrder并确保它出现在适当的位置.

So, as this was all a test, the table is called testTbl that, as well as an id row had an Integer row called sortOrder. This is the trigger that is call AFTER INSERT. It allows the sortOrder to be inserted and it assures that it appears at the proper spot.

DROP TRIGGER "main"."trig_testTbl";

CREATE TRIGGER "main"."trig_testTbl" AFTER INSERT ON "testTbl" FOR EACH ROW
BEGIN
    /* tell the trigUpdate table that we're updating*/
     UPDATE trigUpdate SET doNotUpdate = 1 WHERE tblName = 'testTbl';
    /* do what we need to do */
     UPDATE testTbl SET sortOrder = sortOrder + 1
     WHERE sortOrder >= New.sortOrder
     AND rowid <> New.rowid;
    /* Tell it we're done*/
     UPDATE trigUpdate SET doNotUpdate = 0 WHERE tblName = 'testTbl';
END;

您会注意到BEGIN在名为trigUpdate的表中将字段设置为1(真)后的第一行.

You'll note the first line after BEGIN sets a field to 1 (True) in a table called trigUpdate.

这是sortOrder的AFTER UPDATE上的WHEN子句.有两个触发器在sortOrder上都称为AFTER UPDATE.一种是新的sortOrder小于旧的sortOrder，另一种是vica反之.

Here is the WHEN clause on the AFTER UPDATE of sortOrder. There are two triggers that are both called AFTER UPDATE on sortOrder. One is when the new sortOrder is less than the old one, and the other is vica-versa.

New.SortOrder> Old.SortOrder和(从trigUpdate WHERE tblName ='testTbl'中选择doNotUpdate)<> 1

New.SortOrder > Old.SortOrder And (SELECT doNotUpdate FROM trigUpdate WHERE tblName = 'testTbl') <> 1

这篇关于有没有一种方法可以防止在删除和/或插入触发器中触发更新触发器?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！