View SQL modification traces (SQL change Tracking on Table)

This blog goes to http://www.xuebuyuan.com/108399.html

Before reading this blog, I would like to tell you that I do not often write a blog, this is just a try, if the writing is not good excuse me, I just want to tell you some of the SQL intrinsic mechanism.

With experience, most of the time we are personnel for trigger is not the best way to find a table to modify traces, trigger and insert modify delete and other actions tightly linked again, may be in our

A lot of problems arise in future revisions. I'm going to tell you now. A way to view the modification table records in a timely manner.

1. Modify the database configuration to allow the database to be modified for tracking.

Alter Database RDCC
Set change_tracking = On
(change_retention = 2 days, auto_cleanup = ON);

Database modification tracking permissions are turned on, not all of the table's tracking permissions are turned on, and each table must also be opened separately.

2. First we build a simple table and insert a few records.

(
    EmployeeID nvarchar (Ten) 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 ')

After the database is allowed to be traced, we must also set the table to be traceable.

ALTER TABLE Employee ENABLE change_tracking with (track_columns_updated = on);

So now we're going to start tracking the table changes.

Update employee  Set Phone1 = ' 12121212 ' where EmployeeID = ' E001 '
Update employee  Set Phone1 = ' 21212121 ' where EmployeeID = ' E002 '

Then we look at the change history:

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 '

What we see is the row value of the EmployeeID that the employee table has modified.

Let's look at the entire modification record:

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 '

列SYS_CHANGE_OPERATION显示的是操作类型，那么我们再做一条插入操作并查看结果：

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 '

So we seeSYS_CHANGE_OPERATION多了一条插入记录类型为I.那么数据库表的任意改动我们都可以通过代码跟踪到。而且一个修改记录的即（sys_change_version）从创建开始，会在系统表里保存两天。

View SQL modification traces (SQL change Tracking on Table)