XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   On Wed, 18 May 2011 07:27:20 +0000 (UTC), Erland Sommarskog   
    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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)