Duplicate mails from sp_send_dbmail within trigger

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » General » Duplicate mails from sp_send_dbmail within...

Duplicate mails from sp_send_dbmail within trigger

Rate Topic

Display Mode

Topic Options

Author

Message

johnnyrmtl

Posted Tuesday, January 15, 2013 8:58 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 9:02 AM
Points: 70, Visits: 325

Hello,

I have a trigger on a table and when a record is inserted I receive the email 3 times.
I did a quick search and haven't found anything to resolve the issue. any ideas ? as this is my trigger.

USE [ABC]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[table1]
ON [dbo].[table1]
FOR INSERT, UPDATE, DELETE

AS
BEGIN
DECLARE @EmailTXT varchar(max)
IF (Select Count(*) from Inserted) > 0
BEGIN
-- inserted

Select @EmailTXT = srvname + ' ' + name + ' ' from inserted

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = 'emailhidden',
@subject = 'A record has been inserted or modified on table blabla on server. Please Verify',
@body_format = 'HTML',
@importance = 'High',
@body = @EmailTXT

END
ELSE If (Select Count(*) from Deleted) > 0
BEGIN

-- deleted

Select @EmailTXT = srvname + ' ' + name + ' ' from deleted

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert',
@recipients = email hidden,
@subject = 'A record has been deleted on table blabla on server. Please Verify',
@body_format = 'HTML',
@importance = 'High',
@body = @EmailTXT

END
END

Post #1407315

« Prev Topic | Next Topic »

Permissions