Oleg Netchaev (10/1/2010)

---

Hugo Kornelis (10/1/2010)

---

Exactly what change are you refering to? As far as I know, the effect of ROLLBACK in a trigger in current versions is the same as it was in SQL Server 2000 (and probably even versions before that).

There is a minor change in what is reported. In SQL Server 2000 it was somewhat common to issue rollback tran inside of the trigger to undo whatever work has been done including whatever DML operation(s) which started from begin tran outside of the trigger. SQL Server 2000 did not report any errors. Starting from SQL Server 2005, the reporting behaviour has changed somewhat: the value of @@trancount must be the same then entering and exiting the trigger, otherwise, transaction ended in the trigger error is reported.

In this question, the most interesting scenario to examine is this (I will also add a "manual" insert into log):

begin tran;

insert into TranLog values (20, 20);

insert into TranTest values (3);

commit tran;

go

What we expect is this: inside of the trigger the first insert is rolled back and it also affected the "manual" insert so there are no records in the log yet. The code in the trigger continues executing and therefore, second insert in the trigger, inserting 13 into the log table stands and once the trigger is finished, we get the error message stating that transaction ended in the trigger, the batch has been aborted. Since it has not been aborted until the last line of trigger finished executing, the log record with value 13 still stands. The hint that it happened in that order is here:

(1 row(s) affected)

Msg 3609, Level 16, State 1, Line 5

The transaction ended in the trigger. The batch has been aborted.

The first message belongs to that insert inside of the trigger, which was after the rollback tran.

This is the difference: in SQL Server 2000 the result would be the same, but the error would not be raised.

One way to silence this error is to ensure that the @@trancount is the same on the way in and out like so:

--replace

if @num %2 = 1 rollback tran;

-- with this

if @num %2 = 1

begin

rollback tran;

begin tran;

end;

Trancount values inserted in the log table aside, the result is the same, both

insert into TranLog values (20, 20); -- after begin tran, before the trigger

insert into TranLog values (@num, @tc); --inside the trigger before rollback

are rolled back and

insert into TranLog (num, trancount) values (10 + @num, @tc); -- inside the trigger after rollback

is executed.

Oleg