SteveStedman

You have a need to keep track of all insert, update and delete actions on a table, or multiple tables. As you consider solutions, you might think about using a trigger, however triggers have their own baggage. You consider using the OUTPUT clause to log to a changes table, but then realize that the output clause cant be enforced.

Then the SQL Server feature called Change Data Capture comes into play. CDC is a SQL Server Feature that monitors the transaction log, looking for changes to specific tables, when the changes are discovered, they are then written into a Change Table that can then be queried to find out what was changed and when it was changed.

Sample Code

Lets take a look, to start with, I create a database called [DemoCDC] to use for the demo. That database contains a single table called [History] that you may recognize from the Week 3 Database Corruption Challenge. Following that are a few insert statements to just start with some data in the table.

CREATE DATABASE [DemoCDC];
GO

USE [DemoCDC]
GO

CREATE TABLE [dbo].[People]
(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] NVARCHAR(200) NULL,
	[dateAdded] DATETIME DEFAULT GETDATE()
);

INSERT INTO [dbo].[People] ([name])
VALUES ('Simba'), ('Annette'), ('Bobby'),
       ('Fred'), ('Mary'), ('Beth');

Read More »TSQL – Change Data Capture