SQLTeam.com | Weblogs | Forums

Email Alert Question


#1

SQL Server 2008

Just wondering (before i go to the admin people and ask to get it set up) if it is possible within SQL Server to set up an email alert whenever a record is added to a certain table?


#2

It is possible. One way would be to add an INSERT trigger to the table and use sp_send_dbmail to send the e-mail.

You will have to configure sp_send_dbmail if it is not already configured.


#3

I'm always shy about doing this when users ask for it. Sooner or later something will add 1,000's of rows to the table and the user's mailbox, or the whole of Exchange/whatever, will crash ...

I prefer to send an Email announcing the new record, and then block further emails until the user does something (such as viewing that record). We then send further emails each night with the total new records IF the user has not visited / seen the new records yet ... but even that causes the user to say "Why did I get an email every day when I was on vacation?"


#4

Thanks for reply - this may work if i can convince the admins


#5

The table is added to on a rare ad-hoc basis and is fed into another system so when it is added to it's very useful for us to know ASAP - useful info to be aware of though - thanks


#6

Yup .. we've had those "user says it could never happen" tables ... and then one day the company merged with another and someone imported thousands of rows from the new company's personnel record into that table and the mail server crashed ...

Easiest fix is not to send another email for, say, 5 minutes. That just needs storing the Date/Time of the last email sent ...


#7

Food for thought - Thanks