User Tips: Receiving an Email When Database Data is Changed

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

Happy Programming!