December 28, 2001 at 3:51 pm
I want to send an email to a user based on criteria in a table. Such as if TimeAcknow is greater than 24 hours or if a a column has certain text in it. I also want the user to be defined out of the same table. Help!
December 28, 2001 at 4:22 pm
It willl help if you can post the table and explain exactly where each piece of data is.
You probably want a task rather than a trigger, but until you define exactly how the data is placed in the table (insert or update), where (column nasmes) and exactly what is to happen, its hard to point you in the right direction.
Steve Jones
December 28, 2001 at 8:47 pm
Thank you Steve,
The first thing that I want to happen is if the column TimeRevc is older than 24 hours and the column TimeAcknow is blank then I want a canned email sent to the person listed in the Assinee column for that entry. The Table name is Asgnmnt. There are other things that I want triggered later but this might get me going on how do set them up.
December 28, 2001 at 9:20 pm
I'd suggest that you handle it by creating a stored procedure that you'll run once a day as a job. Once a day means you don't have to keep track of which ones have been sent already (this is low budget, its better to keep track!). I'd say use a cursor thats based on a select that would identify the records that need an email sent, then for each record use xp_sendmail to send out the notification.
If you do decide to build a 'better' solution, I'd suggest having the daily proc append the records (or the pkey) to a 'queue' table for additional processing. Then you'd run a job/procedure that would generate the email for any row in the queue not marked as completed.
Andy
December 30, 2001 at 10:16 am
I agree with Andy. You don't really want an email from a trigger as if email hangs, the transaction hangs. Or if email fails, then your insert or update could fail.
A job is better, but if you really need a trigger, then use UPDATED() to check if a column has changed.
You can use a
select *
from Asgnmnt
where TimeRevc < dateadd( hour, -24, getdate())
and TimeAcknow = ''
to get the rows. Be sure you update the TimeAcknow each time. You could also do a
while exists( select top 1...)
begin
-- get email using above select
-- send email
-- update row
end
to loop through all rows meeting this criteria.
Steve Jones
December 30, 2001 at 10:28 am
I like the job idea myself. A twist to running a stored proc from a job would be to create a job to send emails using vbscript and the cdo.message object. Then you could run the job from within a trigger. The job would run asynchronously, so you wouldn't have to worry about it hanging or slowing down the db.
Inside the trigger you would just say:
sp_start_job 'jobname'
January 2, 2002 at 4:37 pm
Thank you!
I think that for this task, running a scheduled job would work best after looking at your replies. This way it is done everyday and it doesn't matter if the email gets sent once a day until the criteria are met. The problem is.. I don't know SQL that well so I fumble my way through until it works. But it is a learning experiance.
January 2, 2002 at 4:44 pm
January 3, 2002 at 3:41 pm
So I create a job with this:
select *
from Asgnmnt
where TimeRevc < dateadd( hour, -24, getdate())
and TimeAcknow = ''
How would I get it to send an email to the Assignee of that row?
January 3, 2002 at 4:54 pm
You need either a cursor or a while loop - and xp_sendmail. I'd recommend experimenting with xp_sendmail in QA until you know you have the syntax working, then work it into the rest of your solution.
Andy
January 4, 2002 at 10:16 am
You run xp_sendmail from master (as in execmaster..xp_sendmail). I'd build the email message using a while loop and then run through each row sending an email. Or you could group them by assignee and send one email (usually preferable). Of course, you'd need to include all the row numbers. My TameStrings article part 7 shows how to do this.
As Andy mentioned, work a single row in QA first, then make a job.
Steve Jones
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply