Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Triggers and Transactions


Triggers and Transactions

Author
Message
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3626 Visits: 3236
Hugo Kornelis (10/1/2010)
The description of the effects of ROLLBACK in a trigger is word for word the same for the current version and for SQL Server 2000.
See http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx.

I agree. I work both with sql2000 and sql2005 and the behavior is the same.
Results may be different if this option is set
SET IMPLICIT_TRANSACTIONS ON


honza.mf
honza.mf
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: 1771 Visits: 1323
Carlo Romagnano (10/1/2010)
Results may be different if this option is set
SET IMPLICIT_TRANSACTIONS ON


One thing I haven't counted with, when I was preparing the question.
It's a hell, versions, collations, options, session settings.

Thanks you have mentioned it here.



See, understand, learn, try, use efficient
© Dr.Plch
tommyh
tommyh
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 2000
Carlo Romagnano (10/1/2010)
[b]tommyh.
Running the code under 2000 all i get is
(1 row(s) affected)


under 2005 and 2008 i get
Server: Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

(1 row(s) affected)

Server: Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.


The effect is as you say the same the rollback performed.

/T

tommyh, in sql2000 I think you have this option set:
SET IMPLICIT_TRANSACTIONS ON
Set it off and then rerun the batch.
Also in sql2000 an error message should appear because of COMMIT TRAN when there are not pending transaction.


Nope not on. Turning it on = bad because without adding a nr of commits to the code... it just wont run.

Also there wont be an error message at the COMMIT TRAN because SQL just doesnt execute the commands after the failed insert. You can verify it yourself by adding something like a "select * from i_dont_have_a_table_called_this". Now unless you actually have a table called that you should get an error...which you dont. Or you could do a HUGE cross join and see that SQL just blazes past it.

/T
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16626 Visits: 17024
Great question. Really got my brain kick started this morning which I desperately needed because I was out of coffee at home this morning. ;-) Took me several trips through the trigger to figure out what it was doing.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6243 Visits: 10403
Great question; I really enjoyed working through it. Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Bradley Deem
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)Mr or Mrs. 500 (567 reputation)

Group: General Forum Members
Points: 567 Visits: 1248
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
BEGIN TRAN
INSERT INTO TranTest VALUES (2) -- Explict Transaction (@@Trancount = 2 inside of Trigger)
COMMIT TRAN
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
Source-NH
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1631 Visits: 479
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
Dan Guzman - Not the MVP
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: 736
OK, I'm the dummy this morning: Why wasn't (3,1) one of the inserted rows?
Oleg Netchaev
Oleg Netchaev
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: 1693 Visits: 1808
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
honza.mf
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: 1771 Visits: 1323
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
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