Problem solve Get help with specific problems with your technologies, process and projects.

How to send e-mail notifications when new records are added to a table

Serdar Yegulalp discusses how administrators can send e-mail notifications when there are updates for a particular table.

In various scenarios, an administrator may want to have e-mail notifications sent out when there are updates for a particular table, i.e., when new records are added or when existing records are changed or deleted.

A moderately experienced database administrator may try to do this by creating a trigger that sends out e-mail, but that is not wise. Putting actions like this within a trigger will drastically slow down any action on the database, since the trigger action has to commit successfully before the action itself is complete. The sort of action needed would best take place outside of the context of the database itself, not within it.

Also, how do you handle sending e-mail notifications about new records or changed or deleted records when it's more difficult to passively poll for such changes outside the scope of a trigger? (After all, we just learned that using a trigger is not going to work.)

One approach to this problem is to create a change queue, a separate table used to log any updates made to the table in question. When an update takes place, a trigger assigned to that table copies the relevant information about the change -- a unique key ID -- into the change queue. Later, the queue can be scanned by a regularly executed batch job that keeps a tally of the most recently logged changes (perhaps as a record in a table somewhere), processes everything that has changed and sends off e-mail messages through IIS's Simple Mail Transfer Protocol (SMTP), the blat command-line e-mail tool or some other mechanism. This way, all changes -- INSERT, UPDATE, DELETE -- can be recorded to the change queue and handled by the same mechanism. and, in a way, that's loosely coupled from the changes themselves.

To keep the queue table lean, the programmer may want to read records one at a time and delete them -- only after the e-mail notifications are sent successfully. Additions to the queue are done by a different process -- the less table-level locking, the better.

Serdar Yegulalp is editor of the Windows Power Users Newsletter. Check it out for the latest advice and musings on the world of Windows network administrators -- and please share your thoughts as well!

Dig Deeper on SQL Server Database Modeling and Design