October 30, 2004 at 1:50 pm
I hope you guys can help. I am looking to create a trigger that watches for updates to the sch_status column and fires when the update changes the value to 4. The code that will be executed is below with addition of some code to e-mail the results. It is the actual code to create the update trigger on the
schedule table I need some help with.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
where s.sch_status = 4
order by s.sch_id
October 30, 2004 at 2:12 pm
You might want to have a look at BOL for IF UPDATE to test in your trigger if your column is updated.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 1, 2004 at 10:01 am
The following is probably what you want. I joined the inserted table and changed the where clause to use the inserted table. I'm also assuming that sch_id is a primary key.
select s.sch_id,
'Date Scheduled'=convert(varchar(17),s.sch_date,113),
s.e_subject,
'Number Processed'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id and dl.delivery_status=2),
'Total Number'=(select count(dl.sch_id) from distribution_list dl where
dl.sch_id = s.sch_id),
'Log file'= '\\Renown\eNewsletters\Logs\SE\'+
convert(varchar(12),getdate(),112) +'.log'
from schedule s
join inserted i on i.sch_id=s.sch_id
where i.sch_status = 4
order by s.sch_id
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply