User Tips: Receiving an Email When Database Data is Changed
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
xp_sendmail
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:
CREATE TABLE tstComments (
CommentID int IDENTITY(1,1),
ActionItemID int,
CommentDate datetime,
CommentText varchar(100)
)
GO
|
Next we'll insert a row to update:
insert into tstComments(ActionItemID, CommentDate, CommentText)
values('1','5/5/06','this is the original comment')
|
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
the DROP TRIGGER keyword.
-- Drop the trigger if it already exists
IF EXISTS(
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[updComments]') AND
OBJECTPROPERTY(id, N'IsTrigger') = 1)
DROP TRIGGER [updComments]
GO
|
Once we've ensured that the trigger, if it already exists, has been deleted, we can go ahead and create the trigger.
Use the CREATE TRIGGER keyword followed by your name for the trigger.
With CREATE TRIGGER you need to specify the trigger's name along with what table it operates on and for what
database operation(s):
-- Create the trigger
CREATE TRIGGER updComments
--indicate which table the trigger is to be executed on
ON tstComments
--indicate whether it is an UPDATE, INSERT or DELETE Trigger
FOR UPDATE
AS
|
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 INSERT,
UPDATE, or DELETE statement. If we want to process all of the records that were updated in
a batch UPDATE statement, we'd need to use a CURSOR
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.
-- Make sure only one record has been updated
-- (If more than one record has been updated, do nothing...)
IF (SELECT COUNT(*) FROM inserted) = 1
BEGIN
--holds the CommentID so we know which comment was updated
declare @CommentID varchar(10)
--holds the body of the email
declare @body varchar(3000)
--holds the old comment which has been changed
declare @oldcomment varchar(1000)
--holds the new comment
declare @newcomment varchar(1000)
|
We now need to assign values to the variables from the row that was just updated. The deleted
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 @CommentID and @oldcomment
variables:
SELECT @CommentID = CommentID,
@oldcomment = d.commentText
FROM deleted d
|
Then we want to assign values to the variables from the row after it was updated. To access those valued we can use the
inserted table:
SELECT @newcomment = commentText
FROM inserted
|
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:
SET @body =
'CommentID ' + @CommentID + ' has been updated
Old Comment:' + @oldcomment + '
New Comment: ' + @newcomment
|
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 master database.
Assuming you're fulfilled all of these steps, the following statements will actually send the email to a specified account.
--xp_sendmail is the extended sproc used to send the mail
EXEC master..xp_sendmail
@recipients = 'you@example.com',
@subject = 'Comments have been updated',
@message = @body
END -- This ends the IF statement that ensures
-- only one record has been updated
GO
|
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.
UPDATE tstComments SET
commentText = 'this is the updated comment'
WHERE CommentID = 1
|
If everything is configured correctly, you should receive the following email:
CommentID 1 has been updated
Old Comment:this is the original comment
New Comment: this is the updated comment
You can explore the SQL Server Books Online to find many other useful parameters
to the
xp_sendmail procedure 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!
Happy Programming!
By Wade Wilbur
Attachments:
Download the complete SQL script
