SQL Server Rolls back my transaction when using THROW

Question

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

The issue can be re-created with 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 run the rollback tran - it will tell you there is no begin tran.

If i swap the THROW for a 'normal' exception (like SELECT 1/0) the transaction is not rolled back.

I have checked xact_abort flag - and it is off.

Using SQL Server 2012 and testing through SSMS

Any help appreciated, thanks.

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

SQL Server automatically sets XACT_ABORT ON in triggers.

My example (above) does not illustrate my situation - In reality I'm creating an extended constraint using a trigger.

My use case was contrived, I was trying to test multiple situations in the SAME unit test (not a real world situation, and NOT good unit test practice).

My handling of the extended constraint check and throwing an error in the trigger is correct, however there is no real situation in which I would not want to rollback the transaction.

It can be useful to SET XACT_ABORT OFF inside a trigger for a particular case; but your transaction will still be undermined by general batch-aborting errors (like deadlocks).

Historical reasons aside, i don't agree with SQL Server's handling of this; just because there is no current situation in which you'd like to continue the transaction, does not mean such a situation may not arise. I'd like to see one able to setup SQL Server to maintain the integrity of transactions, if your chosen architecture is to have transactions strictly managed at origin, i.e. "he alone who starts the transaction, must finish it". This, aside from usual fail-safes, e.g. if your code is never reached due to system failure etc.

Answer 1

THROW will terminate the batch when outside the scope of TRY/CATCH (https://msdn.microsoft.com/en-us/library/ee677615.aspx). The implication here is that no further processing of the batch takes place, including the statements following the insert. You'll need to either surround your INSERT with a TRY/CATCH or use RAISERROR instead of THROW.

T-SQL error handing is a rather large and complex topic. I suggest you peruse the series of error-handling articles by Erland Sommarskog: http://www.sommarskog.se/error_handling/Part1.html. Most relevant here is the topic Can I Prevent the Trigger from Rolling Back the Transaction? http://www.sommarskog.se/error_handling/Part3.html#Triggers. The take away from a best practices point of view is that triggers are not the right solution if you enforce business rules in a trigger without a rollback.