Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2002
    Posts
    9

    Unanswered: trigger rollback

    Thanking in advance to not be told to RTFM.

    If a trigger bails and is rolled back is the calling (triggering) statement also rolled back? Yes I'll take a url etc... blame it on me having to little coffee...

    zara

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    according to Books Online:

    If a ROLLBACK TRANSACTION is issued in a trigger:

    All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.

    The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.

    None of the statements in the batch after the statement that fired the trigger are executed.

    A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed, except that STATIC or INSENSITIVE cursors are left open if:

    CURSOR_CLOSE_ON_COMMIT is set OFF.

    The static cursor is either synchronous, or a fully populated asynchronous cursor.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Feb 2002
    Posts
    9
    Originally posted by Paul Young
    according to Books Online:

    If a ROLLBACK TRANSACTION is issued in a trigger:

    All data modifications made to that point in the current transaction are rolled back, including any that were made by the trigger.

    The trigger continues executing any remaining statements after the ROLLBACK statement. If any of these statements modify data, the modifications are not rolled back. No nested triggers are fired by the execution of these remaining statements.

    None of the statements in the batch after the statement that fired the trigger are executed.

    A ROLLBACK in a trigger closes and deallocates all cursors that were declared and opened in the batch containing the statement that fired the trigger. This includes cursors declared and opened in stored procedures called by the batch that fired the trigger. Cursors declared in a batch prior to the batch that fired the trigger are only closed, except that STATIC or INSENSITIVE cursors are left open if:

    CURSOR_CLOSE_ON_COMMIT is set OFF.

    The static cursor is either synchronous, or a fully populated asynchronous cursor.
    Thanks. I shoulda found that in BOL. In a nutshell: is the triggering statement and it's trigger and all it's actions all one transaction?

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    Could be. It depends on how you have written your code, if you have auto commit turned on, if you handle setting your own transactions.
    You might want to check out the following in the BOL index:

    transactions, overview
    SET IMPLICIT_TRANSACTIONS
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Feb 2002
    Posts
    9
    Originally posted by Paul Young
    Could be. It depends on how you have written your code, if you have auto commit turned on, if you handle setting your own transactions.
    You might want to check out the following in the BOL index:

    transactions, overview
    SET IMPLICIT_TRANSACTIONS
    Yeah thanks. Let assume IMPLICIT TRANSACTIONS (the default right) I'm NOT explicitly defining any transactions anywhere in code. Just a simple UPDATE trigger. If I roll it back on error will the calling transactions get rolled back?

    thanks for your time btw

  6. #6
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    in your example, all updates would be rolled back.
    Paul Young
    (Knowledge is power! Get some!)

  7. #7
    Join Date
    Feb 2002
    Posts
    9
    Originally posted by Paul Young
    in your example, all updates would be rolled back.
    Thanks tons.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •