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.