Differencies in triggers behavior on 2000 and 2005 SQL Servers

This code works fine on MS SQL 2000 but fails on MS SQL 2005:

create database tt

go

--exec dbo.sp_dbcmptlevel @.dbname=N'tt', @.new_cmptlevel=80

go

use tt
go

create table t (id int)
go

create proc p as update t set id=id
go

create trigger t1_i on t instead of update as update t set id=id
go

create trigger t1 on t for update as if @.@.nestlevel > 5 return exec p update t set id=id
go

insert t select 1
go

update t set id=id
go

use master
go
drop database tt
go

Using database compatibility level 80 (SQL 2000, uncomment the line starting with --exec dbo.sp_dbcmptlevel...) does not make this code work like it does on SQL 2000!

We have examined this code and found that changing

create trigger t1 on t for update as if @.@.nestlevel > 5 return
exec p -- this procedure just executes "update t set id=id" and fails

to direct "update"

create trigger t1 on t for update as if @.@.nestlevel > 5 return
update t set id=id -- works fine without any errors

makes this code work fine but our procedure p is too complex to do any workarounds...

With which error does the procedure fail ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Msg 570, Level 16, State 1, Procedure p, Line 2

Instead of triggers do not support direct recursion. Trigger execution failed.

|||Hi,

do you have another triggers defined on the table ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

There are no other triggers. That code (in the first message) is enough complete to reproduce the error.

Sestrin

|||I believe this has been submitted to MS Connect, http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=248294 and awaiting workaround.