Introduction
Before you start reading this article. Just wanted to tell
you guys that I don’t write articles much, this is just an attempt after years.
So please excuse the paraphrases if they are not well articulated. My objective
is to just give you a bit of idea about some of the hidden mechanism of SQL (or maybe I was not
aware of this feature before researched and implemented this.) named as Change
Tracking.
Most of the time we have experienced that triggers are not
the best solution to find out whether any records are modified in the table.
Since event of triggers are tightly bind with the insert update or delete
action. Soon after implementation you might experience issues in future releases.
There was never a straightforward solution to track the changes that are happing
on the records, and above this the mind goes on toss when you want to track it
live. And this becomes a biggest challenge when you have a limitation that and
the best practice words like “DO NOT USE TRIGGERS”.
Well here I’m going to introduce you with a workaround of trigger,
or maybe I should put it in this words. How to keep a track of the records those
are modified or created in the database.
Overview
Change Tracking enables an application to keep track of
all changes made by the user and this is an inbuilt method.
This plays a very important role when you are building a real
time replication tool that synchronies data from one database to another type
of database that type can be oracle or Mangodb.
Configuring / Enabling CT (Change Tracking)
on the database
Alter Database <DatabaseName>
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
Note: once the CT is turned on, on the database level it does not enables the CT of all the tables of the database. The CT mast to be enabled on the tables individually
Let me explain a bit here: the functionality of CT is to keep
the track of bunch of record changes with the version number. If you look
carefully the Change_retention = 2 will
actually keep the latest version number of the record that is been modified in
the system for 2 days.
Configuring Table for CT (Change Tracking)
For this article I have created the sample table named Employee
with some sample values, and would consider employee table as the basis of
current article going forward.
Create table Employee
(
EmployeeID nvarchar(10) primary key,
FirstName nvarchar(100),
LastName nvarchar(100),
Phone1 nvarchar(10)
)
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E001','Santosh','Poojari','1111111111')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E002','Karan','Shah','2222222222')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E003','Vineesh','Padmanabhan','3333333333')
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E004','Gautam','Sharma','4444444444')
Since the database is configured with the CT. we can proceed and
enable the CT on the table. Below is the
code how to enable to CT on Employee table that already have records.
ALTER TABLE Employee ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
Tracking the Changes made in the Table
Now let's change the phone numbers of two of the employees in table
and see wether CT is able to return the Employee ID from the changes are made.
update employee set Phone1 ='12121212' where EmployeeID ='E001'
update employee set Phone1 ='21212121' where EmployeeID ='E002'
Identifying the changes:
SELECT ISNUll(pn.EmployeeID,0) as EmployeeID
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'
Resultset :
EmployeeID
E001
E002
(2 row(s) affected)
As you can see in the above box that the CT is able to give you the employee ID of the modified records in the employee table.
Now let me modify the code little bit and show the entire column info that is been returned in the resultset.
SELECT *
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'
Resultset:
SYS_CHANGE_VERSION
| SYS_CHANGE_CREATION_VERSION
| SYS_CHANGE_OPERATION
| SYS_CHANGE_COLUMNS
| SYS_CHANGE_CONTEXT
| EmployeeID
| FirstName
| LastName
| Phone1
|
26
| 27
| U
| 0x0000000004000000
| NULL
| E001
| Santosh
| Poojari
| 12121212
|
27
| 27
| U
| 0x0000000004000000
| NULL
| E002
| Karan
| Shah
| 21212121
|
The SYS_CHANGE_OPERATION column provides the information what
happened to this record “U” stats that it was modified.
Let's do and insert and see what the resultset would look like:
insert into Employee (EmployeeID,FirstName,LastName,Phone1) Values ('E006','S','G','555555')
GO
SELECT *
from changetable(changes employee, 1) as ct
INNER JOIN employee pn on pn.EmployeeID = CT.EmployeeID
WHERE SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation <> 'D'
Resultset:
SYS_CHANGE_VERSION
| SYS_CHANGE_CREATION_VERSION
| SYS_CHANGE_OPERATION
| SYS_CHANGE_COLUMNS
| SYS_CHANGE_CONTEXT
| EmployeeID
| FirstName
| LastName
| Phone1
|
26
| 27
| U
| 0x0000000004000000
| NULL
| E001
| Santosh
| Poojari
| 12121212
|
27
| 27
| U
| 0x0000000004000000
| NULL
| E002
| Karan
| Shah
| 21212121
|
28
| 28
| I
| NULL
| NULL
| E006
| S
| G
| 555555
|
As you can see the column SYS_CHANGE_OPERATION with new employee
record has the new incremented version number 28.
So whenever there are any changes that are done on CT enabled
tables. A new version number is created and will be retained in the change
tracking system table for 2 days (as configured).
You can write to me if there are any further queries. You can
also refer the tutorial on MSDN by clicking here.
Im is a Senior Software Developer working with a software company in Mumbai(India). He has overall 5.5 years of experience in .net technology. He has knowledge in C# 3.0, SQL Server 2005, SQL Reporting service, Enterprise Library 3.0, WCSF & Windows Workflow Foundation.
He has a hands on cutting edge tool like MS Visio, Rational Rose, Borland together 2006 & CruiseControl.Net
Currently his area of interest is on LINQ and Sharepoint.
He is MCPD-EA Certified.