To read the article online, visit http://www.4GuysFromRolla.com/webtech/tips/t051706-1.shtml

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

    Return to user tips...

    • Article Information
    Article Title: User Tips: Receiving an Email When Database Data is Changed
    Article Author: Wade Wilbur
    Article URL: http://www.4GuysFromRolla.com/webtech/tips/t051706-1.shtml

    Copyright 2013 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers