title: Prevent mutually recursive execution of triggers?
body: <p>Suppose you have the tables <code>Presentations</code> and <code>Events</code>. 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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Now, the customer wants it so that, if a user ever changes the information in the <em>event</em>, it should go back to the presentation as well. For obvious reasons, I can't do the reverse:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>After all, this would cause each trigger to fire after each other. What I could do is add a column <code>last_edit_by</code> 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:</p>
  
  <pre><code>    AND last_edit_by &gt;= 0
  </code></pre>
  
  <p>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?</p>
  
  <hr>
  
  <p><strong>Answer</strong> thanks to Steve Robbins:</p>
  
  <p>Just wrap the potentially nested <code>UPDATE</code> statements in an IF condition checking for <code>trigger_nestlevel()</code>. For example:</p>
  
  <pre><code>CREATE TRIGGER update_presentations
  ON Presentations
  AFTER UPDATE
  AS
  BEGIN
      IF trigger_nestlevel() &lt; 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
  </code></pre>
  
  <p>Note that <code>trigger_nestlevel()</code> 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 <code>trigger_nestlevel() &lt; 3</code> in both triggers.</p>