Nested AFTER Triggers in SQL Server -

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