Trigger shouldn't block insert

Question

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?

Answer 1

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

Answer 2

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.