August 12, 2009 at 9:29 am
This is my first attempt at a trigger. I would like to send an email when the acttype is set to 'A'. When I created the trigger and manually update the field it works but for some reason it's preventing the software from changing the acttype field to 'A'. Can anyone help me understand why this would happen?
ALTER TRIGGER [dbo].[ExpUpdateEmail]
ON [dbo].[EXPAPRVHIST]
FOR UPDATE
AS
declare @userid char(20)
declare @userIDNew char(20)
declare @ponum char(15)
declare @body varchar(2000)
declare @acttype char(1)
select @userid = userid, @ponum=d.ponum
FROM deleted d
select @useridnew=userid, @acttype=acttype
FROM inserted
SET @body= 'Status approved for PONUM: ' + @ponum
IF @acttype='A'
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sql',
@recipients = 'email@address.com',
@body = @body,
@subject = 'test trigger'
END
August 12, 2009 at 9:42 am
You'd have to make sure the software is running the same commands, or similar ones as what you are.
The other thing is that sending emails from triggers is a bad idea. It encapsulates the sending inside the transaction, which a) slows things down, possibly blocking things and b) creates a dependency that could cause your update to fail if there is an issue.
Instead you ought to log the item in another table and have a job pick up the log, then send an email.
August 12, 2009 at 9:57 am
OK, guess I will need to change my approach. If I write to a new table it would be possible to send one email per new record with a Job?
August 12, 2009 at 10:29 am
You can do it however you want. However in your current approach, you are assuming only one row is updated at a time. That's not necessarily how SQL Server works, and it allows any number of rows to be updated at a time.
So, when you determine what's changed, you'd use SET based solutions,
select i.userid
from inserted i
inner join deleted d
on i.pk = d.pk
where i.status = 'A'
and insert that into the log table. You'd want to put some sort of timestamp field in there as well, and a "sent" column. Then you'd set up a job that would scan that table and look for unsent emails, send them, mark the rows as "Sent"
August 12, 2009 at 11:33 am
Good stuff, thanks for the help.
August 12, 2009 at 1:00 pm
My pleasure. If you get stuck, please ask more or start a new thread.
Keep in mind that many times someone says they want an email for every change, and they'll get 10 in an hour and get annoyed. Really determine if this is needed for each row (like auditing) or periodic (every 5 min, hour, day, etc.) for notification. You can easily then schedule your job and configure it as needed. As you build it, keep in mind schedules and requirements may change, so be sure you account for that.
August 12, 2009 at 1:08 pm
Actually, sending to DBMail should be OK within a trigger. Unlike SQL Mail. DBMail is mediated through Service Broker under the hood, so those Email problems like network delays, jammed mail ports, etc. should not affect the trigger.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply