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:
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
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
3 comments:
Very useful article- keep posting
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
Yes you will have to schedule job for every 10 seconds
Post a Comment