Most of the time we hear a best practice word “Don’t use trigger” , off course it is
true, triggers are tightly coupled with DML statement (insert/update/delete)
and add overhead to sql server in case of DML execution. But then without using
trigger how to track the changes that has made to the data..
Yes there are ways like the OUTPUT clause, but there is one
very intresting way to track the changes made to the data i.e. through change tracking.
Change Tracking enables
an application to keep track of all changes made
by the user or the called stored procedure.
Let’s see how to enable
Change Tracking on the database.
alter database testingDB
set change_tracking = on
(change_retention = 2 days, auto_cleanup = on);
Command(s) completed successfully.
Once the change tracking is enabled, it will keep the change
with the version number for the number of days defined in change_retention parameter. Like here above we have defined change_retention = 2 this means the changes will be there for 2
days.
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 must to be
enabled on the table
Let’s see how to enable
Change Tracking on the Table.
alter table testTable
enable change_tracking with ( track_columns_updated
= on)
Command(s) completed successfully.
The above code will
enable CT to the testable table. Now onward whatever update performed on this
table, CT will track all those changes
select * from testTable
id name
----------- ------------
1 Akansha
2 Gunjal
3 Abhiroop
(3 row(s) affected)
Now we firing the update
statement where we are updating the name of id
=2 to ‘Jubi R’
update testTable
set name = 'Jubi R'
where id=2
(1 row(s) affected)
Table is updated with
new data that the ‘Jubi R’ for id=2
select * from testTable
id name
----------- ------------
1 Akansha
2 Jubi R
3 Abhiroop
(3 row(s) affected)
Now let’s find whether our change has been tracked by change
tracker, by firing the below query.
SELECT pn.id
from changetable(changes testTable, 1) ct,testTable pn
where pn.id= ct.id
and
SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation
<> 'D'
id
-----------
2
(1 row(s) affected)
As you can see in the
above, the CT is able to give you the id
of the modified records in the testTable table.
Now lets modify the
above query to include few more column
SELECT ct.sys_change_version,
ct.id,pn.name,ct.sys_change_operation
from changetable(changes testTable, 1) ct,testTable pn
where pn.id= ct.id
and
SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation
<> 'D'
sys_change_version id
name sys_change_operation
--------------------
----------- ------------ --------------------
3 2 Jubi R U
(1 row(s) affected
The
SYS_CHANGE_OPERATION column provides the information what happened to this
record “U” stats that it was modified.
Let
do an insertion to the table and see the change track resultant…
insert into testTable values ('Silpa')
select * from testTable
id name
----------- ------------
1 Akansha
2 Jubi R
3 Abhiroop
4 Silpa
(4 row(s) affected)
Now
after insertion lets fire the Change
Track query.
SELECT ct.sys_change_version,
ct.id,pn.name,ct.sys_change_operation
from changetable(changes testTable, 1) ct,testTable pn
where pn.id= ct.id
and
SYS_CHANGE_VERSION > 1 and CT.Sys_Change_Operation
<> 'D'
sys_change_version id
name sys_change_operation
--------------------
----------- ------------ --------------------
3 2 Jubi R U
4 4 Silpa I
(2 row(s) affected)
Now here from the output we can see, the
version number is 4, the latest operation performed on table ‘testTable’ and the operation is Insert
(I) ie sys_change_operation =1
and the new values entered is Silpa.
Conclusion : we can track the changes
of database using CR efficiently without using triggers.
Next Upcoming: Track the changes using
output
clause.
Let
everything go, when it comes to code
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment