Mar 1, 2012

Send an email when data of any columns of table changes in sql server

We want create crate an application which automatically sends an email notification when data of particular columns of table in database is changed in sql server.

Over all idea

1. Enable the change tracking (CT) in database
2. Enable the change tracking (CT) in table
3. Enabled and configure the database mail
4. Create a stored procedure which will get data change using CT and send an email
5. Create a sql sever agent job which will execute the procedure in every 10 second.

Suppose we want to send an email alert when column Price changes of table tblPrice

Step 1:
Creating following tables in the database ExactHelp

USE ExactHelp


--To keep the price of each items
CREATE TABLE tblPrice(
  ItemId BIGINT IDENTITY PRIMARY KEY,
  Price MONEY,
)

--Insert some data into it.
INSERT tblPrice VALUES(50),(30),(40)


Note: Table must have a primary key


--To keep the history of price change.
CREATE TABLE tblPriceHistory(
  PriceHistoryID BIGINT IDENTITY PRIMARY KEY,
  Price MONEY,
  ItemId BIGINT,
  ChangedDate DATETIME DEFAULT(GETDATE()),
  ChangeVersion BIGINT
)

--To keep the email queue
CREATE TABLE tblEmailQueue(
  EmailQueueID BIGINT IDENTITY PRIMARY KEY,
  Recipients VARCHAR(MAX),
  Message_Subject VARCHAR(4000),
  Message_Body VARCHAR(MAX),
  IsTransmitted BIT DEFAULT(0)
)

Step 2: Enabling the CT (Change) tracking in the database ExactHelp

ALTER DATABASE ExactHelp
SET CHANGE_TRACKING = ON

Step 3: Enabling the CT (Change tracking) in the table tblPrice


ALTER TABLE tblPrice
ENABLE CHANGE_TRACKING
WITH(TRACK_COLUMNS_UPDATED = ON)

Step 4: Creating a stored procedure which will send an email alter when moPrice will be changed with ntPriceID



CREATE PROC uspAlertOnPriceChange
AS
BEGIN

  SET NOCOUNT ON

  DECLARE @LastSynchronizeVersion AS BIGINT
  DECLARE @ObjectID AS INT
  DECLARE @MsgBody AS VARCHAR(MAX)
  DECLARE @ChangeType AS CHAR(1)
  DECLARE @ColumnID AS INT
  DECLARE @ChangeColumnID AS VARBINARY(4100)

  SET @ObjectID = OBJECT_ID('tblPrice')
SET @LastSynchronizeVersion = CHANGE_TRACKING_MIN_VALID_VERSION(@ObjectID)
SELECT @ColumnID = COLUMNPROPERTY(@ObjectID,'Price','ColumnID')

IF OBJECT_ID('msdb..#tblPrice') IS NOT NULL
DROP TABLE #tblPrice

CREATE TABLE #tblPrice(
  ItemId BIGINT,
  Price MONEY)

INSERT INTO tblPriceHistory(
  Price,
  ItemId,
  ChangeVersion
)
OUTPUT INSERTED.ItemId,INSERTED.Price INTO #tblPrice
SELECT
  p.Price,
  CT.ItemId,
  CT.SYS_CHANGE_VERSION
FROM CHANGETABLE(CHANGES tblPrice,@LastSynchronizeVersion ) AS CT
INNER JOIN tblPrice p
ON p.ItemId = CT.ItemId
AND CHANGE_TRACKING_IS_COLUMN_IN_MASK(@ColumnID,CT.SYS_CHANGE_COLUMNS) = 1
WHERE NOT EXISTS(
SELECT 1
FROM tblPriceHistory ph
WHERE ph.ItemId = CT.ItemId
AND ph.Price = p.Price
AND ChangeVersion = (SELECT MAX(ChangeVersion)
FROM tblPriceHistory cph WHERE cph.ItemId = CT.ItemId))

INSERT INTO tblEmailQueue(
  Recipients,
  Message_Subject,
  Message_Body
)
SELECT
  'receiver@gmail.com',
  'Price Change Alert',
  'Hi Admin,' + CHAR(13) + CHAR(10) +
  CHAR(13) + CHAR(10) + CHAR(9) + 'Price of Item Id ' +   CAST(pc.ItemId AS VARCHAR) + ' has changed. New price is '
  + '$' + CAST(pc.Price AS VARCHAR) +
  CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) + 'Thanks,' +
  CHAR(13) + CHAR(10) + 'ExactHelp@blogspot.com'
FROM #tblPrice pc

IF OBJECT_ID('msdb..#tblPrice') IS NOT NULL
  DROP TABLE #tblPrice

END

Step 5: Create stored procedure which pick one row form tblEmailQueue and send email alert:


CREATE PROCEDURE uspSendEmail
AS
BEGIN

SET NOCOUNT ON

DECLARE @Email_QueueID AS BIGINT
DECLARE @Recipients AS VARCHAR(MAX)
DECLARE @From_Address AS VARCHAR(100)
DECLARE @Message_Subject AS VARCHAR(255)
DECLARE @Message_Body AS VARCHAR(MAX)

SELECT TOP(1)
  @Email_QueueID = EmailQueueID
  ,@Recipients = Recipients
  ,@Message_Subject = Message_Subject
  ,@Message_Body = Message_Body
FROM tblEmailQueue
WHERE IsTransmitted = 0

IF @Email_QueueID IS NULL
  RETURN

EXECUTE msdb.dbo.sp_send_dbmail
  @profile_name = 'Exact_Help_Profile',
  @recipients = @Recipients,
  @from_address = 'exacthelp@blogspot.com',
  @subject = @Message_Subject,
  @body = @Message_Body

UPDATE tblEmailQueue 
  SET IsTransmitted = 1
WHERE @Email_QueueID = EmailQueueID

END


Step 6: Enable the database mail:
a. Right click on database mail and choose Configure Database Mail



b. We may get welcome window. Click on Next button.



c. In Select Configuration Task choose Set up Database mail



d. In new profile window, write profile name and click on Add button.



e. In Add account to profile click on New Account button



f. In New database mail account window fill the account information:
Account name: Write any name
E-mail address: Write any email address
Sever name: It is most important. Write valid name or IP address of your mail sever.



Note: If your mail server needs authentication then fill authentication information. 

g. In Manage Profile security select your profile and click on Next button.



h. In configure system parameters window set different parameters and click on Next button.



i. Now in Complete the wizard window click on finish button 

Step 7: Create agent job which will execute the stored procedures uspAlertOnPriceChange and uspSendEmail after every 10 second.

a. Right click on sql sever agent and choose job



b. In General Tab Write down any job name:



c. Click on steps tab and click on New button



d. Write down any step name. Select your database name and write down the execute procedures statement and press OK button.



e. Select schedule tab and click on New button:



f. In New Job schedule window fill the scheduling information.



h. Click on OK buttons.

To test it:


UPDATE TOP(1) tblPrice SET
   Price = 105


If every thing is fine then you will get an email alert at receiver@gmail.com

Indexes best practices in sql server: Where to start creating indexes?
Sql server query optimization tips : Tuning best practices with examples
Fastest or most efficient way to insert data in sql server
Index in sql server with examples

4 comments:

  1. Very useful article- keep posting

    ReplyDelete
  2. when I am updating or deleting row , i ll not get mail but i ll schedule job then only i m able to receive mail

    ReplyDelete
    Replies
    1. Yes you will have to schedule job for every 10 seconds

      Delete
  3. The "forward to friends" option that an email marketing solution services provide will allow interested home owners to pass it on to friends struggling with the same issues when moving.
    extract emails

    ReplyDelete