SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Honza Mensdorff / Triggers and Transactions / Latest Posts

RE: Triggers and Transactions

L' Eomot Inversé — Tue, 05 Oct 2010 12:39:59 GMT

[quote][b]honza.mf (10/5/2010)[/b][hr]I don't know. You are right but experiments show this behaviour.Many times I read BOL or some other documentation I feel like being in the helicopter.[/quote]BoL often makes me feel like that. And the experiment in this case shows another oddity: when in autocommit mode, a reference to @@tc in the statement bring auto-committed will be evaluated before the transaction is started (hence the 0 count in the first and fourth rows). I've written quite a lot of things that look at @@tc for various reasons, but results like these make me very very careful about doing it!

RE: Triggers and Transactions

honza.mf — Tue, 05 Oct 2010 08:31:20 GMT

[quote][b]Tom.Thomson (10/5/2010)[/b][hr]Nice question. But is there something wrong with the documentation? The BoL page referenced states [quote]@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)[/quote] 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.[/quote]I don't know. You are right but experiments show this behaviour.Many times I read BOL or some other documentation I feel like being in the helicopter.

RE: Triggers and Transactions

L' Eomot Inversé — Tue, 05 Oct 2010 07:36:16 GMT

Nice question. But is there something wrong with the documentation? The BoL page referenced states [quote]@@TRANCOUNT is incremented by one when entering a trigger, even when in autocommit mode. (The system treats a trigger as an implied nested transaction.)[/quote] 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.

RE: Triggers and Transactions

Koen Verbeeck — Mon, 04 Oct 2010 00:15:45 GMT

Nice question!

RE: Triggers and Transactions

UMG Developer — Fri, 01 Oct 2010 22:33:21 GMT

Thanks for the question, it made me slow down and think.

RE: Triggers and Transactions

SQLRNNR — Fri, 01 Oct 2010 14:26:29 GMT

Thanks for the question.

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 12:51:21 GMT

[quote][b]Oleg Netchaev (10/1/2010)[/b][hr][quote][b]honza.mf (10/1/2010)[/b][hr]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.[/quote]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[/quote]I understood you picked two points of view. Both are very important.

RE: Triggers and Transactions

Oleg Netchaev — Fri, 01 Oct 2010 12:27:25 GMT

[quote][b]honza.mf (10/1/2010)[/b][hr]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.[/quote]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

RE: Triggers and Transactions

Source-NH — Fri, 01 Oct 2010 12:06:56 GMT

[quote][b]rtelgenhoff (10/1/2010)[/b][hr]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.'[/quote]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.

RE: Triggers and Transactions

rtelgenhoff — Fri, 01 Oct 2010 11:48:23 GMT

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.'

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 11:23:15 GMT

[quote][b]Oleg Netchaev (10/1/2010)[/b][hr][quote][b]Hugo Kornelis (10/1/2010)[/b][hr]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).[/quote]There is a minor change in what is reported. In SQL Server 2000 it was somewhat common to issue [b]rollback tran[/b] 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,[b] transaction ended in the trigger[/b] error is reported. In this question, the most interesting scenario to examine is this (I will also add a "manual" insert into log):[code="sql"]begin tran;insert into TranLog values (20, 20);insert into TranTest values (3);commit tran;go[/code]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 5The 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:[code="sql"]--replaceif @num %2 = 1 rollback tran;-- with thisif @num %2 = 1 begin rollback tran; begin tran;end;[/code]Trancount values inserted in the log table aside, the result is the same, both[code="sql"]insert into TranLog values (20, 20); -- after begin tran, before the triggerinsert into TranLog values (@num, @tc); --inside the trigger before rollback[/code]are rolled back and insert into TranLog (num, trancount) values (10 + @num, @tc); -- inside the trigger after rollbackis executed.Oleg[/quote]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.

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 11:17:40 GMT

[quote][b]Rich-403221 (10/1/2010)[/b][hr]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.[/quote]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.

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 11:11:40 GMT

[quote][b]Dan Guzman - Not the MVP (10/1/2010)[/b][hr]OK, I'm the dummy this morning: Why wasn't (3,1) one of the inserted rows?[/quote]Because the trigger allows only even numbers and 3 is an odd number.

RE: Triggers and Transactions

Oleg Netchaev — Fri, 01 Oct 2010 10:15:54 GMT

[quote][b]Hugo Kornelis (10/1/2010)[/b][hr]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).[/quote]There is a minor change in what is reported. In SQL Server 2000 it was somewhat common to issue [b]rollback tran[/b] 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,[b] transaction ended in the trigger[/b] error is reported. In this question, the most interesting scenario to examine is this (I will also add a "manual" insert into log):[code="sql"]begin tran;insert into TranLog values (20, 20);insert into TranTest values (3);commit tran;go[/code]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 5The 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:[code="sql"]--replaceif @num %2 = 1 rollback tran;-- with thisif @num %2 = 1 begin rollback tran; begin tran;end;[/code]Trancount values inserted in the log table aside, the result is the same, both[code="sql"]insert into TranLog values (20, 20); -- after begin tran, before the triggerinsert into TranLog values (@num, @tc); --inside the trigger before rollback[/code]are rolled back and insert into TranLog (num, trancount) values (10 + @num, @tc); -- inside the trigger after rollbackis executed.Oleg

RE: Triggers and Transactions

Dan Guzman - Not the MVP — Fri, 01 Oct 2010 09:55:51 GMT

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

RE: Triggers and Transactions

Source-NH — Fri, 01 Oct 2010 09:06:49 GMT

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.

RE: Triggers and Transactions

Bradley Deem — Fri, 01 Oct 2010 09:05:26 GMT

Good question. A slight variation on the question helps explain the behavior of @@TRANCOUNT which isn't very clearly stated in the BOL.[quote]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.[/quote]So consider the following.[code="sql"]CREATE TABLE TranTest(num int)GOCREATE TRIGGER TrgTranTest ON TranTest FOR INSERTAS BEGIN SET NOCOUNT ON PRINT 'TranCount is ' + CAST(@@TRANCOUNT as VARCHAR(10))ENDGOSET NOCOUNT ONINSERT INTO TranTest VALUES (1) -- Implicit Transaction (@@Trancount = 1 inside of Trigger)GOSET NOCOUNT ONBEGIN TRANBEGIN TRANINSERT INTO TranTest VALUES (2) -- Explict Transaction (@@Trancount = 2 inside of Trigger)COMMIT TRANCOMMIT TRANGOSET NOCOUNT ONBEGIN TRANINSERT INTO TranTest VALUES (3) -- Explicit Transaction (@@Trancount = 1 inside of Trigger)COMMIT TRANGO[/code]Which produces the following output.[quote]TranCount is 1TranCount is 2TranCount is 1[/quote]

RE: Triggers and Transactions

WayneS — Fri, 01 Oct 2010 08:30:33 GMT

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

RE: Triggers and Transactions

Sean Lange — Fri, 01 Oct 2010 07:30:35 GMT

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.

RE: Triggers and Transactions

tommyh — Fri, 01 Oct 2010 07:04:29 GMT

[quote][b]Carlo Romagnano (10/1/2010)[/b][hr][quote][b]tommyh.Running the code under 2000 all i get is [code="sql"](1 row(s) affected)[/code]under 2005 and 2008 i get[code="sql"]Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.(1 row(s) affected)Server: Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.[/code]The effect is as you say the same the rollback performed./T[/quote]tommyh, in sql2000 I think you have this option set:SET IMPLICIT_TRANSACTIONS ONSet 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.[/quote]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

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 06:23:41 GMT

[quote][b]Carlo Romagnano (10/1/2010)[/b]Results may be different if this option is set [code]SET IMPLICIT_TRANSACTIONS ON[/code][/quote]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.

RE: Triggers and Transactions

Carlo Romagnano — Fri, 01 Oct 2010 06:15:19 GMT

[quote][b]Hugo Kornelis (10/1/2010)[/b][hr]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 [url=http://msdn.microsoft.com/en-us/library/ms181299.aspx]http://msdn.microsoft.com/en-us/library/ms181299.aspx[/url] and [url=http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx]http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx[/url].[/quote]I agree. I work both with sql2000 and sql2005 and the behavior is the same.Results may be different if this option is set [code]SET IMPLICIT_TRANSACTIONS ON[/code]

RE: Triggers and Transactions

Carlo Romagnano — Fri, 01 Oct 2010 06:06:56 GMT

[quote][b]tommyh.Running the code under 2000 all i get is [code="sql"](1 row(s) affected)[/code]under 2005 and 2008 i get[code="sql"]Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.(1 row(s) affected)Server: Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.[/code]The effect is as you say the same the rollback performed./T[/quote]tommyh, in sql2000 I think you have this option set:SET IMPLICIT_TRANSACTIONS ONSet 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.

RE: Triggers and Transactions

ziangij — Fri, 01 Oct 2010 02:49:19 GMT

thanks for this nice example... :-)

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 02:02:04 GMT

[quote][b]Hugo Kornelis (10/1/2010)[/b]I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.[/quote]Yes. You can have constraints in some other application tier too. But triggers are the last and safest barrier.

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 01:51:52 GMT

[quote][b]tommyh (10/1/2010)[/b]Running the code under 2000 all i get is [code="sql"](1 row(s) affected)[/code]under 2005 and 2008 i get[code="sql"]Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.(1 row(s) affected)Server: Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.[/code]The effect is as you say the same the rollback performed./T[/quote]Thanks for this observation, in time I wrote the question I missed this. You are right the messages are more clear.

RE: Triggers and Transactions

Hugo Kornelis — Fri, 01 Oct 2010 01:46:19 GMT

Thanks, Tommy! I don't have SQL2000 available to test, so I had to go by the documentation. And 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 [url=http://msdn.microsoft.com/en-us/library/ms181299.aspx]http://msdn.microsoft.com/en-us/library/ms181299.aspx[/url] and [url=http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx]http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx[/url].

RE: Triggers and Transactions

tommyh — Fri, 01 Oct 2010 01:39:25 GMT

[quote][b]Hugo Kornelis (10/1/2010)[/b][hr][quote][b]tommyh (10/1/2010)[/b][hr]Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.[/quote]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).[quote]However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?[/quote]I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.[/quote]Running the code under 2000 all i get is [code="sql"](1 row(s) affected)[/code]under 2005 and 2008 i get[code="sql"]Server: Msg 3609, Level 16, State 1, Line 1The transaction ended in the trigger. The batch has been aborted.(1 row(s) affected)Server: Msg 3609, Level 16, State 1, Line 2The transaction ended in the trigger. The batch has been aborted.[/code]The effect is as you say the same the rollback performed./T

RE: Triggers and Transactions

Hugo Kornelis — Fri, 01 Oct 2010 01:33:30 GMT

[quote][b]tommyh (10/1/2010)[/b][hr]Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back.[/quote]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).[quote]However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?[/quote]I develop a code generator that offers much more constraints that just the standard SQL Server constraints. Triggers are used to check modifications against these constraints. If violations are found, the trigger rolls back the transaction, logs the violation, and sends an error message to the client.

RE: Triggers and Transactions

honza.mf — Fri, 01 Oct 2010 01:11:16 GMT

[quote][b]tommyh (10/1/2010)[/b][hr]However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?[/quote]Rolling back a transaction inside a trigger is used to avoid inconsistent data. Eg.[code="sql"]select @s = somedata, @o = otherdata from insertedif @s < 0 and @o = 1 then rollback transaction[/code]Or you can avoid change in some column[code="sql"]if update(pk)begin raiserror('Update of primary key is not allowed',11,1) rollback transactionend[/code]

RE: Triggers and Transactions

tommyh — Fri, 01 Oct 2010 00:14:36 GMT

Good question, didnt know it behaved that way. Also good that Microsoft change the way that SQL handles this. In 2000 you wouldnt have a clue that your insert just got shafted. Atleast in 2005 and 2008 you get an error back. However i still wonder in what circumstances that rolling back a transactions inside a trigger is a good thing. Isnt anything i have ever felt the need to use. But then i only use triggers for logging. So curious if anyone has an exempel on when this would be usefull?/T

Triggers and Transactions

honza.mf — Thu, 30 Sep 2010 22:31:25 GMT

Comments posted to this topic are about the item [B]Triggers and Transactions[/B]