Path: csiph.com!x330-a1.tempe.blueboxinc.net!feeder1.hal-mli.net!feeder.news-service.com!85.214.198.2.MISMATCH!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming Subject: Re: SSE 2008: Transactions and Rollbacks: When are they done? Date: Wed, 18 May 2011 07:27:20 +0000 (UTC) Organization: Erland Sommarskog Lines: 31 Message-ID: References: <91q5t6h56vk3db1ipt8eacd5407jc08c7f@4ax.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Injection-Date: Wed, 18 May 2011 07:27:20 +0000 (UTC) Injection-Info: mx04.eternal-september.org; posting-host="G7+Jz22XqYCG8C6rb1H3YA"; logging-data="30739"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1/OUqTepjVpOMzz4zSYWHMz" User-Agent: Xnews/2005.10.03 Mime-proxy/1.4.c.4 (Win32) Cancel-Lock: sha1:s57eImPJBnNFp4OA3eNBCYdJnQ0= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:345 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