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.

I have a trigger on insert but if the trigger fails the insert fails too.

is there a way to let the insert proceed even if the trigger fails?

EDIT: I use a trigger to send a email when a new record is entered. I want the record to be saved regardless if the email was sent or not..

How would I do that from a sp?

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

No

  • A trigger is part of the transaction
  • An error will abort the INSERT statement (if not in an explicit transaction)
  • An error in a trigger will doom a transaction (that is, must be rolled back)
  • An error in a trigger where TRY/CATCH blocks are not used (around the INSERT too) will abort the batch

Don't use a trigger. Use a stored procedure instead to decouple the logic from the INSERT.

Personally, a trigger failing means some complex DRI has failed or I can't write to a history table. That is a bad thing...

Edit:

  • Use Service Broker to send an notification from the trigger.
  • Another piece of code will pick it up and send asynchronously

I haven't implemented myself so I'll have to refer you to Google

share|improve this answer
    
Thanks for that info. I edited the question to address your answer.. –  Ezi Oct 4 '11 at 21:19
add comment

If you are not comfortable with Service Broker, you could still use the trigger but instead of sending the email, it inserts the contents of the email into some sort of EmailQueue table. You would then have an asynchronouse process periodically check that table and process the emails separately, deleting them from the queue when sent.

Decoupling the generating of the mail with the sending of the mail will prevent the inserts from failing, and will allow you to troubleshoot mail issues while still capturing new mails to be sent.

share|improve this answer
1  
This is the way it's set up right now... I want to avoid this process by simply doing a trigger. but it looks like its not practical. Thanks –  Ezi Oct 5 '11 at 14:12
add comment

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.