TSQL – Change Data Capture
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');