Stored Procedure to send mail after new data insert, update

• jaype-365633

  SSC-Addicted

  Points: 499

  More actions

  March 18, 2010 at 1:33 am

  #221924

  I am have a Trigger which sends mail after the new data is inserted or updated. I need a Stored Procedure with on @body = 'HTML'. Please help.

  The Trigger i have used is ;

  CREATE TRIGGER New_Author ON dbo.EMP_DETAILS AFTER INSERT, UPDATE AS

  DECLARE @EID VARCHAR(50)

  DECLARE @CID varchar(50)

  DECLARE @DEPTID varchar(50)

  DECLARE @MID varchar(50)

  DECLARE @Did varchar(50)

  DECLARE @TIME varchar(50)

  SET @EID = (SELECT EMPID FROM inserted)

  SET @CID = (SELECT CARDID FROM inserted)

  SET @DEPTID = (SELECT DEPTID FROM inserted)

  SET @MID = (SELECT MAINID FROM inserted)

  SET @DID = (SELECT DETIALSID FROM inserted)

  SET @TIME = (SELECT TIME FROM inserted)

  IF (UPDATE (EMPID))

  BEGIN

  DECLARE @msg varchar(500)

  SET @msg = 'New EMP Updated in Database"' + @EMPid + '' + @CARDID + '" The new Author id is $' + CAST(@EMPid as varchar(10)) + '.'

  EXEC msdb.dbo.sp_send_dbmail

  @recipients=N'ABC@ABC.COM',

  @copy_recipients='ABC@ABC.COM',

  @blind_copy_recipients ='ABC@ABC.COM',

  @body= @msg, @subject = 'NEW EMP UPDATED', @profile_name = 'AK'

  END

• jonathan.monteclaro

  SSC Journeyman

  Points: 79

  More actions

  April 1, 2010 at 2:19 am

  #1143737

  You have to create the content of the body as html.

  DECLARE @tabHTML nvarchar(MAX);

  SET @tabHTML =

  '<table width="90%" border="0" cellspacing="2" cellpadding="2">' +

  '<tr>' +

  '<td> </td>' +

  '<td class="style3">Customer Feedback / Inquiry</td>' +

  '<td></td>' +

  '</tr>' +

  <tr>' +

  '<td> </td>' +

  '<td><div align="left" style="padding-left:1px"><span class="style2">Reference No.: ' + @RefNo + '</span></div></td>' +

  '<td> </td>' +

  '</tr>'

  ....... and so on...

  --Get the Recipient

  SELECT @Recipient=RouteEmail FROM NatureQuery WHERE NatureCd = @NatureCd;

  --Populate the email subject

  SET @Subject = 'Ref No. '+ @RefNo + ', ' + @Nature ;

  then call the DBMail SP.

  EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ContactUs',

  @recipients=@Recipient,

  @subject=@Subject,

  @body = @tabHTML,

  @body_format = 'HTML';