SQL Mail each time a new record is created

Question

Post reply

SQL Mail each time a new record is created

brian.cook

Default port

Points: 1484
More actions
February 9, 2011 at 7:25 am

#233374

Can SQL Mail be used to send an email each time a user creates a new record, and then sends that record data to a specific user/group?
What would you recommend?
Is this possible to begin with?
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

Viewing 6 posts - 1 through 5 (of 5 total)

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

franck.maton Ten Centuries Points: 1145 More actions · Answer 1

Hi,

You could use a trigger for such a job:

CREATE TRIGGER CustomerUpdateMail

ON YourTable

FOR UPDATE

AS

declare @recordContent varchar(200)

declare @body varchar(2000)

SELECT @recordContent = recordContent

FROM inserted

SET @body = 'Record=' @recordContent ' has been inserted in table YourTable'

EXEC master..xp_sendmail

@recipients = 'your@email.com',

@subject = 'New row inserted',

@message = @body

GO

Something like that should work.

Have fun.

Franck.

brian.cook Default port Points: 1484 More actions · Answer 2

Thanks, Never used a trigger before.

Didn't think of that.

Learn something new daily!

Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner

sturner SSC-Insane Points: 22411 More actions · Answer 3

Yes, of course it is possible. Is it practical? ... that depends upon the volume of the new rows and the design and implementation of the mechanism.

In the simplest sense you could add an insert trigger that would place the ID of the new record(s) (or certain details of the the records) into another work table or a message queue to be processed by a separate e-mailer process.

If you don't need the emails to be sent immediately (or soon) after a row is added you could implement it as a batch process that checks for new records at certain intervals. It would send emails out for each new record that was added since the last time it checked. How often it checked would depend upon the requirements of the application.

These are just some ideas but without more detailed requirements that is all I can tell you.

The probability of survival is inversely proportional to the angle of arrival.

sturner SSC-Insane Points: 22411 More actions · Answer 4

franck.maton (2/9/2011)
Hi,
You could use a trigger for such a job:
CREATE TRIGGER CustomerUpdateMail
ON YourTable
FOR UPDATE
AS
declare @recordContent varchar(200)
declare @body varchar(2000)
SELECT @recordContent = recordContent
FROM inserted
SET @body = 'Record=' @recordContent ' has been inserted in table YourTable'
EXEC master..xp_sendmail
@recipients = 'your@email.com',
@subject = 'New row inserted',
@message = @body
GO
Something like that should work.
Have fun.
Franck.

Not a good implementation for performance reasons, but mainly because this trigger will fail if more than one row is inserted.

The probability of survival is inversely proportional to the angle of arrival.

franck.maton Ten Centuries Points: 1145 More actions · Answer 5

I supposed the table isn't "updated" every milliseconds. Because asking to receive a mail on every insert on a huge table doesn't make sense, to me...