Stored Procedure to send mail after new data insert, update

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Stored Procedure to send mail after new data...

Stored Procedure to send mail after new data insert, update

Rate Topic

Display Mode

Topic Options

Author

Message

jaype-365633

Posted Thursday, March 18, 2010 1:33 AM

Grasshopper

Group: General Forum Members
Last Login: 2 days ago @ 11:37 PM
Points: 17, Visits: 107

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

Post #885290

jonathan.monteclaro

Posted Thursday, April 01, 2010 2:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 27, 2011 1:27 AM
Points: 1, Visits: 11

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

Post #894502

« Prev Topic | Next Topic »

Permissions