Triggers and Transactions

  • honza.mf

    SSCertifiable

    Points: 5519

    Comments posted to this topic are about the item Triggers and Transactions



    See, understand, learn, try, use efficient
    © Dr.Plch

  • tommyh

    SSCertifiable

    Points: 6252

    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

  • honza.mf

    SSCertifiable

    Points: 5519

    tommyh (10/1/2010)


    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?

    Rolling back a transaction inside a trigger is used to avoid inconsistent data. Eg.

    select @S = somedata, @o = otherdata from inserted

    if @S < 0 and @o = 1 then

    rollback transaction

    Or you can avoid change in some column

    if update(pk)

    begin

    raiserror('Update of primary key is not allowed',11,1)

    rollback transaction

    end



    See, understand, learn, try, use efficient
    © Dr.Plch

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    tommyh (10/1/2010)


    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.

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

    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?

    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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • tommyh

    SSCertifiable

    Points: 6252

    Hugo Kornelis (10/1/2010)


    tommyh (10/1/2010)


    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.

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

    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?

    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.

    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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    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 http://msdn.microsoft.com/en-us/library/ms181299.aspx and http://msdn.microsoft.com/en-us/library/aa238433%28SQL.80%29.aspx.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • honza.mf

    SSCertifiable

    Points: 5519

    tommyh (10/1/2010)

    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

    Thanks for this observation, in time I wrote the question I missed this. You are right the messages are more clear.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • honza.mf

    SSCertifiable

    Points: 5519

    Hugo Kornelis (10/1/2010)

    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.

    Yes. You can have constraints in some other application tier too.

    But triggers are the last and safest barrier.



    See, understand, learn, try, use efficient
    © Dr.Plch

  • ziangij

    SSCertifiable

    Points: 6569

    thanks for this nice example... 🙂

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    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.

  • Carlo Romagnano

    SSC-Insane

    Points: 21712

    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

    SSCertifiable

    Points: 5519

    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

    SSCertifiable

    Points: 6252

    Carlo Romagnano (10/1/2010)


    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

    SSC Guru

    Points: 286408

    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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • WayneS

    SSC Guru

    Points: 95341

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

Viewing 15 posts - 1 through 15 (of 32 total)

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