Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Duplicate mails from sp_send_dbmail within trigger Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2013 8:58 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 10:53 AM
Points: 80, Visits: 366
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 »

Add to briefcase

Permissions Expand / Collapse