How do I prevent a database trigger from recursing?

PureKrome_30674 0
Viewed 50545

I've got the following trigger on a table for a SQL Server 2008 database. It's recursing, so I need to stop it.

After I insert or update a record, I'm trying to simply update a single field on that table.

Here's the trigger :

ALTER TRIGGER [dbo].[tblMediaAfterInsertOrUpdate] 
   ON  [dbo].[tblMedia]
   BEFORE INSERT, UPDATE
AS 
BEGIN
    SET NOCOUNT ON

    DECLARE @IdMedia INTEGER,
        @NewSubject NVARCHAR(200)   

    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

Can anyone tell me how I can prevent the trigger's insert from kicking off another trigger again?

triggers sql-server-2008 tsql sql-server sql
7 Answers

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

user_1503420
Related