Sending mail when a table is updated

Question

Hi, my name is Tayyeb, I have recently finished my course in SQL Server 2005. I am currently working as a Windows System Administrator.

I am a newbie to databases, my question is that we have a database and if a table gets updated then I'd like to receive an email saying what has been updated.

Can anyone help me on this solution?

Thanks in advance

Answer 1

You would want to setup insert and update triggers on the table and have them call the msdb.dbo.sp_send_dbmail stored procedure.

Answer 2

Create a table that stores the datetime for the last update in that particular table.

Set up a trigger for your table that updates the datetime on an update.

Have an external application poll the datetime at a regular interval, and if it is changed, send an e-mail.

Answer 3

Using a trigger is a given. Either solution, DBMail or a polling process, will work. If you go with a polling process, go ahead and make the polling interval something you can change while the polling process is running, if possible. The problem you are going to run into is if you want to test or debug it, you won't want to wait the full polling interval. If the interval is 5 minutes, you either have to restart the poller or have a separate polling interval just for checking if the polling interval changed (can we say recursive?). So write the poller with debugging/testing in mind.

That might be enough to convince you to use the DBMail solution. I've never used it so others will have to speak to that.