Error handling in triggers
-
I like triggers, but it seems that it is hard to handle errors in triggers. I am running a stored procedure that uses a cursor (I know, I know, I couldn't find a better way to do it) to loop through many records in a staging table and inserting these records into another table, which has a trigger on it. If the trigger fails it stops the calling stored procedure. I want to be able to handle the error and continue my looping. I did put a rollback command and raise error command in the trigger. Any suggestions?
-
I am running a stored procedure that uses a cursor (I know, I know, I couldn't find a better way to do it) to loop... I'm thinking that you should post THAT code with a description and see if we can help you eliminate the cursor, first...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's not just hard, it is impossible to handle an error in a trigger.
A trigger is considered to be part of the statement that caused the trigger. You can't check @@ERROR in a trigger, the trigger is immediately abandoned and the execution context returns to the original statement (unless the error is severe enough to abort the entire batch).
You can either remove constraints that are causing the errors (and give up on data integrity), or put code in the stored procedure to look for error conditions before causing them.
Or upgrade to SQL 2005 and enjoy the benefits of TRY - CATCH.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply