5

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?

0

2 Answers 2

11

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

0
0

You could also put a try-catch around the insert statement and handle the error nicely.

create PROCEDURE insert_rain
   @rain_time DATETIME ,
   @location VARCHAR(100)
AS
BEGIN;
   SET XACT_ABORT ON;
   BEGIN TRANSACTION;
   PRINT 'before insert';

   BEGIN TRY
      insert INTO rain
         ( rain_time, location )
         values  ( @rain_time, @location );
      PRINT 'after insert';

      COMMIT;
   end try
   begin catch;
      if @@trancount > 0
         rollback;
      throw;
   end catch;
END;
go

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.