Change data Capture (CDC) feature

Source: Internet
Author: User
Tags dname

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]

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.