Post reply

automatic rollbacks in triggers - what does NOT cause them

  • November 9, 2004 at 6:57 am

    #87028

    If in a trigger, a SQL statement (such as an update or insert) receives an error of any severity level ( if @@Error <> 0 at the end of the statement), would SQL automatically roll the trigger/transaction back and end the batch? If not, I assume there cases where the trigger must be checking for @@error <>0 in order to perform a manual rollback.   

     

    The answer to the preceding question resolves my real question:  Should you check @@ERROR  <> 0 and perform rollbacks in triggers after every SQL statement or is this essentially done for you by the way SQL Server processes all severities of errors in triggers?

     

    From my testing, it appears that error severities that do not cause automatic rollbacks in 'normal' statements/batches will cause a rollback and end to the batch if the same error is received in a trigger.  If this is true - do you need to error check at all in a trigger?

  • November 9, 2004 at 7:59 am

    #529456

    Jane,

    Unfortunately, the answer is: It depends

    I recommend you read this article by Erland Sommarskog, which covers this topic and related issues in great detail:

    http://www.sommarskog.se/error-handling-I.html

    --
    Adam Machanic
    whoisactive

  • November 9, 2004 at 9:02 am

    #529473

    Thanks.  This article says what I thought but couldn'tfind in print.  It's a good concise article. 

    I still would like to be able to find it in print from Microsoft.  But, according to this article it is true that SQL will automatically roll back all errors in a trigger.  I can cause a rollback of my own for any reason I want and if I do, the flow of control will be different than if SQL issues the rollback automatically.

Viewing 3 posts - 1 through 2 (of 2 total)

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