How to abort an insert of multiple rows in a trigger

Question

Hi, With sql Server 2005. I have declared a trigger that get fired "AFTER INSERT, UPDATE" , in this trigger I'm using a WHILE and a CURSOR to loop on the INSERTED table's rows. When I find a row that does not sotisfy a specific condition:

I want the trigger to rise an error and do not insert any of the rows that fired the trigger (not even those that already satisfaied my condition). <--- I don't know how to do this!

Can you tell me how can I rise the error and prevent the insertion?

Answer 1

use rollback

IF <some condition>
BEGIN
   RAISERROR ('condition doesn't satisfy something', 16, 1)
   ROLLBACK TRANSACTION
END

Answer 2

I'm not sure what logic you are doing in that cursor loop, but if at all possible try to replace the cursor loop with a query:

if exists (select PK from INSERTED where .....)
BEGIN
    --from @SQLMenace's answer
    RAISERROR ('condition doesn't satisfy something', 16, 1)
    ROLLBACK TRANSACTION

END

a cursor in a trigger gives me a bad feeling: locking, blocking, and slow come to mind...