Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #350

Re: SSE 2008: Transactions and Rollbacks: When are they done?

Cross-posted to 2 groups.

Show all headers | View raw

On Wed, 18 May 2011 07:27:20 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

     Let me see if I follow.

>Gene Wirchenko (genew@ocis.net) writes:
>>      The insert trigger fires.  At the first statement of the insert
>> trigger's try block, @@trancount=1.  Why 1?
>
>When you are in a trigger, you are always in a transaction. If you did not 
>start one explicitly, there is still an implicit one defined by the 
>statement that fired the trigger. This is an essential point: the trigger is 
>part of the INSERT statemnet, and if the trigger fails, the INSERT statement 
>should also be rolled back.

     Had I not been checking trigger code, there would not have been
an implicit transaction created, and it would have been correct (and a
good idea) to have a BEGIN TRANSACTION.  Since the code in question is
a trigger, there is no need for a BEGIN TRANSACTION, but it does not
hurt.

    Correct?

>>      Supposedly, set xact_error on causes auto-rollbacks.  Fine, but
>> the above happens with set xact_error off (unless I am actually not
>> setting it in the right place.)
> 
>When you are in a trigger XACT_ABORT is ON by default. Furthermore, if you 
>roll back the transaction in the trigger, the batch is aborted.

     In the trigger, because a raiserror() caused the catch block to
be executed, a rollback will automatically occur.

     Correct?

     Were it not trigger code, then the setting of SET XACT_ABORT
would matter.

     Correct?

     Does the setting for SET XACT_ABORT get restored when a trigger
finishes execution?  How does this work when there are other
procedures called?  Same question, but is it any different if the
lower-level procedure raises an error (as does your error handler)?

>Error handling in SQL Server is a patchwork of inconsistencies. A lot 

     Eä Cthulhu!

>inherited from Sybase, but Microsoft has been careful to add their own 
>madness. Up to SQL 2000, an error in a trigger always aborted the batch, but 
>starting with SQL 2005 you can prevent that with an explicit SET XACT_ABORT 
>OFF in the trigger.

     Nothing that I have read has mentioned when to set SET
XACT_ABORT.  Is any time before an error occurs acceptable?  From your
last paragraph, the answer to this appears to be yes.

     Correct?

Sincerely,

Gene Wirchenko

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-17 15:09 -0700
  Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 07:27 +0000
    Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-18 10:50 -0700
      Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 23:39 +0200
        Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 13:10 -0700
          Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-19 23:38 +0200
            Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 23:20 -0700
              Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-20 07:32 +0000

csiph-web