Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Triggers and Transactions Expand / Collapse
Author
Message
Posted Friday, October 1, 2010 11:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,365, Visits: 1,314
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
Post #996865
Posted Friday, October 1, 2010 11:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,365, Visits: 1,314
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
Post #996868
Posted Friday, October 1, 2010 11:48 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Monday, August 25, 2014 2:13 PM
Points: 703, Visits: 324
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.'
Post #996887
Posted Friday, October 1, 2010 12:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:16 AM
Points: 1,626, Visits: 478
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.
Post #996900
Posted Friday, October 1, 2010 12:27 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:12 AM
Points: 1,676, Visits: 1,755
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
Post #996909
Posted Friday, October 1, 2010 12:51 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,365, Visits: 1,314
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
Post #996931
Posted Friday, October 1, 2010 2:26 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #997003
Posted Friday, October 1, 2010 10:33 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 9:38 AM
Points: 2,163, Visits: 2,189
Thanks for the question, it made me slow down and think.
Post #997140
Posted Monday, October 4, 2010 12:15 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
Nice question!



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

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #997367
Posted Tuesday, October 5, 2010 7:36 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:59 PM
Points: 8,748, Visits: 9,296
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
Post #998349
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse