防止相互递归执行触发器？ [英] Prevent mutually recursive execution of triggers?

问题描述

假设您有表演示文稿和事件。保存演示文稿并包含基本事件信息（如位置和日期）后，将使用触发器自动创建事件。 （由于技术原因，恐怕不可能简单地将数据保存在一个位置并使用视图。）此外，在稍后的演示中更改此信息时，触发器也会将更新复制到事件中，像这样：

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

毕竟，这将导致每个触发器彼此触发。我可以做的是在两个表中添加一列 last_edit_by ，其中包含用户ID。如果在触发器中填充了特殊的无效ID（例如，通过使实际人员的所有用户ID为正，而使脚本的用户ID为负），则可以将其用作退出条件：

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:

    AND last_edit_by >= 0

此可能有效，但是我想做的是向SQL Server指示，在事务中，触发器应该只触发一次。有办法检查吗？还是要检查表是否已被触发器影响？

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?

答案，谢谢到史蒂夫·罗宾斯（Steve Robbins）：

Answer thanks to Steve Robbins:

只需将可能嵌套的 UPDATE 语句包装在IF条件中，以检查 trigger_nestlevel（）。例如：

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

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

请注意 trigger_nestlevel（）似乎是基于1的，而不是基于0的。如果您希望两个触发器中的每个触发器执行一次，但不经常执行，只需检查 trigger_nestlevel（）< 3 在两个触发器中。

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.

推荐答案

我不确定每次交易都执行此操作，但是您是否需要为其他零件打开嵌套触发器？如果您在服务器上关闭它们，则不会从另一个触发器更新表中触发该触发器。

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.

编辑（评论的答案）：您将需要更改触发器A才能使用 TRIGGER_NESTLEVEL

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

这篇关于防止相互递归执行触发器？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！