SQL Server 2008 Capture DML Changes Using Change Data Capture
7. CDC captures any DML changes that happen to the Currency Table by logging these changes into the cdc.dbo_Currency_CT table.
Select * from cdc.dbo_Currency_CT
8. Now let’s update and delete some rows from the Currency table by executing the following TSQL Query:
Set CurrencyName = ‘AFGHANI’
Where CurrencyKey = 1
Delete From Currency
Where CurrencyKey = 3
9. Now if you run the select statement against the cdc.dbo_Currency_CT table you should see that all the changes to the Currency table are captured. You could also see the values between 1…4 being assigned against _$operations column. The translation for each of these values is outlined below:
_$operations = 1 Means row was Deleted
_$operations = 2 Means row was Inserted
_$operations = 3 Means value of row before update
_$operations = 4 Means value of row after update
The Change Data Capture feature helps DBA’s to keep track of all the Data Manuplation Language changes on a specific user table.