View SQL modification traces (SQL change tracking on table)

Affirm this blog go to http://www.codeproject.com/Articles/338724/SQL-Change-Tracking-on-Table

Before reading this blog, I want to tell you that I am not writing blogs frequently. This is just an attempt. If you are not writing well, please forgive me. I just want to tell you some internal SQL mechanisms.

Based on experience, we often think that a trigger is not the best way to find a trace of table modification. The trigger is closely linked with insert, modify, delete, and other actions.

There will be many problems in future revisions and releases. Now I will tell you how to view and modify table records in a timely manner.

1. Modify the database configuration to allow the database to perform modification tracking.

 
Alter database rdcc
Set change_tracking = on
(Change_retention = 2 days, auto_cleanup = on );
 

The database modification trail permission is enabled. Not all tables are enabled, and each table must be enabled separately.

2. Create a simple table and insert several records.

  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  ' )

After the database allows tracking, we must also set the table to be trackable.

Alter TableEmployee enable change_trackingWith(Track_columns_updated= On);
 

Now let's track table modifications.

UpdateEmployeeSetPhone1='12121212' WhereEmployeeid='E001'
UpdateEmployeeSetPhone1='21212121' WhereEmployeeid='E002'
 

Then we can view the modification record:

  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   ' 

 

We can see the row value corresponding to the modified employeeid in the employee table.

Next let's look at the entire modification record:

Select*
FromChangetable (changes employee,1)AsCT
Inner JoinEmployee PNOnPn. employeeid=Ct. employeeid
WhereSys_change_version> 1 AndCt. sys_change_operation<> 'D'
 

The column sys_change_operation shows the operation type, so let's perform an insert operation and view the result:

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  ' 

Then we can see thatIf sys_change_operation has an additional insert record whose type is I, any changes to the database table can be made throughCodeTracked. In addition, a modification record (sys_change_version) is saved in the system table for two days from creation.