Emailing a newly inserted record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Emailing a newly inserted record

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:39:06

Toby writes "OS: Windows Server 2003 Standard
SQL Server 2005 Ent.

I have read over the email stuff, stored procedures, and triggers, but am still having a difficult time piecing it together with what I want to accomplish.
When a new record is inserted into a table I want to send an email to an address that contains in the body the content of that newly inserted record. Not sure if a trigger would work, or if a stored procedure can accomplish this or the combination of the two. Currently I have created an insert trigger that will send an email message, but it sends the entire table and not just the new record. Maybe I just need to tweak this. Anyway here is my current trigger.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [reminder]
ON [dbo].[ModuleI]
FOR INSERT
AS

DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'~H1~Confident Informal EAP Referral~H1' +
N'table border="1"' +
N'th~Logon ID~th~thInformal EAP Referral~th' +
CAST ( ( SELECT td = LogonID, '',
td = ModuleI
FROM ConnectionsEAP.dbo.ModuleI

FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'~TABLE~' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='tboogerd@bccmobile.com',
@copy_recipients = 'tboogerd@bccmobile.com',
@subject = 'Confident Informal EAP Referral',
@body = @tableHTML,
@body_format = 'HTML' ;"

edit by Rob Volk: HTML formatting removed because it messes up SQL Team's layout

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-18 : 09:56:02

I would NEVER recommend doing this in a trigger.

A more robust method, IMHO, is to use the trigger to copy data to a staging queue and then have a scheduled job (as frequent as once per minute) send the messages waiting in the queue.

Regardless, read up on triggers in Books Online and you will see how to use the virtual "inserted" and "deleted" tables to reference records affected by the transaction.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources