Triggers and Transactions

  • 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

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

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

  • 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

  • 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

  • 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

  • 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

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

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

  • 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

  • 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

  • Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

  • Nice question!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • 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

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply