Sql-server – Is it possible to raiserror within a For Delete Trigger that rolls back the deletes

deleteexceptionraiserrorsql servertrigger

There is a claim at the end of this accepted answer that if an error occurs within a "For Delete" trigger, the delete action would be rolled back because it is part of an implicit transaction.

However here is an example showing that the deleted rows remain deleted even though an error is raised:

create table T1 ( i1 int  );
go

create trigger T1_ForDelete on T1
for delete
as
raiserror('Raised 16', 16, 1);
raiserror('Raised 18', 18, 1);
raiserror('Raised #2 16', 16, 255);
raiserror('Raised #2 18', 18, 255);
go

insert into T1 (i1) values (1);

set xact_abort on; -- makes no difference

delete from T1;

Output (order changes):

Msg 50000, Level 16, State 1, Procedure T1_ForDelete, Line 4
Raised 16
Msg 50000, Level 16, State 255, Procedure T1_ForDelete, Line 6
Raised #2 16

(1 row(s) affected)
Msg 50000, Level 18, State 1, Procedure T1_ForDelete, Line 5
Raised 18
Msg 50000, Level 18, State 255, Procedure T1_ForDelete, Line 7
Raised #2 18

Then

select * from T1; -- Returns no records

Is this the expected behavior or is there a way to prevent deletion on an error (e.g. different severity/state)?

I cannot use an "Instead of Delete" because "on delete cascade" is employed. Create Trigger (MSDN), search "For INSTEAD OF"

Best Answer

In order for that to work as you've coded it above, you need a rollback after your RAISERROR or, as the comments correctly state, wrap in a TRY/CATCH.

By itself, it's not going to be treated the same as an actual statement or batch terminating error. For example, this will work the way you expect:

create table T1 ( i1 int  );
create table t2 (i2 int primary key);
go

create trigger T1_ForDelete on T1
for delete
as
    insert into t2 (i2)
    SELECT i1 
    FROM DELETED
go

insert into T1 (i1) values (1);
insert into t2 (i2) values (1);

delete from T1;
go

SELECT * from t1

In the above code the trigger will error, and cause the delete (outer) transaction to rollback. By itself, RAISERROR is only really transferring control and/or sending messages up the stack (so to speak)

BOL describes this behavior:

When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.

With a severity of 10 or lower in a TRY block.

With a severity of 20 or higher that terminates the database connection.

So RAISERROR outside the scope of a TRY block simply returns the error to the caller and is not treated as a statement-terminating error regardless of the severity you define.

Related Question