How do I prevent a database trigger from recursing?

Solution 1:

Not sure if it is pertinent to the OP's question anymore, but in case you came here to find out how to prevent recursion or mutual recursion from happening in a trigger, you can test for this like so:

IF TRIGGER_NESTLEVEL() <= 1/*this update is not coming from some other trigger*/

MSDN link

Solution 2:

I see three possibilities:

1. Disable trigger recursion:

   This will prevent a trigger fired to call another trigger or calling itself again. To do this, execute this command:

   ALTER DATABASE MyDataBase SET RECURSIVE_TRIGGERS OFF
   GO
   

2. Use a trigger INSTEAD OF UPDATE, INSERT

   Using a INSTEAD OF trigger you can control any column being updated/inserted, and even replacing before calling the command.

3. Control the trigger by preventing using IF UPDATE

   Testing the column will tell you with a reasonable accuracy if you trigger is calling itself. To do this use the IF UPDATE() clause like:

   ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate]
      ON  [dbo].[tblMedia]
      FOR INSERT, UPDATE
   AS
   BEGIN
       SET NOCOUNT ON
       DECLARE @IdMedia INTEGER,
           @NewSubject NVARCHAR(200)   
   
       IF UPDATE(UniqueTitle)
         RETURN;
   
       -- What is the new subject being inserted?
       SELECT @IdMedia = IdMedia, @NewSubject = Title
       FROM INSERTED
   
       -- Now update the unique subject field.
       -- NOTE: dbo.CreateUniqueSubject is my own function. 
       --       It just does some string manipulation.
       UPDATE tblMedia
       SET UniqueTitle = dbo.CreateUniqueSubject(@NewSubject) + 
                         CAST((IdMedia) AS VARCHAR(10))
       WHERE tblMedia.IdMedia = @IdMedia
   END
   

Solution 3:

TRIGGER_NESTLEVEL can be used to prevent recursion of a specific trigger, but it is important to pass the object id of the trigger into the function. Otherwise you will also prevent the trigger from firing when an insert or update is made by another trigger:

   IF TRIGGER_NESTLEVEL(OBJECT_ID('dbo.mytrigger')) > 1
         BEGIN
             PRINT 'mytrigger exiting because TRIGGER_NESTLEVEL > 1 ';
             RETURN;
     END;

From MSDN:

When no parameters are specified, TRIGGER_NESTLEVEL returns the total number of triggers on the call stack. This includes itself.

Reference: Avoiding recursive triggers