Stored Procedure to send mail after new data insert, update

  • 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

  • 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';

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply