Prevent mutually recursive execution of triggers?

Question

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:

    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?

---

Answer thanks to Steve Robbins:

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

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.

Answer 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