Imtiaz34639


I have observed the following strange behaviour . Can someone point out why it is so.

I have following tables

t1

----

Id int NOT NULL ---PK column

t2

----

Id int NOT NULL ---PK column

Now I create a INSTEAD OF INSERT trigger on t2 which is nothing but insert into t2 from inserted table like below

CREATE TRIGGER t2_trigger

ON t2

instead of insert

AS

BEGIN

insert into t2

select id from inserted

END

GO

I do the following

insert into t2 values (1)

Now lets consider the two scenarios....Trigger on T2 is enabled

begin transaction

insert into t1 values (1)

insert into t1 values (2)

insert into t2 values (1)

commit

/*

Records in t1 = 0

Records in t2 = 1

*/

Now I DISABLE the triigger

lets run again

begin transaction

insert into t1 values (1)

insert into t1 values (2)

insert into t2 values (1)

commit

Now I have the following

/*

Records in t1 = 2 ( )

Records in t2 = 1

*/

How is that the inserts in the batch get committed even if the insert fails (with no trigger enabled)

and get rolled back when the insert fails from the inside of INSTEAD OF INSERT trigger....

This is driving me nuts...

Please help.




Re: Weird behaviour of INSTEAD OF INSERT TRIGGER

Imtiaz


No replies so far.......I am waiting to her something from the MVPs




Re: Weird behaviour of INSTEAD OF INSERT TRIGGER

Umachandar Jayachandran - MS

Any error inside the trigger will rollback the transaction and end the batch execution. Otherwise, the batch abort behavior depends on the XACT_ABORT setting. In your 2nd example with disabled trigger, you have XACT_ABORT OFF (default) setting so the commit will get executed after the final insert statement failure given the lack of error handling/checks. So this is expected behavior. Look up BOL topics on XACT_ABORT, trigger, error handling etc.