none
在SQL Server 2008中,如何捕获SQL Server 表中的插入、更新和删除的数据? RRS feed

  • 问题

  • 大家好,

    在我们实际应用程序中,经常有这样的需求:如何捕获SQL Server 表中的插入、更新和删除的数据。在SQL Server 2008中,引入一个新的特性:变更数据捕获(Change Data Capture)。变更数据捕获仅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

    谢谢
    2011年2月15日 1:43
    版主

答案

  • 大家好,

    在此提供一个示例展示如何使用变更数据捕获(Change Data Capture)这一特性来捕获SQL Server 表中的插入、更新和删除的数据,下面对变更数据捕获(Change Data Capture)简称CDC。CDC 是一个表级别的特性,在使用之前我们必须先启用这一特性。我们可以运行下面的语句去核实哪些数据库已经启用这一特性。
    USE master 
    GO 
    SELECT [name], database_id, is_cdc_enabled 
    FROM sys.databases 
    GO
    

    我们可以运行下面的存储过程启用CDC。

    USE AdventureWorks 
    GO 
    EXEC sys.sp_cdc_enable_db 
    GO
    
    


    启用CDC后,在数据库AdventureWorks中,我们将会看到以CDC命名一个架构已经创建。

    同时,在数据库AdventureWorks中,以CDC 为架构的一些系统表也得以创建。

    cdc.captured_columns:用来捕获实例中跟踪的每一列返回一行。
    cdc.change_tables:为数据库中的每个更改表返回一行。
    cdc.ddl_history:针对启用了变更数据捕获的表所做的每一数据定义语言(DDL)更改返回一行。
    cdc.index_columns:为与更改表关联的每一个索引列返回一行。
    cdc.lsn_time_mapping:为每个在更改表中所存在行的事务返回一行。

    当我们要对表进行跟踪时,必须在相关的表上启用CDC。我们可以执行下面语句来核实哪些表启用CDC。

    USE AdventureWorks 
    GO 
    SELECT [name], is_tracked_by_cdc 
    FROM sys.tables 
    GO
    
    

    我们可以使用下面的存储过程在相关的表上启用CDC。在启用CDC前,我们必须确保SQL Server Agent 服务已经启用。

    USE AdventureWorks 
    GO 
    EXEC sys.sp_cdc_enable_table 
    @source_schema = N'HumanResources', 
    @source_name = N'Shift', 
    @role_name = NULL 
    GO
    
    

    在相关表上启用CDC后,一些脚本也会自行创建。



    当成功启用以后,我们会发现一个新的系统表cdc.HumanResources_Shift_CT已经创建。我们运行下面的语句来核实一下HumanResources.Shift和cdc.HumanResources_Shift_CT 的数据。

    USE AdventureWorks
    GO
    SELECT * FROM HumanResources.Shift
    GO
    SELECT *FROM cdc.HumanResources_Shift_CT
    GO
    

    我们运行下面的语句插入一笔新的数据。

    USE AdventureWorks 
    GO 
    INSERT INTO [HumanResources].[Shift] 
    ([Name],[StartTime],[EndTime],[ModifiedDate]) 
    VALUES ('Tracked Shift',GETDATE()-1, GETDATE(), GETDATE()) 
    GO 
    
    


    再次核实一下HumanResources.Shift和cdc.HumanResources_Shift_CT 的数据。

    我们运行下面的语句更新刚刚插入的一笔新数据。

    USE AdventureWorks
    GO
    UPDATE [HumanResources].[Shift]
    SET Name = 'New Name',
    ModifiedDate = GETDATE()
    WHERE ShiftID = 4
    GO
    
    

    再次核实一下HumanResources.Shift和cdc.HumanResources_Shift_CT 的数据。

    我们运行下面的语句删除一笔数据。

    USE AdventureWorks 
    GO 
    DELETE 
    FROM [HumanResources].[Shift] 
    WHERE ShiftID = 4 
    GO
    

    再次核实一下HumanResources.Shift和cdc.HumanResources_Shift_CT 的数据。

    一些关于CDC的小贴士:

    1. 我们也可以在指定的列上启用CDC。
    2. 更多信息请参阅:
        变更数据捕获
        http://msdn.microsoft.com/zh-cn/library/bb522489(v=SQL.100).aspx

    谢谢

    2011年2月15日 2:13
    版主