Nov
4
Written by:
Steve Gray
11/4/2010 4:21 PM
SQL 2008 offers two forms of built in auditing: Change Tracking and Change Data Capture. CDC captures the data before and after the change, so it’s a complete record. Change Tracking is the ‘lite’ version. It captures the fact that data has been changed, useful in ‘occasionally connected’ replicating solutions
Here’s a quick code sample to demonstrate
ALTER DATABASE TEST SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 365 DAYS, AUTO_CLEANUP = ON);
GO
ALTER TABLE item ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
go
DECLARE @sync_last_received_anchor BIGINT, @sync_new_received_anchor BIGINT;
SELECT @sync_last_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();
INSERT INTO item (item, color) VALUES ('Test8', 'red')
update item set color = 'black' where rowid = 1
SELECT @sync_new_received_anchor = CHANGE_TRACKING_CURRENT_VERSION();
--return inserts
SELECT *
FROM item AS item
INNER JOIN CHANGETABLE(CHANGES item, @sync_last_received_anchor) CT
ON CT.rowid = item.rowid
WHERE (CT.SYS_CHANGE_OPERATION = 'I'
AND CT.SYS_CHANGE_CREATION_VERSION <= @sync_new_received_anchor)
-- peek at the change table
select * from CHANGETABLE(CHANGES item, 0) t order by 1
The end result looks something like this:
As always, I welcome your comments!