Nested trigger problem while recursive triggers are disabled

hello,

i'm still relative newbie in using database triggers. the following example produces trigger loop (exceeding recursion limit of 32), because trigger fires trigger b , reverse. why happen also if database option "recursive triggers enabled" has been set false? 

create table triggertest (id int not null primary key, last_update datetime, counter int);
go

insert triggertest (id) values (1);
go

create trigger triggertest_a on triggertest after insert, update
begin
 set nocount on;
 update triggertest
 set last_update = getdate()
 from triggertest t inner join inserted on i.id = t.id
end
go

create trigger triggertest_b on triggertest after insert, update
begin
 set nocount on;
 update triggertest
 set counter += 1
 from triggertest t inner join inserted on i.id = t.id
end
go

insert triggertest (id) values (2); -- produces recursion error message

of course, example without deeper sense, it's test. i'm working sqlserver 2008.

regards,

klaus

klaus

why not having 1 trigger update such as

create trigger triggertest_b on triggertest after update
begin
 set nocount on;
 update triggertest
 set counter += 1,last_update = getdate()

 from triggertest t inner join inserted on i.id = t.id
end
go

and having last_update default getdate() , counter default=1 (not null)

---

best regards, uri dimant sql server mvp http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

SQL Server  >  Transact-SQL