January 26, 2010 at 9:41 am
I want a trigger to send an email only if the notes or status has been updated. It works correctly except when it does send an email it sends two. Why?
ALTER TRIGGER [dbo].[TR_Update]
on [dbo].[tblIncidents]
after update
as
declare @message varchar(max)
declare @description varchar(max)
declare @incidentid varchar(50)
declare @email varchar(50)
declare @subj varchar(100)
declare @notes varchar(max)
declare @oldnotes varchar(max)
declare @status varchar(50)
declare @status2 varchar(50)
declare @name varchar(100)
SELECT @incidentid = incidentid, @name = [name], @status= (case when closeddate is null then 'Open' else 'Closed' end), @description=descriptionofincident, @email=email, @notes= case when notesonstatus is null then 'None Entered' else notesonstatus end from inserted left join contacts on inserted.username = contacts.username
select @oldnotes= case when notesonstatus is null then 'None Entered' else notesonstatus end, @status2 = (case when closeddate is null then 'Open' else 'Closed' end) from deleted
if @notes = @oldnotes and @status = @status2
Begin
Return
End
else
begin
set @message = 'Dear '+@name+',
Your incident has been updated. '
SET @subj = 'Alert - Incident Has Been Updated'
EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'SQLAlerts',
@recipients = @email,
@body = @message,
@subject = @subj;
END
January 26, 2010 at 9:53 am
The only reason I can see that this might send 2 emails is if you have multiple email addresses in the contacts.email column.
There is one issue with this trigger though, and that is that it does not handle a set based update. I recommend reading this article[/url] to see why that is important.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply