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