How do I prevent a database trigger from recursing?
sql, sql-server, sql-server-2008, t-sql, triggers
Solution
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
Problem
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?