/*
The purpose of this article is to demonstrate how to use a simple trigger to send an email.
This is a simplified version of code from an action item application which 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. 
*/

--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')


-- 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


--now we create the trigger
--use the CREATE and TRIGGER keywords followed by your name for the trigger
-- 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

-- Make sure <b>only one record</b> 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)


   SELECT @CommentID = CommentID,
          @oldcomment = d.commentText
   FROM deleted d

   SELECT @newcomment = commentText
   FROM inserted
   
   SET @body =
'CommentID ' + @CommentID + ' has been updated
Old Comment:' + @oldcomment + '
New Comment: ' + @newcomment


   --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



--after you have created the table, inserted the row and created the trigger, run this update statement
UPDATE tstComments SET
    commentText = 'this is the updated comment'
WHERE CommentID = 1


/*
this stored procedure will send you the following email:

	CommentID 1 has been updated
	Old Comment:this is the original comment
	New Comment: this is the updated comment

*/