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

3 Answers 3

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

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

YES

The trigger fails on its normal instructions, null values, etc

::Use an Instead of insert trigger start with

select into #inserted from Inserted

declare @query nvarchar(max),@iretval int

::You can bypass the fail by building each instruction into a query

set @query=N'update.... etc '

::it is also a good idea to check query before the sp_executesql, because ::if the query is null then the iretval will be 0 anyway i.e.

if @Query is null begin set @iretval=999 goto last end

::then

exec @Iretval=sp_executesql @query [[parmameters ] outvalue] if @iretval>0 goto Last

:: your insert should carry a flag or something to show if the trigger worked allowing subsequent processing that the trigger did not do assuming this is required

Last

if @iretval=0 update #inserted set flag=ok

insert into table select * from #inserted

::note. you must check each statement, and if you have to retrieve values from other tables check the values are valid and not isnull.

if anywants an example. email me jhodgson@tsti.co.za

share|improve this answer

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.