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

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

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

Cross-posted to 2 groups.

Show all headers | View raw

Gene Wirchenko (genew@ocis.net) writes:
>      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.

Actually, I recommend against using BEGIN and COMMIT TRANSACTION in 
triggers; only ROLLBACK makes sense.
 
>      In the trigger, because a raiserror() caused the catch block to
> be executed, a rollback will automatically occur.

This is getting confusing even for me! You see, normally RAISERROR does not 
terminate the batch, even if XACT_ABORT is on. But yes, in a trigger.
This is probably for compatibility reasons.
 
>      Were it not trigger code, then the setting of SET XACT_ABORT
> would matter.

Actually, XACT_ABORT matters also in triggers. It is just that the 
default is different.
 
>      Does the setting for SET XACT_ABORT get restored when a trigger
> finishes execution?  

Yes. The effect of any SET command is reverted when the scope in which the 
SET command was executed in exists. (With one single exception SET 
CONTEXT_INFO.)

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

XACT_ABORT ON extends into these procedures, and errors in the procedures 
will bubble up to the trigger. There is however a completely horrendeous
exception. Normally if you something like:

   CREATE PROCEDURE bad_tran AS BEGIN TRANSACTION

You will get an error about trancount mismatch. But if the procedure is 
called from a trigger, the error is suppressed. I reported this as a bug -
it was closed by design! (And I think I've seen it documented in Books
Online.)

>      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?
 
Many people put SET NOCOUNT ON in the top of their procedures. That could be 
a good place for SET XACT_ABORT ON as well.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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