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:
-
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
-
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. -
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
Solution 4
ALTER DATABASE <dbname> SET RECURSIVE_TRIGGERS OFF
RECURSIVE_TRIGGERS { ON | OFF }
ON Recursive firing of AFTER triggers is allowed.
OFF Only direct recursive firing of AFTER triggers is not allowed. To also disable indirect recursion of AFTER triggers, set the nested triggers server option to 0 by using sp_configure.
Only direct recursion is prevented when RECURSIVE_TRIGGERS is set to OFF. To disable indirect recursion, you must also set the nested triggers server option to 0.
The status of this option can be determined by examining the is_recursive_triggers_on column in the sys.databases catalog view or the IsRecursiveTriggersEnabled property of the DATABASEPROPERTYEX function.
Solution 5
I think i got it :)
When the title is getting 'updated' (read: inserted or updated), then update the unique subject. When the trigger gets ran a second time, the uniquesubject field is getting updated, so it stop and leaves the trigger.
Also, i've made it handle MULTIPLE rows that get changed -> I always forget about this with triggers.
ALTER TRIGGER [dbo].[tblMediaAfterInsert]
ON [dbo].[tblMedia]
FOR INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON
-- If the Title is getting inserted OR updated then update the unique subject.
IF UPDATE(Title) BEGIN
-- Now update all the unique subject fields that have been inserted or updated.
UPDATE tblMedia
SET UniqueTitle = dbo.CreateUniqueSubject(b.Title) +
CAST((b.IdMedia) AS VARCHAR(10))
FROM tblMedia a
INNER JOIN INSERTED b on a.IdMedia = b.IdMedia
END
END
Pure.Krome
Just another djork trying to ply his art in this mad mad world. Tech stack I prefer to use: Laguage: C# / .NET Core / ASP.NET Core Editors: Visual Studio / VS Code Persistence: RavenDB, SqlServer (MSSql or Postgres) Source control: Github Containers: Docker & trying to learn K&'s Cloud Platform: Azure Caching/CDN: Cloudflare Finally: A Tauntaun sleeping bag is what i've always wanted spaces > tabs
Updated on November 03, 2020Comments
-
Pure.Krome over 3 years
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?