April 28, 2009 at 1:33 pm
I have three columns (Leadtime,SafetyStock, Min/Max) in my Part table that I want to track changes to. Any time these fields are updated I want to send an email that changes have been made to the department manager. How would I go about setting this up?
April 28, 2009 at 1:40 pm
You could use sp_send_dbmail in a trigger. If you look that one up, it's pretty easy to implement.
- 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
April 29, 2009 at 7:01 am
I know this is less of an issue with Database Mail than SQL Mail, but if your mail service fails then your trigger will fail too. I've got into the habit of getting the trigger to write the details of the change, the destination email address etc. to a 'messages' table and then having a scheduled job loop through any unsent messages, mail them out and then flag them as sent. This way any problems with mail won't affect the operation of your trigger and you will always be able to see the message contents, even if they haven't been sent.
Do feel free to point out that this is extremely outdated and I'll reconsider using this strategy.
--
Scott
April 29, 2009 at 7:37 am
Database Mail is asynchronous. E-mail system failure won't hang up the trigger.
You can also set failover e-mail servers in the options, if you have access to such, and if the first one fails, it'll try the second one, and so on till either one works or they've all failed. Even without that, network connectivity, server availability, etc., won't cause the trigger/script/proc to hang fire when it calls sp_send_dbmail.
Definitely not true with SQL 2000's implementation.
- 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
May 7, 2009 at 1:04 pm
Agree with G squared --
Part of one of my triggers failed ( the send_dbmail block) yet the code after it successfully wrote to an auditing table I use to create a record of the supposed email sent. Still the data in the auditing table is inconsistent to say the least.
----------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply