Send an e-mail when a new record is added

Question

Send an e-mail when a new record is added

Kris-155042

Hall of Fame

Points: 3460
More actions
January 2, 2007 at 6:45 pm

#176231

I have an Auditing database that audits almost everything that happens from another application. The purpose is so that the auditors can see who's been doing what. However they only want to know one particular event. I have created a view when the event id gets added to the main table. For example there are event id's from 1 through to 10, but the view will only show event id 9.
How do I then have a new event added to that table, it sends it off to the selected people via e-mail automatically? I just want it to send the updated records at the end of every day not every time one is added.
Hope this makes sense.
Thanks,
Kris

Viewing 15 posts - 1 through 15 (of 15 total)

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

howyue SSC-Addicted Points: 439 More actions · Answer 1

which sql server u r using ? i'm not sure about sql2000, but for sql2005,

it is quite simple, as u can write trigger to your table and each time a new record is inserted use the dbmail to sent an email.

or if u would sent email at the end of each day, you can create a job to execute a storedprocedure(to sent email) and set its schedule to daily.

of course before u can use the dbmail function, make sure u hav set the profile correctly.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 2

I'm using 2005. The storedprocedure was the way I was going for. Then I could (as you say) create a job to run everynight. I already have several e-mails that get sent from the server, so that's not a problem.

I specifically want to know how to right the storedprocedure.

Thanks,

Kris

howyue SSC-Addicted Points: 439 More actions · Answer 3

the storedprocedure should be as simple as passing paremeter into the system storedprocedure:

EXEC msdb.dbo.sp_send_dbmail

@recipients = @emailto,

@copy_recipients = @emailcc,

@blind_copy_recipients = @emailbcc,

@subject = @emailsubject,

@body = @emailbody,

@file_attachments = @emailattach,

@body_format = 'HTML'

and execute it! hope i did not misunderstand what u said.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 4

O.K I've had a play around with this and my question is now. When I run the script, it says that the mail queued. According to BOL that means it's a success. But I have not received an e-mail. I've tested the e-mail and I get it normally. I've noticed in the activity logs there is a process running where the wait time is just growing and growing on one activity.

The other question how do I then have it only send new entries and don't send it at all if there are no changes?

This is what I have so far.

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'kris'

@recipients = 'kris@work.com',

@query = 'SELECT * FROM db1.dbo.table1 INNER JOIN

db1.dbo.table2 ON

db1.dbo.table1.col1 = db1.dbo.table2.col1

WHERE (db1.dbo.table1.col2 = 9)' ,

@subject = 'email',

@attach_query_result_as_file = 1

Thanks for all your help so far

Thanks,

Kris

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 5

Your not going to believe it. Obviously the Christmas break wasn't enough for me. I had a typo in the e-mail address

Still the question still remains is there away to send just the new entries since the last time the job was run?

Thanks for all your help

Thanks,

Kris

MohammedU SSCoach Points: 19644 More actions · Answer 6

If you want to send the email immediately Use the trigger

or

if your table datetime column then use that column check and send the new records using a procedure and schedule that procedure to run every 5/10/30 minutes or so....

MohammedU
Microsoft SQL Server MVP

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 7

I've actually refined the script a little as below:

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'krha',

@recipients = 'kris@work.com.au',

@query = 'SELECT * from db1.dbo.view1' ,

@subject = 'email',

@attach_query_result_as_file = 1

I was hoping someone could just help me with a query of something along the lines of, if date of a new is today run this script if not don't bother running.

Thanks,

Kris

howyue SSC-Addicted Points: 439 More actions · Answer 8

i agreed with addict, to sent email for only new entries, use trigger.

if u wan to check the record date before sending email, why don u just put a IF ... ELSE statement before calling the msdb.dbo.sp_send_dbmail in the storedprocedure?

So everynight the Job execute the storedprocedure will only sent email with records created on the same day?

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 9

That's what I want to do but I'm not sure of the syntax due to it being a timedate statement.

Thanks,

Kris

Anders Pedersen SSChampion Points: 11410 More actions · Answer 10

I have a timestamp on a table I do somethign similar ( a report of DDL changes in the last 24 hours). The part of the where clause looks like this:

posttime >= dateadd(hh,-24,getdate())

I actualy have -25 to have some overlap jic.

Alternatively you could have a bit field that is set whenever you send the email to indicate it has been emailed, then just select those that have not been marked as sent.

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 11

Thanks for that. That worked perfectly.

I hate to be a pain. But I now need an IF clause. If there is no new data don't send an e-mail. Can you help me out with that syntax. I know I'm pushing the friendship. But I'm pretty sure once I get that done it should be perfect.

Thanks,

Kris

MohammedU SSCoach Points: 19644 More actions · Answer 12

if exists (select 1 from from tablename where ....)

begin

sendmai...

end

MohammedU
Microsoft SQL Server MVP

Kris-155042 Hall of Fame Points: 3460 More actions · Answer 13

I've put the following

IF EXISTS (SELECT * from db1.dbo.view1 WHERE

(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))

BEGIN

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'krha',

@recipients = 'kris@work.com.au',

@query = 'SELECT * from db1.dbo.view1

(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))',

@subject = 'email',

@attach_query_result_as_file = 1

END

And when I parse it, it says it's successful but when I go to execute it I get Incorrect syntax near '>'. I get the same error when I remove the extra brackets.

What am I missing this time?

Thanks,

Kris

MohammedU SSCoach Points: 19644 More actions · Answer 14

You are mssing WHERE cluase in your send mail query..

@query = 'SELECT * from db1.dbo.view1 WHERE

(db1.dbo.view1.date >= dateadd(hh,-24,getdate()))',

MohammedU
Microsoft SQL Server MVP