database mail trigger on insert

Question

database mail trigger on insert

Viewing 3 posts - 16 through 17 (of 17 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

Sossoliso (3/14/2011)
I once Supported an Finance/Accounts System which used Triggers to alert agents when a sale had been made. One day the trigger to could send out any mails... The system became unsuable because data was roilled back each time a sale was made.
if anything happened between the data changes and sending of mail.. what happens next?
Either the data change gets rolled back or process continues in which case the sending oif the mail procedure gets lost.
Sending of mail may not always be as quick as would be liked.. so trigger creates bottleneck in the process.. There may be blogs out there about this issue.. cannot find any at the moment.. speaking from experience..
happy mailing.

Yep that was exactly what happened with the old mails service in SQL 2000; it would potentially rollback the trigger.

The structure then really required you to build your own queue and not send an email inside of a trigger due to rollback issues, exactly as you describe.

the new database mail is asynchronous, meaning it only reports that the mail was queued ;yes, you do have to set it up before you can use it, but otherwise, it's pretty bulletproof.

it does not error and rollback your trigger, which makes it safe to use now. it can potentially return the id of the mail item you sent for reference with it's output parameter, but doesn't depend on the mail sending successfully or not;

outside of the trigger, you can go to the views in the msdb database and check the views like sysmail_allitems or sysmail_faileditems to see if the mail sent successfully or not, and what the specific issue was (mailserver not found, relaying prohibitied, mailbox full, etc

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

spin (3/14/2011)
great, thanks.
I see your point and def don't want to start rolling back transactions. Am i right in thinking that i don't need to worry about this in 2005 as the emailing will not rollback the insert??

Sossoliso (a.k.a. Lionel E. Nzenze) concerns are not unfounded, however the advice pertains to the various synchronous mail solutions available prior to Database Mail being introduced with SQL Server 2005. You do not need to worry about the issue as long as you're using Database Mail, which is an asynchronous mail system. When using Database Mail the trigger will not wait for the email to be sent/delivered before the transaction will complete, it will only wait for the mail to be queued to be sent. That is the fundamental difference between Database Mail and the various other synchronous mail solutions that had been available.

You can read more about the sub-system here: http://msdn.microsoft.com/en-us/library/ms175887.aspx

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

spin Hall of Fame Points: 3274 More actions · Answer 3

thanks for all your replies.

my afternoon is now going to be checking out the link posted by opc.three on database mail. 🙂

for the subject to include date i went with putting everything in a variable first.

Thanks again, spin.