July 19, 2007 at 3:58 pm
Trying to create my first trigger on 2000 here, could someone give me a couple of pointers?
There is a table I'd like to watch, when a specific field changes from 0 to 1 an email should be sent with some of the data from that specific row.
ie Active changes from 0 to 1, send an email with that row's id, description etc.
I know how to send off emails, but didn't know how to integrate a trigger into this - it seemed like it would be an Update trigger, but I wasn't sure how to get the data that is being changed / that specific row. The one thought I had was to create another table that would mirror the existing one, then when the trigger fires check between the two and determine what row has changed. After emailing the people, update the mirror.
Is there a way to do this without having to create a second table?
Thanks for any tips!
July 19, 2007 at 4:33 pm
You can, but I'd avoid sending an email from the trigger. Someone changes it, then realizes it's a mistake, changes it back, etc. you get lots of emails. Plus it's harder to include changes to multiple rows.
I'd actually create a second table, put in the PK, the change, and the date, so something like
create table AuditRowChanges(rowid int, rowchange datetime, value int)
then create a trigger on the first table that's
create trigger MyTriger on MyTable for update
as
if updated(status)
insert AuditRowChanges
select i.rowid
getdate()
i.status
from inserted i
inner join deleted d
on i.rowid = d.rowid
where i.status d.status
This will capture multiple changes and log them. Now set a job in SQL that reads the audit table and sends an email to someone with the row IDs that have changed in the last xx minutes (depending on your schedule of the job).
July 20, 2007 at 9:11 am
Further, make sure that if the 'self correcting' scenario occurs, that your email will capture both the initial change and the correction. That will make the process a 'bit' more complicated, I'm sure.
How sensitive is the timing on this task? That will also be a big design consideration.
July 20, 2007 at 9:21 am
Another reason to use Steve Jones suggestion is that the external process (sending the email) is not affecting your database update. If the email server is down or slow, your trigger may be waiting for response, and holding up the data update. That will cause at least slow response times, probable locking problems, and potentially deadlocks.
For this reason, I am always queasy about having trigger code dependent on external (to SQL server) processes. I have had to troubleshoot clients where they were using triggers to synchronize databases on different platforms, and having performance and locking issues. Having these external processes run asynchronously or independently from a trigger is a better solution.
Hope this helps
Mark
July 20, 2007 at 10:00 am
Thanks for the replys guys. And I had a typo in the first email - its actually a 2005 system if that changes anything.
This was going to be used for something that happens pretty infrequently which is why I was thinking it would be ok to send the email in the trigger. This is supposed to fire off an email to accounting when a new project gets setup to have them add a new code. But like Mark said, may as well pull it out of the trigger and do it the right way then hose some other stuff later on.
The timing isn't too critical, I suppose there could be a scheduled job that runs a couple times a day to do the monitoring / updates / email instead of a trigger. Just figured this would be a nice and easy way to learn how to use them.
Bob
July 20, 2007 at 10:13 am
You can still use the trigger to record the update, just don't send the email.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply