Triggers and Transactions

Question

Triggers and Transactions

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Bradley Deem SSCrazy Points: 2565 More actions · Answer 1

Good question. A slight variation on the question helps explain the behavior of @@TRANCOUNT which isn't very clearly stated in the BOL.

A trigger operates as if there were an outstanding transaction in effect when the trigger is executed. This is true whether the statement firing the trigger is in an implicit or explicit transaction.

So consider the following.

CREATE TABLE TranTest

(num int)

GO

CREATE TRIGGER TrgTranTest

ON TranTest

FOR INSERT

AS

BEGIN

SET NOCOUNT ON

PRINT 'TranCount is ' + CAST(@@TRANCOUNT as VARCHAR(10))

END

GO

SET NOCOUNT ON

INSERT INTO TranTest VALUES (1) -- Implicit Transaction (@@Trancount = 1 inside of Trigger)

GO

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO TranTest VALUES (2) -- Explict Transaction (@@Trancount = 2 inside of Trigger)

COMMIT TRAN

GO

SET NOCOUNT ON

BEGIN TRAN

INSERT INTO TranTest VALUES (3) -- Explicit Transaction (@@Trancount = 1 inside of Trigger)

COMMIT TRAN

GO

Which produces the following output.

TranCount is 1
TranCount is 2
TranCount is 1

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 2

I found the question a little misleading due to the fact that you are assigning the trancount to a variable prior to the insert. If you inserted @@trancount DIRECTLY into tranlog, then the answer that showed "(11,1),(2,1),(12,1),(13,1)" would have been the correct answer, as a transaction would have been initiated at the point that the insert statement into the Tranlog table occurred. Other than that minor comment, a great question illustrating transactional behavior in a trigger.

Dan Guzman - Not the MVP Hall of Fame Points: 3757 More actions · Answer 3

OK, I'm the dummy this morning: Why wasn't (3,1) one of the inserted rows?

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 4

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

honza.mf SSCertifiable Points: 5519 More actions · Answer 5

Dan Guzman - Not the MVP (10/1/2010)
OK, I'm the dummy this morning: Why wasn't (3,1) one of the inserted rows?

Because the trigger allows only even numbers and 3 is an odd number.

See, understand, learn, try, use efficient
© Dr.Plch

honza.mf SSCertifiable Points: 5519 More actions · Answer 6

Rich-403221 (10/1/2010)
I found the question a little misleading due to the fact that you are assigning the trancount to a variable prior to the insert. If you inserted @@trancount DIRECTLY into tranlog, then the answer that showed "(11,1),(2,1),(12,1),(13,1)" would have been the correct answer, as a transaction would have been initiated at the point that the insert statement into the Tranlog table occurred. Other than that minor comment, a great question illustrating transactional behavior in a trigger.

This construct was to show that @@trancount is zero directly after the rollback statement.

The insert statement (which is not in the transaction this time) starts the implicit transaction.

I played with combination of both this inserts, through the variable and @@trancount, but there were to many phenomena for one question.

I hoped the discussion will cover all these subtle details. And I'm feeling I was right.

honza.mf SSCertifiable Points: 5519 More actions · Answer 7

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

Thank you Oleg, well done.

It has only one little bug: You start new transaction allways. It is unsafe as you can nest several transactions.

I prefer in such a construct to test @@trancount = 1.

But what is more dangerous, you hide the rollback message, wich can be very important if you have something before the insert statement, just like you in your first example.

rtelgenhoff SSC Eights! Points: 928 More actions · Answer 8

It's interesting that even though the first INSERT statement is not part of an explicit transaction, the ROLLBACK inside the trigger negates the INSERT. If there was no trigger defined, an INSERT without a preceding BEGIN TRAN, followed by a ROLLBACK produces the error 'Msg 3903, Level 16, State 1, Line 2 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'

Source-NH SSCarpal Tunnel Points: 4165 More actions · Answer 9

rtelgenhoff (10/1/2010)
It's interesting that even though the first INSERT statement is not part of an explicit transaction, the ROLLBACK inside the trigger negates the INSERT. If there was no trigger defined, an INSERT without a preceding BEGIN TRAN, followed by a ROLLBACK produces the error 'Msg 3903, Level 16, State 1, Line 2 The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'

That behavior is governed by SQL Server's default behavior of Autocommit transactions. Because the trigger executes within the context of the original transaction, as long as BOTH the trigger and the statement causing trigger execution are successful, then the original transaction is committed automatically.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 10

honza.mf (10/1/2010)
Thank you Oleg, well done.
It has only one little bug: You start new transaction allways. It is unsafe as you can nest several transactions.
I prefer in such a construct to test @@trancount = 1.
But what is more dangerous, you hide the rollback message, wich can be very important if you have something before the insert statement, just like you in your first example.

Thank you for pointing it out Honza. I just forgot to mention that by no means I advocate the snippet I wrote. I only did it to accentuate the difference between SQL Server 2000 and 2005 behaviour, which is in 2000 the error was not reported but in 2005 it is whenever the trancount on enter is not the same as trancount on exit. As far as execution is concerned, it is the same in both versions, that is the script after rollback tran inside of the trigger continues executing.

This was a very good question, as always.

Oleg

honza.mf SSCertifiable Points: 5519 More actions · Answer 11

Oleg Netchaev (10/1/2010)
honza.mf (10/1/2010)
Thank you Oleg, well done.
It has only one little bug: You start new transaction allways. It is unsafe as you can nest several transactions.
I prefer in such a construct to test @@trancount = 1.
But what is more dangerous, you hide the rollback message, wich can be very important if you have something before the insert statement, just like you in your first example.
Thank you for pointing it out Honza. I just forgot to mention that by no means I advocate the snippet I wrote. I only did it to accentuate the difference between SQL Server 2000 and 2005 behaviour, which is in 2000 the error was not reported but in 2005 it is whenever the trancount on enter is not the same as trancount on exit. As far as execution is concerned, it is the same in both versions, that is the script after rollback tran inside of the trigger continues executing.
This was a very good question, as always.
Oleg

I understood you picked two points of view. Both are very important.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Thanks for the question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

UMG Developer SSChampion Points: 13482 More actions · Answer 13

Thanks for the question, it made me slow down and think.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 14

Nice question!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

TomThomson SSC Guru Points: 104773 More actions · Answer 15

Nice question.

But is there something wrong with the documentation? The BoL page referenced states

@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)

which suggests that the two rows logged for the insert of value 2 (which occurred in an explicit transaction) should have tc = 2 not tc = 1.

Tom