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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy