Nested AFTER Triggers in SQL Server are AFTER triggers nested to each other or a trigger on table1 could affect table2 and a trigger on table2 could affect table1 recursively. This circular execution will go till it reaches its maximum depth of 32 then it will stop.
Let us see how nested after triggers could give wrong output and how we could fix this problem.
--create a sample table = table1 if object_id('table1','U') is not null drop table table1; go CREATE TABLE table1 ( col1 INT not null identity , col2 INT ); go --create a sample table = table2 if object_id('table2','U') is not null drop table table2; go CREATE TABLE table2 ( col1 INT not null identity , col2 INT ); go --create trigger 1 on table1 create trigger trigger1 on table1 after update as set nocount on; update table2 set table2.col2 = inserted.col2 -1 from table1 join inserted on table1.col1 = inserted.col1; go --create trigger 1 on table1 create trigger trigger2 on table2 after update as set nocount on; update table1 set table1.col2 = inserted.col2 -1 from table2 join inserted on table2.col1 = inserted.col1; go --insert test value in table1 insert into table1 values(100); insert into table2 values(100); --update value in table1 update table1 set col2=101 where col1=1; go |
This will cause an error “Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).” To see what is going one let us updated the triggers to exit after we get col2=90.
--alter trigger 1 on table1 alter trigger trigger1 on table1 after update as if (select col2 from inserted) = 90 return; set nocount on; update table2 set table2.col2 = inserted.col2 -1 from table1 join inserted on table1.col1 = inserted.col1; go --alter trigger 1 on table1 alter trigger trigger2 on table2 after update as if (select col2 from inserted) = 90 return; set nocount on; update table1 set table1.col2 = inserted.col2 -1 from table2 join inserted on table2.col1 = inserted.col1; go --update value in table1 update table1 set col2=101 where col1=1; go |
The set nocount on suppresses the message from the subsequent trigger execution but if we could select in what’s inside the tables
--check value of the tables select col1 as [table1 col1], col2 as [table1 col2] from table1; select col1 as [table2 col1], col2 as [table2 col2] from table2; go |
This proves that how nested triggers could give spurious result. Therefore unless there is a good reason to keep them it is good idea to turn them off from our server. Turn off nested triggers from our
--turn off nested trigger configuration EXEC sp_configure 'nested triggers', 0; RECONFIGURE; go --update value in table1 update table1 set col2=101 where col1=1; go |
This time we manage to get a correct result from out update operation.
Read More
http://technet.microsoft.com/en-us/library/ms190739.aspx