Avoiding recursive triggers in SQL vs avoiding nested execution

I have just run into a case where I need to add a trigger to a table that will execute whenever an entry in the table is created or altered. The problem is that the action that the needs to be taken includes updating values in the same table – which will activate the trigger – which will update the table – which will activate the trigger – which will… you get the picture.

So I looked up how to prevent triggers from performing recursive calls. There where many different answers out there, most of which I rejected because they did not match my needs. However I kept seeing one solution pop up over and over involving code like the following:

CREATE TRIGGER trig_TableA ON [dbo].[TableA]
AFTER INSERT, UPDATE
AS
BEGIN
   IF TRIGGER_NESTLEVEL() > 0 RETURN
   INSERT [dbo].[TableA] (tablecolumn) VALUES (datavalue)
END

It seems to be widely accepted as the way to stop recursive trigger execution. But as I looked at it, it seemed to me that it does not. The key is in the name of the function (TRIGGER_NESTLEVEL): it stops nested execution of triggers, not recursive execution.

So I thought I would test it out. I created a simple test scenario: three tables (A, B and C) which for whatever reason need to be kept in synch – so that whatever is entered into one of the three needs to be entered into the other two.
This code creates the tables:

CREATE TABLE [dbo].[TableA](
       [entryText] [nchar](10) NULL,
       [entryDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TableB](
       [entryText] [nchar](10) NULL,
       [entryDate] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TableC](
       [entryText] [nchar](10) NULL,
       [entryDate] [datetime] NULL
) ON [PRIMARY]
GO

And this code creates the triggers using the logic I saw on various posts:

CREATE TRIGGER trig_TableA ON dbo.TableA
AFTER INSERT
AS
BEGIN
   Print 'trig_TableA testing TRIGGER_NESTLEVEL '
         + CAST(TRIGGER_NESTLEVEL() AS VARCHAR(3))
         + ' = 1';
   IF (TRIGGER_NESTLEVEL()) = 1
     BEGIN
        PRINT 'trig_TableA is inserting into TableB';
        INSERT INTO TableB
               (
                 entryText
                 , entryDate
               )
        SELECT i.entryText
               , i.entryDate
        FROM INSERTED i;
     END
   ELSE
     BEGIN
        PRINT 'trig_TableA will not insert into TableB';
     END
END
GO

CREATE TRIGGER trig_TableB ON dbo.TableB
AFTER INSERT
AS
BEGIN
   Print 'trig_TableB testing TRIGGER_NESTLEVEL '
         + CAST(TRIGGER_NESTLEVEL() AS VARCHAR(3))
         + ' = 1';
   IF (TRIGGER_NESTLEVEL()) = 1
     BEGIN
        PRINT 'trig_TableB is inserting into TableC';
        INSERT INTO TableC
               (
                 entryText
                 , entryDate
               )
        SELECT i.entryText
               , i.entryDate
        FROM INSERTED i;
     END
   ELSE
     BEGIN
        PRINT 'trig_TableB will not insert into TableC';
     END
END
GO

CREATE TRIGGER trig_TableC ON dbo.TableC
AFTER INSERT
AS
BEGIN
   Print 'trig_TableC testing TRIGGER_NESTLEVEL '
          + CAST(TRIGGER_NESTLEVEL() AS VARCHAR(3))
          + ' = 1';
   IF (TRIGGER_NESTLEVEL()) = 1
     BEGIN
        PRINT 'trig_TableC is inserting into TableA';
        INSERT INTO TableA
               (
                 entryText
                 , entryDate
               )
        SELECT i.entryText
               , i.entryDate
        FROM INSERTED i;
     END
   ELSE
     BEGIN
        PRINT 'trig_TableC will not insert into TableA';
     END
END
GO 

The idea here is that whenever an Insert is performed on one of the tables it will automatically perform the same insert on the next table (A inserts to B, B to C and C to A). TRIGGER_NESTLEVEL() is checked to prevent the inserts from looping forever.
I inserted a record into TableC to test that it would appear in the other tables and this is what I saw:

INSERT INTO [TESTOOS].[dbo].[TableC]
       ( [entryText]
         , [entryDate])
       VALUES
       ( 'Alpha'
         , CURRENT_TIMESTAMP )
GO

trig_TableC testing TRIGGER_NESTLEVEL 1 = 1
trig_TableC is inserting into TableA
trig_TableA testing TRIGGER_NESTLEVEL 2 = 1
trig_TableA will not insert into TableB

And the tables contain

        entryText  entryDate
------- ---------- -----------------------
TABLEA: Alpha      2014-08-05 17:04:15.703
TABLEB:
TABLEC: Alpha      2014-08-05 17:04:15.703

As I thought. When Trig_TableC was executed the value of TRIGGER_NESTLEVEL() was 1, so it executed the INSERT into TableA. However, when Trig_TableA executes, the value of TRIGGER_NESTLEVEL() is 2 (indicating that one trigger has called a second trigger). Processing stopped before all three tables were updated – I needed all three tables to be updated.

The proper method to avoid recursion is to use the object_id parameter of the TRIGGER_NESTLEVEL() function to see if the trigger is being called multiple times:

ALTER TRIGGER trig_TableA ON dbo.TableA
AFTER INSERT
AS
BEGIN
   DECLARE @tn_b INT;
   SET @tn_b = TRIGGER_NESTLEVEL(( SELECT object_id 
                                   FROM   sys.triggers 
                                   WHERE  name = 'trig_TableB' ));
   Print 'trig_TableA testing TRIGGER_NESTLEVEL(trig_TableB) '
         + CAST(@tn_b AS VARCHAR(3))
         + ' < 1';
   IF (@tn_b) < 1
     BEGIN
        PRINT 'trig_TableA is inserting into TableB';
        INSERT INTO TableB
               (
                 entryText
                 , entryDate
               )
        SELECT i.entryText
               , i.entryDate
        FROM INSERTED i;
     END
  ELSE
     BEGIN
        PRINT 'trig_TableA will not insert into TableB';
     END
END
GO

ALTER TRIGGER trig_TableB ON dbo.TableB
AFTER INSERT
AS
BEGIN
   DECLARE @tn_c INT;
   SET @tn_c = TRIGGER_NESTLEVEL(( SELECT object_id 
                                   FROM   sys.triggers 
                                   WHERE  name = 'trig_TableC' ));
   Print 'trig_TableB testing TRIGGER_NESTLEVEL(trig_TableC) '
         + CAST(@tn_c AS VARCHAR(3))
         + ' < 1';
   IF (@tn_c) < 1
     BEGIN
         PRINT 'trig_TableB is inserting into TableC';
         INSERT INTO TableC
                (
                  entryText
                  , entryDate
                )
         SELECT i.entryText
                , i.entryDate
         FROM INSERTED i;
     END
   ELSE
     BEGIN
         PRINT 'trig_TableB will not insert into TableC';
     END
END
GO

ALTER TRIGGER trig_TableC ON dbo.TableC
AFTER INSERT
AS
BEGIN
    DECLARE @tn_a INT;
    SET @tn_a = TRIGGER_NESTLEVEL(( SELECT object_id 
                                    FROM   sys.triggers 
                                    WHERE  name = 'trig_TableA' ));
    Print 'trig_TableC testing TRIGGER_NESTLEVEL(trig_TableA) '
          + CAST(@tn_a AS VARCHAR(3))
          + ' < 1';
    IF (@tn_a) < 1
      BEGIN
          PRINT 'trig_TableC is inserting into TableA';
          INSERT INTO TableA
                 (
                   entryText
                   , entryDate
                 )
          SELECT i.entryText
                 , i.entryDate
          FROM INSERTED i;
     END
   ELSE
     BEGIN
         PRINT 'trig_TableC will not insert into TableA';
     END
END
GO

Now each trigger is looking to see if the next trigger in the chain has already been called. The important thing to note is that we are seeing if a trigger has been called multiple times, not if multiple triggers have been called (which is what the internet examples do). Let’s give it a try.

INSERT INTO [TESTOOS].[dbo].[TableC]
       ( [entryText]
         , [entryDate])
       VALUES
       ( 'Beta'
         , CURRENT_TIMESTAMP )
GO

trig_TableC testing TRIGGER_NESTLEVEL(trig_TableA) 0 < 1
trig_TableC is inserting into TableA
trig_TableA testing TRIGGER_NESTLEVEL(trig_TableB) 0 < 1
trig_TableA is inserting into TableB
trig_TableB testing TRIGGER_NESTLEVEL(trig_TableC) 1 < 1
trig_TableB will not insert into TableC

        entryText  entryDate
------- ---------- -----------------------
TABLEA: Alpha      2014-08-05 17:04:15.703
TABLEA: Beta       2014-08-05 17:07:36.390
TABLEB: Beta       2014-08-05 17:07:36.390
TABLEC: Alpha      2014-08-05 17:04:15.703
TABLEC: Beta       2014-08-05 17:07:36.390

That’s more like it: there are three Beta entries, one in each table.

Now, the example above required me to have each trigger reference a different one – but that is only because I was setting up an example. In most cases the proper form to use will be something like

CREATE TRIGGER trig_TableA ON [dbo].[TableA]
AFTER INSERT, UPDATE
AS
BEGIN
   IF TRIGGER_NESTLEVEL(( SELECT object_id 
                          FROM   sys.triggers 
                          WHERE  name = 'trig_TableA' )) > 1 RETURN
   INSERT [dbo].[TableA] (tablecolumn) VALUES (datavalue)
END

Thanks to psiintl for the following simplification:

CREATE TRIGGER trig_TableA ON [dbo].[TableA]
AFTER INSERT, UPDATE
AS
BEGIN
   IF TRIGGER_NESTLEVEL(@@PROCID) = 1
      INSERT [dbo].[TableA] (tablecolumn) VALUES (datavalue)
END