在SQL Server CDC表中追踪操作用户的触发器问题求助

原代码的核心问题 #

1. 直接操作CDC系统表：cdc.dbo_DatabaseLog_CT是SQL Server CDC自动维护的系统表，手动插入/修改数据会破坏CDC的一致性，属于官方不支持的操作。
2. 语法错误：INSERT语句中@UserName与子查询之间缺少逗号，且逻辑错误，无法将用户与变更行正确关联。
3. 未覆盖所有操作类型：只处理了INSERT场景，遗漏了UPDATE和DELETE操作的用户记录。
4. 未处理多行操作：没有考虑单次操作涉及多行数据的情况，无法保证每一行都关联正确的操作用户。

正确解决方案 #

步骤1：给源表添加操作用户字段（若不存在） #

在源表dbo.DatabaseLog中新增存储操作用户的字段，CDC会自动将该字段捕获到变更表中：

USE [AdventureWorks2022]
GO
ALTER TABLE [dbo].[DatabaseLog] 
ADD [ChangeBy] NVARCHAR(100) NULL;

步骤2：更新CDC捕获实例（若已提前启用CDC） #

如果已经为该表启用了CDC，需要将新增的ChangeBy字段加入CDC捕获范围：

EXEC sys.sp_cdc_add_column 
    @source_schema = N'dbo',
    @source_name = N'DatabaseLog',
    @column_name = N'ChangeBy',
    @capture_instance = N'dbo_DatabaseLog'; -- 替换为实际的CDC捕获实例名称

步骤3：创建正确的触发器 #

触发器负责在源表发生变更时自动填充ChangeBy字段，同时处理DELETE操作的审计（可选）：

USE [AdventureWorks2022]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER TRIGGER [dbo].[trg_DatabaseLog_TrackUser]
ON [dbo].[DatabaseLog]
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON; -- 避免返回影响行数干扰业务逻辑

    -- 处理INSERT和UPDATE：为变更行填充操作用户
    IF EXISTS (SELECT * FROM inserted)
    BEGIN
        UPDATE t
        SET t.ChangeBy = SYSTEM_USER
        FROM [dbo].[DatabaseLog] t
        INNER JOIN inserted i ON t.DatabaseLogID = i.DatabaseLogID; -- 需替换为表的主键字段
    END

    -- 可选：单独记录DELETE操作的用户信息（因DELETE后源表无数据，CDC变更表中对应行的ChangeBy会为NULL）
    IF EXISTS (SELECT * FROM deleted)
    BEGIN
        -- 先创建删除审计表（若不存在）
        IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'DatabaseLog_DeleteAudit')
        BEGIN
            CREATE TABLE [dbo].[DatabaseLog_DeleteAudit]
            (
                AuditID INT IDENTITY(1,1) PRIMARY KEY,
                DatabaseLogID INT NOT NULL,
                ChangeBy NVARCHAR(100) NOT NULL,
                DeleteTime DATETIME NOT NULL DEFAULT GETDATE()
            )
        END

        INSERT INTO [dbo].[DatabaseLog_DeleteAudit] (DatabaseLogID, ChangeBy)
        SELECT d.DatabaseLogID, SYSTEM_USER
        FROM deleted d;
    END
END;
GO

关键说明 #

• CDC变更表的自动捕获：完成上述步骤后，CDC会自动将源表的ChangeBy字段同步到cdc.dbo_DatabaseLog_CT变更表中，你可以通过查询该表获取包含操作用户的完整变更记录。
• DELETE操作的特殊处理：由于DELETE操作会移除源表中的行，CDC变更表中对应的__$operation=3（删除前镜像）记录里的ChangeBy字段会是NULL，因此建议通过自定义审计表单独记录删除操作的用户信息。

内容的提问来源于stack exchange，提问作者eusebio72