SQL SERVER – 2005 Understanding Trigger Recursion and Nesting with examples

Trigger events can be fired within another trigger action. One Trigger execution can trigger even on another table or same table. This trigger is called NESTED TRIGGER or RECURSIVE TRIGGER. Nested triggers SQL Server supports the nesting of triggers up to a maximum of 32 levels. Nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers When a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur.

Disable Nesting/Recursing Triggers
Following script will stop executing all the nested triggers.
sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

There is also alternate way to stop Trigger Recursion

ALTER DATABASE
databasename
SET RECURSIVE_TRIGGERS ON | OFF

Restrict Trigger Nesting to certain level
Put following script in trigger code. This will stop the trigger recursion after certain levels. In following case it will stop after 5 recursion.
IF ((
SELECT TRIGGER_NESTLEVEL()) > 5 )
RETURN

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – 2005 – SSMS Change T-SQL Batch Separator
Next Post
SQL SERVER – Math Functions Available in SQL Server

Related Posts

28 Comments. Leave new

  • It is happening for me with stored procedures. What may be the solution for this?

    Reply
  • is there a way to write trigger Before Insert/Update /Delete

    Reply
  • hello , after disabling the trigger same issue is arrive ‘Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).’. n i also check that in DB level no trigger exit.so please give me solution if you have

    Reply

Leave a Reply

Menu