2day i'm sent an sql mail when somebody enter data to my table using trigger :)
2 sent that u need to have a database mail profile( that i already posted how to create)
To add trigger to a table
Create trigger [tg_remainder] on [dbo].[Trans_Remainder]
AFTER INSERT -------- Intialize when trigger to happen
AS
declare @id int, -------- primary key, from where i fetch the data
@remainder nvarchar(MAX),-----Table field name
@description nvarchar(MAX),------Table field name
@MailBody nvarchar(MAX),-------value from the database
@TableHeader nvarchar(MAX) ----------My html tag comes here
set @id=0
SET @remainder=''
SET @description=''
SET @MailBody = ''
SET @TableHeader = '<html><body>
<table width="750" border="0">
<tr>
<td bgcolor="#990000"> </td>
<td bgcolor="#990000"> </td>
</tr>
<tr>
<td bgcolor="#CC99FF"> </td>
<td bgcolor="#CC99FF"> </td>
</tr>
<tr>
<td bgcolor="#FFCC99"><img src="http://evia.ucsd.edu/conferences/sdd/04/images/sdd2004_01.jpg" alt=""></img></td>
<td bgcolor="#FFCC99"> </td>
</tr>'
select @id=Pk_Remainder from INSERTED---------- getting last inserted id here
declare Clu CURSOR----------Creating CURSOR
for
SELECT Str_Remainder,Str_Description from Trans_Remainder where Pk_Remainder=@id------------my query
OPEN Clu
FETCH NEXT from Clu INTO @remainder,@description
IF(@@FETCH_STATUS=0)
BEGIN
SET @MailBody = @MailBody + '<tr><td>'+getdate()+'</td></tr><tr><td>'+@description+'</td></tr>'
FETCH NEXT FROM Clu INTO @remainder, @description
END
SET @MailBody = @TableHeader + @MailBody + '</table>'
-- SENDING EMAIL
BEGIN TRY
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'arun@simplyasp.net',
@body = @MailBody,
@sensitivity ='Personal',
@importance ='High',
@body_format = 'HTML',
@subject = 'New Match Found ',
@profile_name = 'GmailRemainder'--------- profile name
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() as ErrorNumber,
ERROR_MESSAGE() as ErrorMessage;
END CATCH
close Clu
DEALLOCATE Clu
and i hope it's helped u... :)
Have a nice day... 'N happy Coding :)
No comments:
Post a Comment