Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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.
0
milesryoung
Asked:
milesryoung
  • 2
1 Solution
 
chapmandewCommented:
It is because the trigger executes in the same transaction scope as the original update.

If you're using 2005, you still have access to the inserted/deleted tables on the update outside of the trigger if you use the OUTPUT clause:

http://articles.techrepublic.com.com/5100-10878_11-6074046.html
0
 
milesryoungAuthor Commented:
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 :-)
0
 
chapmandewCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now