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 01, 2010 6:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:53 AM
Points: 2,393, Visits: 2,286
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
Posted Friday, October 01, 2010 6:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:47 PM
Points: 1,298, Visits: 1,306
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
Posted Friday, October 01, 2010 7:04 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Thursday, November 21, 2013 11:33 PM
Points: 1,481, Visits: 1,959
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
Posted Friday, October 01, 2010 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:24 PM
Points: 11,990, Visits: 11,007
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)
Post #996620
Posted Friday, October 01, 2010 8:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:59 AM
Points: 6,544, Visits: 8,761
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
Posted Friday, October 01, 2010 9:05 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 9:28 PM
Points: 554, Visits: 1,178
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
Posted Friday, October 01, 2010 9:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, September 19, 2013 7:41 AM
Points: 1,626, Visits: 477
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
Posted Friday, October 01, 2010 9:55 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, September 30, 2013 9:43 AM
Points: 710, Visits: 664
OK, I'm the dummy this morning: Why wasn't (3,1) one of the inserted rows?
Post #996794
Posted Friday, October 01, 2010 10:15 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:53 PM
Points: 1,676, Visits: 1,744
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
Posted Friday, October 01, 2010 11:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:47 PM
Points: 1,298, Visits: 1,306
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
Post #996860
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse