SQL Server 2008中新增的 1.变更数据捕获（CDC） 和 2.更改跟踪

概述

1.变更数据捕获（CDC） 

      每一次的数据操作都会记录下来

2.更改跟踪

      只会记录最新一条记录

以上两种的区别:         http://blog.csdn.net/zjcxc/article/details/3975644

同步数据的应用:        http://blog.csdn.net/zjcxc/article/details/3924959

SQL Server 2008中SQL应用系列--目录索引

本文主要介绍SQL Server中记录数据变更的四个方法：触发器、Output子句、变更数据捕获（Change Data Capture 即CDC）功能、同步更改跟踪。其中后两个为SQL Server 2008所新增。

一、触发器

在SQL Server的早期版本中，如果要记录某个表或视图的Insert/Update/Delete操作，我们可以借助触发器（Trigger）（http://msdn.microsoft.com/zh-cn/library/ms189799.aspx）, 这在数据量较小的情况下往往是有效的方式之一，其中后触发器（After Trigger）只能跟踪表的三个操作中的任意组合，而前触发器（Instead Of trigger）可以处理表和视图的更新（即使普通的Update View语句在某些列不明确的情况下报错）。我们看两个例子：

准备基础数据：

[sql] view plaincopyPRint?

1. USE testDb2 
2. GO 
3. --创建两个测试表
4. IF NOT OBJECT_ID('DepartDemo') IS NULL
5. DROP TABLE [DepartDemo] 
6. GO 
7. IF NOT OBJECT_ID('DepartChangeLogs') IS NULL
8. DROP TABLE [DepartChangeLogs] 
9. GO 
10. --测试表
11. CREATE TABLE [dbo].[DepartDemo]( 
12. [DID] [int] IDENTITY(101,1) NOT NULL PRIMARY KEY, 
13. [DName] [nvarchar](200) NULL, 
14. [DCode] [nvarchar](500) NULL, 
15. [Manager] [nvarchar](50) NULL, 
16. [ParentID] [int] NOT NULL DEFAULT ((0)), 
17. [AddUser] [nvarchar](50) NULL, 
18. [AddTime] [datetime] NULL, 
19. [ModUser] [nvarchar](50) NULL, 
20. [ModTime] [datetime] NULL, 
21. [CurState] [smallint] NOT NULL DEFAULT ((0)), 
22. [Remark] [nvarchar](500) NULL, 
23. [F1] [int] NOT NULL DEFAULT ((0)), 
24. [F2] [nvarchar](300) NULL
25. ) 
26. GO 
27. --记录日志表
28. CREATE TABLE [DepartChangeLogs] 
29. ([LogID] [bigint] IDENTITY(1001,1) NOT NULL PRIMARY KEY, 
30. [DID] [int] NOT NULL, 
31. [DName] [nvarchar](200) NULL, 
32. [DCode] [nvarchar](500) NULL, 
33. [Manager] [nvarchar](50) NULL, 
34. [ParentID] [int] NOT NULL DEFAULT ((0)), 
35. [AddUser] [nvarchar](50) NULL, 
36. [AddTime] [datetime] NULL, 
37. [ModUser] [nvarchar](50) NULL, 
38. [ModTime] [datetime] NULL, 
39. [CurState] [smallint] NOT NULL DEFAULT ((0)), 
40. [Remark] [nvarchar](500) NULL, 
41. [F1] [int] NOT NULL DEFAULT ((0)), 
42. [F2] [nvarchar](300) NULL, 
43. [LogTime] DateTime Default(Getdate()) Not Null, 
44. [InsOrUpd] char not null
45. ) 
46. GO 

创建触发器：

[sql] view plaincopyprint?

1. /******* 创建一个After DML触发器 ******/ 
2. /********* 3w@live.cn 邀月***************/ 
3. CREATE TRIGGER dbo.tri_LogDepartDemo 
4. ON [dbo].[DepartDemo] 
5. AFTER INSERT, Delete /************此处使用update与“Insert,Delete”效果是一样的，邀月注 **********/ 
6. AS
7. SET NOCOUNT ON --屏蔽触发器发送“受影响的行数”给应用程序
8. -- Inserted rows
9. INSERT [DepartChangeLogs] 
10. (DID,[DName], [DCode], [Manager], [ParentID], 
11. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2], 
12. LogTime, InsOrUPD) 
13. SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID], 
14. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2], 
15. GETDATE(), 'I'
16. FROM inserted i 
17. -- Deleted rows
18. INSERT [DepartChangeLogs] 
19. (DID,[DName], [DCode], [Manager], [ParentID], 
20. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2], 
21. LogTime, InsOrUPD) 
22. SELECT DISTINCT DID,[DName], [DCode], [Manager], [ParentID], 
23. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2], 
24. GETDATE(), 'D'
25. FROM deleted d 
26. GO 
27. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID], 
28. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2]) 
29. VALUES (N'国家统计局房产审计一科', N'0', N'胡不归', 0, N'DeomUser', 
30. CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime), 
31. 1, N'专业评估全国房价，为老百姓谋福祉', 0, N'') 
32. GO 
33. ----该Update不会被触发器记录，但Update会生效
34. UPDATE departDemo SET [Manager]='任我行' WHERE DID=101 
35. GO 
36. DELETE FROM departDemo where DID=101 
37. GO 
38. SELECT * FROM [DepartChangeLogs] 

统计效果： 如果你觉得触发器过于浪费，你可以试着根据某些字段以缩小触发器的范围

[sql] view plaincopyprint?

1. /********* 使用DML触发器记录特定列的修改 ***/ 
2. /********* 3w@live.cn 邀月***************/ 
3. CREATE TRIGGER dbo.[tri_LogDepartDemo2] 
4. ON [dbo].[DepartDemo] 
5. AFTER Update
6. AS
7. IF Update([Manager]) 
8. Begin
9. print '该部门主管实行终身任免制，不得中途更改！'
10. Rollback ----回滚Update操作
11. End
12. GO 
13. UPDATE departDemo SET [Manager]='任我行' WHERE DID=101 
14. GO 

执行结果： 但触发器的缺陷也是显而易见的，使用触发器请注意以下几点：

1、触发器通常很隐蔽，换句话说，易忘记，特别在检查性能和逻辑问题时。

2、长时间运行的触发器会严重减慢数据操作，特别是在数据频繁修改的数据库中。

3、不记录日志的更新不会引起DML触发器的触发（如WRITETEXT、Trunacte table及批量插入操作）。

4、约束通常比触发器运行更快。

5、处理某些逻辑时，存储过程通常比触发器要更易维护和管理。

6、不允许在触发器中使用Select返回结果集。

关于触发器的更多内容，请看MSDN（http://msdn.microsoft.com/zh-cn/library/ms189799.aspx）

二、使用Output子句

官方解释：OutPut子句（http://technet.microsoft.com/zh-cn/library/ms177564.aspx）返回受 INSERT、UPDATE、DELETE 或 MERGE 语句影响的各行中的信息，或返回基于受这些语句影响的各行的表达式。 这些结果可以返回到处理应用程序，以供在确认消息、存档以及其他类似的应用程序要求中使用。 也可以将这些结果插入表或表变量。 另外，您可以捕获嵌入的 INSERT、UPDATE、DELETE 或 MERGE 语句中 OUTPUT 子句的结果，然后将这些结果插入目标表或视图。

举例：

[sql] view plaincopyprint?

1. /********* 使用Output记录表记录的修改 *****/ 
2. /********* 3w@live.cn 邀月***************/ 
3. ----删除前面的触发器
4. Drop TRIGGER dbo.[tri_LogDepartDemo] 
5. DROP TRIGGER dbo.[tri_LogDepartDemo2] 
6. INSERT [dbo].[DepartDemo] ([DName], [DCode], [Manager], [ParentID], 
7. [AddUser], [AddTime], [ModUser], [ModTime], [CurState], [Remark], [F1], [F2]) 
8. OUTPUT Inserted.*,getdate(),'I' ---注意这行是新增的
9. INTO DepartChangeLogs ---注意这行是新增的
10. VALUES (N'发改委', N'0', N'向问天', 0, N'DeomUser', 
11. CAST(0x00009DF7017B6F96 AS DateTime), N'', CAST(0x0000000000000000 AS DateTime), 
12. 1, N'油价，我说了算', 0, N'') 
13. GO 
14. SELECT * FROM [DepartChangeLogs] 

注意：

1、从OUTPUT 中返回的列反映 INSERT、UPDATE 或 DELETE 语句完成之后但在触发器执行之前的数据。

2、SQL Server 并不保证由使用 OUTPUT 子句的 DML 语句处理和返回行的顺序。

3、与触发器相比，OutPut子句可以直接处理Merge语句。

以上两种方法各有千秋，在合适的情况下采取合适的方法才是明智的选择，令人惊喜的是，SQL Server 2008起，为我们提供了更为强大的内建的方法－变更数据捕获（CDC，http://msdn.microsoft.com/zh-cn/library/bb5002