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 (http://blog.SQLAuthority.com)
pinaldave
HOw to use trriger in sql server?
please as quick as possible
What is Trriger explined?
What is Indexes explined?
Hello,
I just wanted to thank you for the article, it put me out of big troubles with a trigger I was creating.
Keep helping people like that!!!!!!!! ;)
Hello ,
I have a INSERT/DELETE/UPDATE trigger on my master table which stores information about all the users.
When I am updating this master table with a query like Below
Update tblUserGeneral set Loc=2 where LearnerId in (‘12′,’34′,’56′,’89′)
The trigger gets fired for the last Learnerid i.e. 89 but not for all the learnerids in where condition.
Why this happens? any solutions?
Let me know if more information is requuired from my side.
From what I understand an after trigger will only fire “after” a query completes successfully this means after all rows are effected, you need to take this into account when writing a trigger that deals with multiple row changes. you can always conditionalize on the @@ROWCOUNT variable if you need to have different trigger functionality defendant on whether only one row or multiple rows where effected. the inserted and deleted tables hold the data for all the rows effected not just the last.
Dave, excellent information as usual.
I want to execute two triggers simultaneously from one table. Both triggers do different things and need to work independently of each other i.e the first trigger does not need to fire the second trigger (no nesting involved). On a table insert, both triggers should fire.
Is this possible?
I am trying to nest stored procedures within SQLServer 2005, and I am getting the following error:
An INSERT EXEC statement cannot be nested.
I build a table and return it in one stored procedure; which is called by another stored procedure and stored in a temp table (see below).
INSERT INTO #EndPolicies EXEC [dbo].[stpGetExposure] @SnapshotTable,@StartDate,@PolicyType,@Company,@Territory
Where stpGetExposure returns a table.
Any help would greatly be appreciated.
Thanks
Joe Castelli
and stored in a temp table
I learnt the hard was (unconfirmed, but it happened to me) that the recursive_triggers option is off by default.
Watch out for that one!
rts
Thats right.
Recursive_Triggers option is set to OFF by default.
Nested Triggers option is set to ON by default.
With the above settings nested triggers will work but not recursive ones.