How to make the insert on the row occur even if the trigger fails.

Question

How to make the insert on the row occur even if the trigger fails.

doran_doran

SSC-Addicted

Points: 411
More actions
December 22, 2009 at 10:08 am

#214924

How to make the insert on the row occur even if the trigger fails.
Trigger works however I am using sp_send_dbmail and keep getting denied on the service account I am using. I would like the triggers to continue to insert even if the other pieces fails in the triggers (like send email, or the other db is not available, or permission issue).

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Since triggers are synchronous, not going to happen. Well, not true, what you may need to look at is using Service Broker to handle those functions that you need to happen asynchronously from the updates to your tables.

I'd start by reading about Service Broker in BOL. Haven't used it yet myself but looking into myself for several projects I am working on for work.

doran_doran SSC-Addicted Points: 411 More actions · Answer 2

doran_doran

SSC-Addicted

Points: 411

December 22, 2009 at 10:52 am

#1095543

Thanks Lynn...

GSquared SSC Guru Points: 260824 More actions · Answer 3

You could put the parts that might fail in a Try block, put error handling in the corresponding Catch block, and put the insert after that, probably in another Try block. Simple alternate, use nested Try...Catch blocks for each piece, with the outermost being around the insert.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

doran_doran SSC-Addicted Points: 411 More actions · Answer 4

so if insert to remote server fails then we can still continue. hmm. let me see.

do you have any examples?

doran_doran SSC-Addicted Points: 411 More actions · Answer 5

I nested the try catch block and still error. I think it does not matter. If there is an error then it rollbacks.

GSquared SSC Guru Points: 260824 More actions · Answer 6

If you're inserting into a remote server, the DTC will kill the batch if it can't commit the transaction. That overrides Try...Catch functionality, per Books Online. I didn't realize you were dealing with distributed transactions.

For those, you'll need an asynchronous process of some sort.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Full circle. You may want to look at Service Broker. Start by reading about it in BOL.

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 8

Service broker looks pretty powerful but the learning curve also looks steep. You may want to consider a simpler setup. Have your trigger do something that is highly reliable, like inserting a record into an 'actions' table. Then poll you 'actions' table at a frequency that meets your needs. If you find an unprocessed 'action' then do whatever the action is, like send an email. There you have your asynchronous solution like service broker, although certainly not as robust as service broker.