How do I prevent a database trigger from recursing?

Question

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 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

Answer 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