Sql-server – XACT_ABORT ON not working as expected on SQL Server 2012

error handlingsql serversql-server-2012

It looks like XACT_ABORT ON is not working as expected. Here are a table and a procedure that inserts into it:

CREATE TABLE rain
    (
      rain_time DATETIME ,
      location VARCHAR(100)
    );
GO

CREATE PROCEDURE insert_rain
    @rain_time DATETIME ,
    @location VARCHAR(100)
AS
    BEGIN;
        SET XACT_ABORT ON;
        BEGIN TRANSACTION;
        PRINT 'before insert';
        INSERT  INTO rain
                ( rain_time, location )
        VALUES  ( @rain_time, @location );
        PRINT 'after insert';
        COMMIT;
    END;
GO

I've created a trigger to imitate a runtime error:

CREATE TRIGGER rain_no_insert
ON rain
FOR INSERT 
AS
RAISERROR('Cannot insert', 16, 1);
GO

When I invoke the insert procedure, I do get the error, but the execution does not stop, because the last PRINT prints its 'after insert' message:

EXEC insert_rain
    @rain_time = '2015-03-30 12:34:56',
    @location = 'Wautoma, WI';

before insert
Msg 50000, Level 16, State 1, Procedure rain_no_insert, Line 5
Cannot insert

(1 row(s) affected)
after insert

Also the transaction commits:

SELECT * FROM dbo.rain;

rain_time               location
----------------------- ---------------------------------
2015-03-30 12:34:56.000 Wautoma, WI

Why my XACT_ABORT does not abort?

Best Answer

Check Microsoft documentation, specifically the top line:

The THROW statement honors SET XACT_ABORT. RAISERROR does not. New applications should use THROW instead of RAISERROR.

I believe that is your problem, right there.

https://msdn.microsoft.com/en-us/library/ms188792.aspx

Related Question