Duplicate mails from sp_send_dbmail within trigger

• johnnyrmtl

  Hall of Fame

  Points: 3505

  More actions

  January 15, 2013 at 8:58 am

  #151168

  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