XPost: microsoft.public.sqlserver.programming   
   From: esquel@sommarskog.se   
      
   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.   
      
      
   >      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.   
      
   Error handling in SQL Server is a patchwork of inconsistencies. A lot   
   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.   
      
   --   
   Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se   
      
   Books Online for SQL Server 2005 at   
   http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx   
   Books Online for SQL Server 2000 at   
   http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)