asked on

How do I continue with an update if the table's trigger is causing an error?

If I update a record in my e.g. "Address" table, an update trigger attempts to insert the record from the "Deleted" table into a separate archive table.

If for some reason that trigger encounters an error, my stored procedure for updating an address fails completely even though there's nothing wrong with the original update.

The archiving of old "Address" records is not high priority so I would like to know how allow the original (error-free) update to go ahead regardless of whether the trigger has a problem. I had considered using the update stored procedure to perform the update and the archive but then I don't have access to the "Inserted/Deleted" tables.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

milesryoung

ASKER

Thanks for your reply. That is interesting to know but the OUTPUT part would be nested inside the main update/insert. If that caused an error (like how the trigger would) I couldn't continue with the normal update/insert could I? You do deserve a point or 50 for bringing "OUTPUT" to my attention though :-)

chapmandew

Yes, it can. If you insert the changes into a table properly then you should be fine...it is just capturing what is modified. From there, its just like a procedure and you can use that altered data as you see fit. So, so long as you have the insert statement into the table setup properly from the OUTPUT statement then you should be good.