XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)