By Wade Wilbur
For low-volume databases, those that are predominantly read and not written to, or database tables that are designed to not
be updated or only updated under rare circumstances, a DBA or developer may be interested
in being notified whenever the data in a particular table is modified. Or you may have certain records in a table that
are assigned to a particular user and, upon that record being updated, that user should be notified of the change.
While this sort of logic can be implemented at the code level, Microsoft SQL Server has all of the technologies needed to
achieve this aim built directly into it. Triggers
can be used to perform some action when data is inserted, updated, or deleted from a table, and Microsoft SQL Server's
extended stored procedure can be invoked to send an email to one or more recipients. Combining triggers with
xp_sendmail provides a means for
alerting specified users via email when the data in a particular table is modified.
The purpose of this article is to demonstrate how to create such a notification system. This code presented here is a simplified version from an action item application that emails the appropriate people when an item is updated. I noticed that some of my staff who were just learning SQL were intimidated by triggers and SQL Mail, so I wanted to come up with an example which was useful, yet easy to follow.
In this article we'll step through creating the table, some dummy data, and the trigger one step at a time. You can download the entire database script at the end of this article.
Creating the Table and Populating it with a Test Record
Let's start by creating a table which will hold comments that need to be updated:
Next we'll insert a row to update:
Creating the Trigger
Now we create the trigger for the
tstComments table. Before we create the trigger, however, we should
first see if the trigger already exists and, if so, delete it. This can be accomplished by checking the
sysobjects table to see if a trigger with the specified name exists. If so, we can delete it by using
DROP TRIGGER keyword.
Once we've ensured that the trigger, if it already exists, has been deleted, we can go ahead and create the trigger.
CREATE TRIGGER keyword followed by your name for the trigger.
CREATE TRIGGER you need to specify the trigger's name along with what table it operates on and for what
Now for the body of the trigger! Since we will be sending an email, we will need a variable to hold the body of the email (along with some other variables).
One thing to keep in mind is that a trigger only fires once per
DELETE statement. If we want to process all of the records that were updated in
UPDATE statement, we'd need to use a
here. For this example, let's just assume that we only want to send off an email if exactly one record is updated. Therefore,
our trigger won't send notifications if batch
UPDATE statements are issued. In the trigger, the
inserted table contains the record(s) that
were just updated. Therefore, we can check this table to see how many records were updated.
We now need to assign values to the variables from the row that was just updated. The
table is a special table that holds the contents of the row that was updated right before it was updated. So it has the
pre-updated values. We want to take these original values and store them into our
Then we want to assign values to the variables from the row after it was updated. To access those valued we can use the
Set the body of the email by concatinating the strings and the variables. Notice the placement of the single quotes and carriage returns which allows for formatting in the email body:
Finally, send the email using the
xp_sendmail extended stored procedure.
Since the procedure is in the
master database and we are in
pubs (or whatever database you may
be adding this trigger to), we need to specify which database the
xp_sendmail stored procedure is in.
Moreover, in order to use this extended stored procedure you must have SQL Mail set up. See
How to Configure SQL Mail for more information. Lastly,
you must grant
EXECUTE permissions for the
xp_sendmail procedure in the
Assuming you're fulfilled all of these steps, the following statements will actually send the email to a specified account.
Testing the Trigger
After you have created the table, inserted the row and created the trigger, run this
UPDATE statement to test
out the trigger's functionality.
If everything is configured correctly, you should receive the following email:
CommentID 1 has been updatedYou can explore the SQL Server Books Online to find many other useful parameters to the
Old Comment:this is the original comment
New Comment: this is the updated comment
xp_sendmailprocedure as well as detailed descriptions of other types of triggers. Now that you've seen how easy triggers and SQL Mail are, you will no doubt find lots of uses for them!
|Return to user tips...|