Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

SQL Server Change Tracking on Table (Without Triggers)

, 6 Mar 2012
Rate this:
Please Sign up or sign in to vote.
SQL-Server change tracking on a table without triggers.

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gautam Sharma
Software Developer (Senior)
India India
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.

Comments and Discussions

 
QuestionSQL Server Change Tracking PinmemberDenial Parl25-Jul-14 0:19 
QuestionTracking changes made to row PinmemberMember 435337131-Oct-12 20:40 
Question... but what about the information about who did it ? PinmemberWinblows18-Apr-12 19:41 
Hi, thank You for a very fine description of the issue.
I am looking for a way to log changes made on tables by MS Access users working on MS SQL tables. Do I have to set some triggers on inserts / updates / deletes ? or do Change Tracking register it for me too ?
 
- Regards, Winblows
QuestionNice article. need ur input Pinmembersnehal212-Mar-12 21:06 
GeneralExcelente Pinmembercraliaga9-Mar-12 11:08 
QuestionChange tracking Pinmemberchoramale_vs7-Mar-12 2:37 
QuestionThanks, very good explanation PinmemberAfshin Mehrabani6-Mar-12 21:44 
GeneralGood knowledge sharing... PinmemberM-Idrees-pk6-Mar-12 19:00 
QuestionThanks for the Great Article PinmemberMember 19107305-Mar-12 12:46 
AnswerRe: Thanks for the Great Article PinmemberGautam Sharma6-Mar-12 7:07 
QuestionFormatting PinmentorDaveAuld1-Mar-12 12:59 
AnswerRe: Formatting PinmemberGautam Sharma5-Mar-12 12:52 
QuestionGood Reference Pinmembersantosh poojari29-Feb-12 20:55 
AnswerRe: Good Reference PinmemberGautam Sharma29-Feb-12 21:03 
GeneralMy vote of 5 PinmemberPatrick Harris29-Feb-12 18:15 
QuestionThanks for the effort, but... PinmemberDewey29-Feb-12 16:04 
AnswerRe: Thanks for the effort, but... PinmemberGautam Sharma29-Feb-12 21:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 6 Mar 2012
Article Copyright 2012 by Gautam Sharma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid