How do I prevent a database trigger from recursing?

Pure.Krome picture Pure.Krome · Oct 7, 2009 · Viewed 43.3k times · Source

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?

Answer

Anssssss picture Anssssss · Jun 1, 2015

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