SQL Server Rollback my transaction using THROW

Question

I have an INSERT trigger on one of my tables that throws THROW when it finds a duplicate. The problem is that my transactions seem to be implicitly rolling back at this point - this is the problem, I want to control when the transactions are rolling back.

The problem can be recreated using this script:

CREATE TABLE xTable ( id int identity not null ) go create trigger xTrigger on xTable after insert as print 'inserting...'; throw 1600000, 'blah', 1 go begin tran insert into xTable default values rollback tran go drop table xTable 

If you start a rollback transition, it will tell you that there is no initial transition.

If I swap THROW for a “normal” exception (for example, SELECT 1/0), the transaction will not roll back.

I checked the xact_abort flag - and it is off.

Using SQL Server 2012 and testing through SSMS

Any thanks, thanks.

EDIT After reading the articles published by @Dan Guzman, I came to the following conclusion / summary ...

SQL Server automatically sets XACT_ABORT ON to triggers.

My example (above) does not illustrate my situation - in fact, I am creating an extended constraint using a trigger.

My use case was invented, I tried to test several situations in the SAME unit test (not in the real world situation, but not in the good unit test practice).

My processing of extended constraint checking and throwing an error in the trigger is correct, however there is no real situation in which I do not want to roll back the transaction.

It may be useful to set XACT_ABORT OFF inside the trigger for a specific case; but your transaction will still be undermined by common batch processing interrupt errors (e.g. deadlocks).

Historical reasons aside, I do not agree with SQL Server processing this; just because there is no current situation in which you would like to continue the transaction does not mean that such a situation may not occur. I would like to see that you can configure SQL Server to maintain transaction integrity if your chosen architecture should have strictly controlled transactions by origin, i.e. "Only he who begins the transaction must complete it." This is, in addition to the usual fail-safe safes, for example, if your code has never been reached due to a system crash, etc.

Answer 1

THROW terminates the package if it goes beyond TRY/CATCH ( https://msdn.microsoft.com/en-us/library/ee677615.aspx ). Here, it is understood that further processing of the package is not performed, including statements following the insert. You need to either surround your INSERT with TRY/CATCH , or use RAISERROR instead of THROW .

T-SQL error handling is a pretty big and complicated question. I suggest you familiarize yourself with a series of error handling articles by Erland Sommarskog: http://www.sommarskog.se/error_handling/Part1.html . Most relevant is Can I Prevent the Trigger from Rolling Back the Transaction? http://www.sommarskog.se/error_handling/Part3.html#Triggers . To remove from the point of view of best practice is that triggers are not the right solution if you apply business rules in a trigger without rollback.