Triggers and Transactions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Honza Mensdorff » Triggers and Transactions

32 posts, Page 2 of 4

««1 2 3 4 »»»

Triggers and Transactions

Rate Topic

Display Mode

Topic Options

Author

Message

Carlo Romagnano

Posted Friday, October 01, 2010 6:15 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 1:51 AM
Points: 1,971, Visits: 1,821

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

Post #996576

honza.mf

Posted Friday, October 01, 2010 6:23 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:14 AM
Points: 1,102, Visits: 1,197

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

Post #996581

tommyh

Posted Friday, October 01, 2010 7:04 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Friday, May 10, 2013 3:15 AM
Points: 1,476, Visits: 1,943

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

Post #996597

Sean Lange

Posted Friday, October 01, 2010 7:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Today @ 7:20 AM
Points: 8,594, Visits: 8,234

Great question. Really got my brain kick started this morning which I desperately needed because I was out of coffee at home this morning. Wink

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

Post #996620

WayneS

Posted Friday, October 01, 2010 8:30 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:38 PM
Points: 6,368, Visits: 8,230

Great question; I really enjoyed working through it. Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008

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

Post #996685

Bradley Deem

Posted Friday, October 01, 2010 9:05 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:22 AM
Points: 551, Visits: 1,150

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

Post #996752

Source-NH

Posted Friday, October 01, 2010 9:06 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:07 AM
Points: 1,619, Visits: 473

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.

Post #996753

Dan Guzman - Not the MVP

Posted Friday, October 01, 2010 9:55 AM

Say Hey Kid

Group: General Forum Members
Last Login: Friday, April 26, 2013 11:57 AM
Points: 708, Visits: 660

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

Post #996794

Oleg Netchaev

Posted Friday, October 01, 2010 10:15 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:53 AM
Points: 1,662, Visits: 1,709

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

Post #996813

honza.mf

Posted Friday, October 01, 2010 11:11 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:14 AM
Points: 1,102, Visits: 1,197

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.

Post #996860

« Prev Topic | Next Topic »

32 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions