防止触发器的相互递归执行?

Suppose you have the tables Presentations and Events. When a presentation is saved and contains basic event information, such as location and date, an event will be created automatically using a trigger. (I'm afraid it's impossible for technical reasons to simply keep the data at one place and use a view.) In addition, when changing this information later on in the presentation, the trigger will copy the updates over to the event as well, like so:

假设您有表格演示和事件。保存表示并包含基本事件信息(如位置和日期)时，将使用触发器自动创建事件。(出于技术原因，我恐怕不可能简单地将数据保存在一个地方并使用视图。)此外，当稍后在演示中更改此信息时，触发器也会将更新复制到事件中，如下所示:

CREATE TRIGGER update_presentations
ON Presentations
AFTER UPDATE
AS
BEGIN
    UPDATE Events
    SET Events.Date = Presentations.Date,
        Events.Location = Presentations.Location
    FROM Presentations INNER JOIN Events ON Presentations.EventID = Events.ID
    WHERE Presentations.ID IN (SELECT ID FROM inserted)
END

Now, the customer wants it so that, if a user ever changes the information in the event, it should go back to the presentation as well. For obvious reasons, I can't do the reverse:

现在，客户需要它，这样，如果用户在事件中更改了信息，它也应该返回到表示。由于显而易见的原因，我不能做相反的事:

CREATE TRIGGER update_events
ON Events
AFTER UPDATE
AS
BEGIN
    UPDATE Presentations
    SET Presentations.Date = Events.Date,
        Presentations.Location = Events.Location
    FROM Events INNER JOIN Presentations ON Events.PresentationID = Presentations.ID
    WHERE Events.ID IN (SELECT ID FROM inserted)
END

After all, this would cause each trigger to fire after each other. What I could do is add a column last_edit_by to both tables, containing a user ID. If filled by the trigger with a special invalid ID (say, by making all user IDs of actual persons positive, but user IDs of scripts negative), I could use that as an exit condition:

毕竟，这将导致每一个触发器相互触发。我可以在两个表中添加一个列last_edit_by，其中包含一个用户ID。

    AND last_edit_by >= 0

This might work, but what I'd like to do is indicate to the SQL server that, within a transaction, a trigger should only fire once. Is there a way to check this? Or perhaps to check that a table has already been affected by a trigger?

这可能行得通，但我想做的是向SQL服务器表明，在事务中，触发器应该只触发一次。有办法检查一下吗?又或者是检查表是否已经受到触发器的影响?

---

Answer thanks to Steve Robbins:

感谢Steve Robbins:

Just wrap the potentially nested UPDATE statements in an IF condition checking for trigger_nestlevel(). For example:

只需在trigger_nestlevel()的IF条件检查中包装可能嵌套的UPDATE语句。例如:

CREATE TRIGGER update_presentations
ON Presentations
AFTER UPDATE
AS
BEGIN
    IF trigger_nestlevel() < 2
        UPDATE Events
        SET Events.Date = Presentations.Date,
            Events.Location = Presentations.Location
        FROM Presentations INNER JOIN Events ON Presentations.EventID = Events.ID
        WHERE Presentations.ID IN (SELECT ID FROM inserted)
END

Note that trigger_nestlevel() appears to be 1-based, not 0-based. If you want each of the two triggers to execute once, but not more often, just check for trigger_nestlevel() < 3 in both triggers.

注意trigger_nestlevel()看起来是基于1而不是基于0的。如果您希望这两个触发器中的每个都执行一次，但不是更频繁地执行，那么只需检查两个触发器中的trigger_nestlevel() < 3。

1 个解决方案

#1

I'm not sure about doing it per transaction, but do you need nested triggers switched on for other parts? If you switch them off on the server then a trigger won't fire from another trigger updating a table.

我不确定每个事务都要这样做，但是您是否需要为其他部分打开嵌套触发器?如果在服务器上关闭它们，则触发器将不会从更新表的另一个触发器中触发。

EDIT (answer from the comments): You will need to alter trigger A to use TRIGGER_NESTLEVEL

编辑(从注释中回答):您将需要修改触发器A以使用TRIGGER_NESTLEVEL