概述

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