"INSTEAD OF" Triggers don't seem to work (maybe I misunderstand?)

SQL Server documentation (BOL) states that an "instead of" trigger represent
s
code
that will execute instead of the action requested by the statement that
caused the
trigger to fire. For example, in the case of an "instead of update"
trigger, the
update statment which caused the trigger to fire, would be prevented from
updating the table, simply by virtue of the fact that an "instead of update"
trigger exists. Am I understanding this correctly?
I have an "instead of update trigger" which does not prevent the update
unless I explicitly do a ROLLBACK TRAN. Is this the expected behavior?
TIA,
R. AldersonAn Instead Of trigger should prevent the update. Can you post your code?
Try the following:
Set Nocount on
use pubs
go
Create Table foo (foovalue int)
go
Create Trigger trFoo on foo
Instead Of Update As
Select 'trFoo trigger Fired'
go
Insert Into foo (foovalue) values (1)
Select * from foo
Update foo Set foovalue = 2
Select * from foo
Drop Table foo
Your result should be :
foovalue
--
1
trFoo trigger Fired
foovalue
--
1
showing that the update of foovalue to 2 did not take place.
Tom
"rca" <rca@.discussions.microsoft.com> wrote in message
news:3C9E59C2-8BCB-4A86-A3FA-F2ED58014214@.microsoft.com...
> SQL Server documentation (BOL) states that an "instead of" trigger
> represents
> code
> that will execute instead of the action requested by the statement that
> caused the
> trigger to fire. For example, in the case of an "instead of update"
> trigger, the
> update statment which caused the trigger to fire, would be prevented from
> updating the table, simply by virtue of the fact that an "instead of
> update"
> trigger exists. Am I understanding this correctly?
> I have an "instead of update trigger" which does not prevent the update
> unless I explicitly do a ROLLBACK TRAN. Is this the expected behavior?
> TIA,
> R. Alderson