August 3, 2007 at 3:06 am
This concerns the firing of underlying table triggers from an INSTEAD OF trigger on a view. This is probably more of a rant than anything, because i am pretty sure there is nothing i can do about it, but if anyone has any ideas about how to configure SQL Server so both versions are consistent then please let me know.
We choose to turn the 'allow nested triggers' option off and this produces a difference between SQL2000 (8.00.818) and SQL2005 (9.0.3054) which we discovered when we upgraded our test server. On SQL2000 if an INSTEAD OF trigger fires and updates an underlying table, the underlying table trigger does not fire. however on SQL2005 it does.
Having thought about it, it makes sense that if you update a table through a view, then you would want the underlying table trigger to fire. However i can't see any way of forcing the underlying table trigger to fire in SQL2000 when the INSTEAD OF trigger updates the underlying table. I want a nice clean way to make sure both versions of SQL Server behave the way.
BOL seems to have been updated but the information is scarce as to why. It's not in the 'Database Engine Backward Compatibility' section where I would expect it to be. The only info I can find is in this section:
http://msdn2.microsoft.com/en-us/library/ms190739.aspx
which adds this comment to the SQL2000 definition:
I have opened this issue up with Microsoft Connect, but haven't heard anything back from them yet.
Here is some code that will show the difference when running on SQL2000 and SQL2005. Note the 'allow nested triggers' option must be off:
-- SQL2000 with nested triggers OFF: table trigger DOES NOT fire
-- SQL2000 with nested triggers ON: table trigger DOES fire
-- SQL2005 with nested triggers OFF: table trigger DOES fire
-- SQL2005 with nested triggers ON: table trigger DOES fire
-- to turn nested triggers off use:
-- sp_configure 'nested triggers', 0
-- RECONFIGURE
if exists(select * from information_schema.tables where table_name = 'a_Test_InsteadOf')
drop table a_Test_InsteadOf
go
create table a_Test_InsteadOf (
col1 varchar(10)
)
go
create trigger a_Test_InsteadOf_tI on a_Test_InsteadOf
for insert
as
declare @string varchar (100)
select @string = 'table trigger a_Test_InsteadOf_tI: inserted "' + col1 + '"'
from inserted
print @string
go
if exists(select * from information_schema.views where table_name = 'vwa_a_Test_InsteadOf')
drop view vwa_a_Test_InsteadOf
go
create view vwa_a_Test_InsteadOf
as
select col1 from a_Test_InsteadOf
go
create trigger vwa_a_Test_InsteadOf_ti on vwa_a_Test_InsteadOf
instead of insert
as
declare @string varchar (100)
select @string = 'view trigger vwa_a_Test_InsteadOf_tI: inserted "' + col1 + '"'
from inserted
print @string
insert a_Test_InsteadOf
select 'xxx'
go
insert vwa_a_Test_InsteadOf
select 'john'
go
-- cleanup
if exists(select * from information_schema.tables where table_name = 'a_Test_InsteadOf')
drop table a_Test_InsteadOf
go
if exists(select * from information_schema.views where table_name = 'vwa_a_Test_InsteadOf')
drop view vwa_a_Test_InsteadOf
go
-- end of code --
Any help or comments on this issue would be much appreciated,
Regards, Phill.
August 3, 2007 at 9:17 am
Seems to me that turning on underlying triggers will keep sql2k and sql2k5 consistent. Doesn't strike me nearly as serious as the fact that 64 bit sql2k5 doesn't support the JET engine - so no feeds from Excel or Access (and who takes feeds rather than whole documents from Word?).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply