Trigger Based on Query to Send Message
-
Example of what I want to accomplish:
I have a table that tracks Customer Complaints. If a user logs a complaint with code RED, I want to fire off a message to an Email address with the Customer's information.
I have setup alerts based on failed jobs, but never to include dynamic information within the message. I am also curious how to send the message based on a table condition.
Any help or direction would be appreciated!
-
I don't know about any way to do this automatically and immediately, based on insert into a table... but maybe it's just that I never needed it that quickly
. You could write a job which runs every few minutes and goes like this (sorry for the pseudo-code, but I don't know the column names and such anyway):IF EXISTS(select * from complaints where code = 'RED' and status = 0)
BEGIN
/*
............
code for sending the mail alert(s)
............
*/
UPDATE complaints
SET status = 1, time_mailed = GETDATE()
WHERE code = 'RED'
END
/*mail again if no action taken in 20 minutes*/
IF EXISTS(select * from complaints where code = 'RED' and status = 1 and DATEDIFF(mi,time_mailed,GETDATE()) > 20
BEGIN
/*.... send mail....possibly with a copy to someone else to check it
probably change status again
*/
END
etc... I don't know how urgent RED complaints are - whether you need the action in seconds, minutes, or even more than a few minutes is acceptable. Anyway, if the table with complaints is not too big and well indexed, this job should use almost no resources as long as there is nothing to be sent, so it can run pretty often.
The code for mail itself can run along these lines (because @query is a varchar parameter - creating confusion with multiple quotes in more complex queries - it is much easier to put the logic into a view and do just simple "select * from view" at this place:
EXEC master..xp_sendmail @recipients = 'someone@company.com',
@query = 'SELECT * FROM view_red_complaint_info',
@subject = 'ALERT: Complaint with code RED',
@message = 'Please respond immediately and set status of the complaint to Received',
@attach_results = 'FALSE', @width = 250,
@dbuse = 'your_DB_name'
If you want to send the mail to various persons, depending on who is complaining etc., you can create a configuration table, read the mail addresses to a parameter /e.g. @Email varchar(1024)/ and then modify the code to EXEC master..xp_sendmail @recipients = @Email. Caution - if any one of the addresses used in @recipients=... is invalid, the entire statement fails and nothing is sent at all!
Hope this helps you a bit, I tried to keep it simple. If you have any more questions, I'll be glad to explain more.
-
I'm a bit wary of embedding xp_sendmail in a trigger. If you have problems with your mail system which raise an error in the trigger, it will cause your inserts/updates to be rolled back. (Been there!) What I do is create a trigger which, for example when the inserted status is RED, inserts a record in a table used for messages (columns subject, body, recipients, copy_recipients, senttime). I have a job running every five minutes which scans this table for rows where senttime is null, sends the message and updates the senttime. That way even if mail goes down for a period of time I can ensure that I can go back to this table and resend any outstanding messages.
--
Scott -
Thank you Vladan and Scott. I really appreciate the help. This definitely gives me something to get me going in the right direction.
Have a good one!
-
I don't assume this is specific to a SQL problem, but maybe someone can help. When I execute this command exactly as it looks (except with my valid info), the message is not actually sent. I can see it inside of Outlook in the "Inbox". I have to manually open the message and click send to actually get it out.
The SQL Mail is setup correctly and I can send out to the Operator, which is me. However, using the xp_sendmail to send to my address seems to only queue it up. The SQL Mail is setup with a generic mail account that does in fact work.
We do not have an Exchange server (unfortunately) but it is POP3/SMTP.
Thanks again!
-
Maybe not having Exchange is the problem - if another app (such as SQL server) uses my Outlook via MAPI (as SQL does) to send an email message and Outlook is not running, it merely goes into the outbox and is sent when I next run Outlook.
I assume that SQL is running under your user account for its service logon settings?
-
Thanks! I was actually able to install xp_smtp_SendMail and got it working. The other way just would not work and keeping Outlook open to send an email is not something I want to rely on.
Thanks for all the help!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply