title: Sql Server Trigger Fires Square Number of Times
body: <p>I am attempting to add a trigger to a table that logs the value of certain rows changed. Everything works as expected, except the trigger executes the number of rows squared times. For example, if I were to change the name of 5 items in the table in a bulk query, the trigger would log in the TriggerTable 25 entries. Each item is logged the number of of total items changed, so the first name change would be logged 5 times since there were 5 total name changes. Pasted a portion of the trigger below.</p>
  <p>I have tried adding a <code>IF TRIGGER_NESTLEVEL() &lt;= 1</code> to no success. The rest of the functionality works great, except this part. Any advice is appreciated!</p>
  <pre><code>CREATE OR ALTER TRIGGER [dbo].[Trigger]
  ON [dbo].[Table]
  AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      IF UPDATE(Name)
          IF EXISTS(SELECT Name FROM inserted) AND EXISTS(SELECT Name FROM deleted)
              INSERT INTO TriggerTable (Id, ColumnName, OldValue, NewValue)
                  SELECT i.Id, 'Name', d.Name, i.Name
                  FROM deleted d, inserted i
                  WHERE d.Name &lt;&gt; i.Name OR (d.Name IS NULL AND i.Name IS NOT NULL) OR (d.Name IS NOT NULL AND i.Name IS NULL)
          ELSE IF EXISTS(SELECT Name FROM inserted)
              INSERT INTO  TriggerTable (Id, ColumnName, NewValue)
                  SELECT i.Id, 'Name', i.Name
                  FROM inserted i
          ELSE IF EXISTS(SELECT Name FROM deleted)
              INSERT INTO  TriggerTable (Id, ColumnName, OldValue)
                  SELECT d.Id, 'Name', d.Name
                  FROM deleted d
  </code></pre>