SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers and Transactions


Triggers and Transactions

Author
Message
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1931 Visits: 1323
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.



See, understand, learn, try, use efficient
© Dr.Plch
honza.mf
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1931 Visits: 1323
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.



See, understand, learn, try, use efficient
© Dr.Plch
rtelgenhoff
rtelgenhoff
Right there with Babe
Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)Right there with Babe (724 reputation)

Group: General Forum Members
Points: 724 Visits: 363
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
Source-NH
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1719 Visits: 479
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
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1773 Visits: 1813
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
honza.mf
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1931 Visits: 1323
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.



See, understand, learn, try, use efficient
© Dr.Plch
SQLRNNR
SQLRNNR
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31928 Visits: 18551
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

UMG Developer
UMG Developer
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2662 Visits: 2204
Thanks for the question, it made me slow down and think.
Koen Verbeeck
Koen Verbeeck
One Orange Chip
One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)One Orange Chip (27K reputation)

Group: General Forum Members
Points: 27121 Visits: 13268
Nice question!


How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
My blog at SQLKover.

MCSE Business Intelligence - Microsoft Data Platform MVP
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14202 Visits: 12197
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search