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

Stored Procedure to send mail after new data insert, update Expand / Collapse
Author
Message
Posted Thursday, March 18, 2010 1:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, June 23, 2013 11:33 PM
Points: 38, Visits: 111
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
Posted Thursday, April 01, 2010 2:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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 »

Add to briefcase

Permissions Expand / Collapse