| Author |
Topic |
|
Vack
Aged Yak Warrior
530 Posts |
 Posted - 2009-05-19 : 10:26:53
|
| Is it possible to have a trigger email a person when an insert to a table has been done? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 10:32:11
|
| yup. its possible. but why do you need to do this from trigger? can you explain the purpose? its not a good idea to use trigger for this. |
 |
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 10:38:08
|
| That was just my first thought. A user wants to be notified when a posting procedure has finished. When posting is done a user runs another program immediatly after posting which inserts records into another table. So my thought was, when records are inserted into that table I will send an email. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 11:03:03
|
| nope. just schedule a job to execute the procedure and as next step check status of procedure execution and if success, send success mail to user using sp_send_dbmail procedure. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-05-19 : 11:03:54
|
| Definitely an inappropriate use of triggers.Your posting process should handle the notification. Do not tie this to data modification events.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 11:09:38
|
| There is no set time on when they will run the procedure. The posting is done by another software that uses SQL. I do not have access to their posting procedure to handle the notification. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 11:15:48
|
| do you have access to procedure code? |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 11:20:25
|
| no |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 11:53:21
|
| Just had a thought. There is a post date in the table. They post once a day. I can create a job to look for a post date of today, if it finds one send a notification. So my question is how would the T-SQL look. Table oehdrhst_sql field is posted_dt. If it finds one records with a posted date of today send a notification |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-05-19 : 12:41:20
|
| That is a much better strategy. You could even create a schedule job that checks for new data every five minutes or so.Use the datedif() function combined with getdate() to determine how long ago a timestamp value was set.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-05-19 : 12:43:13
|
| ...though my personal preference in these scenarios is to use a trigger on your table to populate a message queuing table. Your scheduled job then checks the message table however often you'd like, and processes whatever it finds in there. This nicely and reliably decouples the OLTP functionality from the notification requirements.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 13:42:55
|
| I'm not understanding why I would need datediff()??if the post_date=getdate() then I want to notify. How does the job know if it failed or succeeded? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 13:53:52
|
| does the procedure return any status now?datediff was to identify unprocessed data each time when you're scheduling job once in every 5 mins or so. |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 13:56:48
|
| When they post records will get inserted into the oehdrhst table and one of the fields that get populated during the insert is the post_date which will be the date they posted. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 14:03:50
|
| then just check for presence of records in oehdrhst table for current day and then send email for notification. |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 14:08:07
|
| That I don't have a problem with. Will the job be considered failed if there are no records?Is this enought to make the job work?select * from oehdrhst_sqlwhere post_date = getdate() |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-05-19 : 14:15:08
|
the job wont be considered failed if there are no records. the job will be a success but it wont do notification part.re. i think it should beselect * from oehdrhst_sqlwhere post_date > dateadd(mi,-interval,getdate()) where interval is interval you choose for job to recur. |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 15:12:04
|
| So what do I put if its every 5 minutes?where post_date > dateadd(mi,-5,getdate()) |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 15:27:53
|
| Just came up with one more issue. How do I only send one email? I have the email working but it is sending me an email every 5 minutes now. |
|
|
|
Vack
Aged Yak Warrior
530 Posts |
Posted - 2009-05-19 : 15:32:25
|
| One other thing I just tested, its reporting Success if today's date is not in the posted_dt field as well. So it is sending emails no matter what. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2009-05-19 : 16:13:55
|
| I think you should migrate to the queuing concept, which will give you greater control over how often e-mails are sent out, and preventing duplicate notifications.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Next Page
|
Email notification