SQL CDC捕获修改数据

--1 - 开启cdc
USE Test;
EXECUTE sys.sp_cdc_enable_db;
GO
 
--2 - 查看是否设置成功，1代表开启cdc
SELECT is_cdc_enabled  FROM SYS.databases WHERE name='Test'
 
--3 - 对表启用捕获
CREATE  TABLE Student
(
	StudentID VARCHAR(50) PRIMARY KEY,
	StudentName NVARCHAR(200),
	StudentAddress NVARCHAR(200),
	UpdateTime DATETIME
)
--SELECT * FROM dbo.Student--需要设置主键
 
EXEC sys.sp_cdc_enable_table 
@source_schema= 'dbo',
    @source_name = 'Student',
    @role_name = N'cdc_Admin',
    @capture_instance = DEFAULT,
    @supports_net_changes = 1,
@index_name = NULL,
--@captured_column_list = NULL,--所有列
@captured_column_list = '[StudentID],[StudentName], [StudentAddress]',--设置需要捕获所有列
@filegroup_name = DEFAULT
 
--3.1 - 检查是否成功
SELECT name, is_tracked_by_cdc,
    CASE WHEN is_tracked_by_cdc = 0 THEN 'CDC功能禁用' ELSE 'CDC功能启用' END 描述,*
FROM sys.tables
WHERE OBJECT_ID= OBJECT_ID('dbo.Student')
 
--3.2 - 返回某个表的变更捕获配置信息
EXEC sys.sp_cdc_help_change_data_capture 'dbo', 'Student'
 
--3.3 - 查询捕获数据
--cdc.dbo_Student_CT 表是根据上述设置自动生成的
SELECT * FROM cdc.dbo_Student_CT
 
 
--3.4 关闭CDC
EXEC sys.sp_cdc_disable_table 
    @source_schema= 'dbo',
       @source_name = 'Student',
       @capture_instance = 'dbo_Student'
 
--4、创建同步配置表
CREATE TABLE Test..Student_CDC_Config
    (
        TableName VARCHAR(50) PRIMARY KEY,
        Last_lsn  BINARY(10)
    );
--4.1 初始化同步配置表
 
--TRUNCATE TABLE dbo.Student_CDC_Config
--SELECT * FROM Student_CDC_Config
--DELETE FROM dbo.Student_CDC_Config WHERE TableName = 'Student';
INSERT INTO dbo.Student_CDC_Config
    (
        TableName,
        Last_lsn
    )
            SELECT
                'Student' AS TableName,
                MAX(start_lsn)         AS Last_lsn
            FROM
                Test.[cdc].[lsn_time_mapping];
 
 
--5.1测试
SELECT * FROM Student
 
--获取两条插入记录
INSERT INTO dbo.Student(StudentID,StudentName,StudentAddress,UpdateTime)
VALUES('03C06ED2-B4DB-43D6-BF23-201E85D6A1D0','学生1','地址1',GETDATE());
INSERT INTO dbo.Student(StudentID,StudentName,StudentAddress,UpdateTime)
VALUES('CCB4DBA6-F73D-4D06-9CD1-D15F2420A0AE','学生2','地址2',GETDATE());

--字段“__$operation”为“1”代表删除，“2”代表插入，“3”执行更新操作前的值，“4”执行更新操作后的值。
--字段“__$start_lsn”由于更改是来源于数据库的事务日志，所以这里会保存其事务日志的开始序列号（LSN）
SELECT * FROM cdc.dbo_Student_CT
 
--获取修改记录
--获取一条原始数据，一条新数据
UPDATE dbo.Student SET StudentName='学生11' WHERE StudentID='CCB4DBA6-F73D-4D06-9CD1-D15F2420A0AE'
SELECT * FROM cdc.dbo_Student_CT
 
--无法获取修改记录
--原因是UpdateTime不是捕获列
UPDATE dbo.Student SET UpdateTime=GETDATE() WHERE StudentID='03C06ED2-B4DB-43D6-BF23-201E85D6A1D0'
SELECT * FROM cdc.dbo_Student_CT