January 16, 2007 at 12:33 am
Scenerio: I want to send email to top managers, if one customer order is pending for a certain period of time, lets say 40 minutes. How can I accomplish that?
January 16, 2007 at 7:36 am
Don't think you'll be able to use a trigger for this. Trigger's only get triggered...by some event, i.e. most of the time due to an insert/update/delete, but now in SQL 2005 DDL triggers as well. Neither of these would apply to you since if you did not have any activity on the record.
Most likely you would want a job that determines which orders needs to be alerted that runs every X minutes. Now you can go a lot of ways from there depending on wheter you want emails to go out every time that job runs for every order not fullfilled, or only every hour/day/week/whatever.
Hope that helps!
January 16, 2007 at 8:39 pm
How can I get more help on "job", this is something new for me.
January 17, 2007 at 2:02 am
Adding more info to my question,
I like the email alert to be sent whenever an order crosses a threshold value. For example, the order is pending for 40 minutes, nothing will happen unless it cross that threshold i.e. 40 in this case. There are many threshold value, e.g. if it crosses 40 minutes, it should go to Front managers, if it crosses 60 minutes, alert should go to middle managers, if it crosses 80 minutes, it will reach the top management.
My confusion is that, the order is lying unattended in the table. Alert will be generated as soon as the order date is > 40 minutes since it was placed.
Is it helpful?
January 17, 2007 at 6:31 am
Anders is quite correct - a trigger is not an option.
You need to write a stored procedure that is run say, every 5 minutes, using a SQL Server job. Jobs are easy to use and set up - just follow the wizard steps in SQL Server Management Studio.
The stored procedure should query your order table and pull out all the orders that violate a particular threshold and emails the orders to a specified email address. The xp_sendmail stored procedure can send the results of a query direct to any email address. With a little bit of thought (i.e. lookup tables and a cursor) you could write a very flexible and easy to maintain alerting procedure.
Hope this helps
David.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply