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.

Use ChangeDataCapture
Go
Select * from cdc.dbo_Currency_CT 
Go 

8. Now let’s update and delete some rows from the Currency table by executing the following TSQL Query:

Use ChangeDataCapture
Go

Update Currency
Set CurrencyName = ‘AFGHANI’
Where CurrencyKey = 1
Go

Delete From Currency
Where CurrencyKey = 3
Go

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

Conclusion
The Change Data Capture feature helps DBA’s to keep track of all the Data Manuplation Language changes on a specific user table.

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!