Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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"

share|improve this question
2  
I went through this when creating our trigger template a good while ago, so my memory is a bit fuzzy on the details. If RAISERROR is used in a CATCH block, it rolls back the transaction automatically; if there's no TRY/CATCH, you have to ROLLBACK manually. I have no idea why this difference exists. –  Jon Seigel Jan 30 at 20:08
    
Yes! You made my day. Want to post as an answer? I modified the trigger body: "as begin try, ..., end try, begin catch, throw; end catch" –  crokusek Jan 30 at 20:15
    
I'd really like to see someone provide an answer as to why this behaviour is like it is... I don't have time right now to get back into the minutae of it all. If no one has taken the bait by later, I'll move my comment into an answer. –  Jon Seigel Jan 30 at 20:18

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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