Change data Capture (CDC) feature

Http://www.cnblogs.com/downmoon/archive/2012/04/10/2439462.html

SQL Server 2008 Application Series-Directory Index

This article focuses on four methods for recording data changes in SQL Server: triggers, output clauses, change data capture (changes Capture, CDC) features, and synchronous change tracking. The latter two are new to SQL Server 2008.

First, Trigger

In earlier versions of SQL Server, if you wanted to record the insert/update/delete action of a table or view, we could use triggers (Trigger) (http://msdn.microsoft.com/zh-cn/library /ms189799.aspx), which is often an effective way in the case of a small amount of data, where the post-trigger (after Trigger) can only track any combination of the table's three operations, while the front trigger (Instead of Trigger) can handle updates to tables and views (even if the normal update view statement complains when some columns are ambiguous). Let's look at two examples:

Prepare basic data:

Use TESTDB2 Go-Create two test tables IF not object_id (' Departdemo ') are NULL DROP TABLE [Departdemo] Go IF not object_id (' Departchangel OGs ') is a NULL DROP table [departchangelogs] Go--Test table CREATE table [dbo]. [Departdemo] ([DID] [int] IDENTITY ( -,1Not NULL PRIMARY KEY, [dname] [nvarchar] ( A) NULL, [dcode] [nvarchar] ( -) NULL, [Manager] [nvarchar] (- M) null, [parentid] [int] NOT NULL DEFAULT (0), [AddUser] [nvarchar] (- M) null, [Addtime] [datetime] NULL, [moduser] [nvarchar] (- M) null, [Modtime] [datetime] NULL, [curstate] [smallint] NOT NULL DEFAULT (0), [remark] [nvarchar] ( -) null, [F1] [int] NOT NULL DEFAULT (0), [F2] [nvarchar] ( -NULL) Go--Log table CREATE table [Departchangelogs] ([Logid] [bigint] IDENTITY (1001,1Not null PRIMARY KEY, [DID] [int] is not NULL, [dname] [nvarchar] ( A) NULL, [dcode] [nvarchar] ( -) NULL, [Manager] [nvarchar] (- M) null, [parentid] [int] NOT NULL DEFAULT (0), [AddUser] [nvarchar] (- M) null, [Addtime] [datetime] NULL, [moduser] [nvarchar] (- M) null, [Modtime] [datetime] NULL, [curstate] [smallint] NOT NULL DEFAULT (0), [remark] [nvarchar] ( -) NULL, [F1] [int]