怎样在SQL Server中实现基于触发器的全文日志追踪来替代CDC功能

在SQL Server中，如果因为版本限制或运维复杂度不想开启CDC，可以利用触发器自己实现一套全文日志追踪机制，把每张业务表的增删改操作记录到统一的日志表中，从而具备变更审计与数据回放能力。

一、为什么用触发器替代CDC

CDC依赖事务日志读取和代理作业，部署较重。触发器是数据库原生功能，逻辑紧跟事务提交，适合做字段级明文记录。对于不需要近实时抽取、只关心谁改了什么的场景，触发器方案更简单。

二、日志表设计

先建立一张通用日志表，保存操作类型、表名、主键、变更内容和执行上下文。

CREATE TABLE dbo.ChangeLog (
    LogID BIGINT IDENTITY PRIMARY KEY,
    TableName SYSNAME NOT NULL,
    OperationType CHAR(1) NOT NULL, -- I/U/D
    KeyValue NVARCHAR(256),
    ChangeText NVARCHAR(MAX),
    UserName NVARCHAR(128) DEFAULT SYSTEM_USER,
    CreatedAt DATETIME DEFAULT GETDATE()
);

三、编写通用触发器模板

以用户表为例，在UPDATE时拼接旧值与新值。注意对<input>等特殊字符无需处理，这里是TSQL字符串。

CREATE TRIGGER dbo.trg_User_FullLog
ON dbo.[User]
AFTER UPDATE, INSERT, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @op CHAR(1);
    IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
        SET @op = 'U';
    ELSE IF EXISTS(SELECT 1 FROM inserted)
        SET @op = 'I';
    ELSE
        SET @op = 'D';

    -- 更新操作记录前后变化
    IF @op = 'U'
    BEGIN
        INSERT INTO dbo.ChangeLog(TableName, OperationType, KeyValue, ChangeText)
        SELECT 'User', 'U', i.ID,
               '旧名:' + d.Name + '->新名:' + i.Name + ';旧年龄:' + CAST(d.Age AS NVARCHAR) + '->新年龄:' + CAST(i.Age AS NVARCHAR)
        FROM inserted i
        JOIN deleted d ON i.ID = d.ID;
    END

    -- 插入操作
    IF @op = 'I'
    BEGIN
        INSERT INTO dbo.ChangeLog(TableName, OperationType, KeyValue, ChangeText)
        SELECT 'User', 'I', i.ID, '新增名:' + i.Name + ';年龄:' + CAST(i.Age AS NVARCHAR)
        FROM inserted i;
    END

    -- 删除操作
    IF @op = 'D'
    BEGIN
        INSERT INTO dbo.ChangeLog(TableName, OperationType, KeyValue, ChangeText)
        SELECT 'User', 'D', d.ID, '删除名:' + d.Name + ';年龄:' + CAST(d.Age AS NVARCHAR)
        FROM deleted d;
    END
END;

四、查询与维护建议

日志表会持续增长，建议按月份归档，并只对近三个月建索引。

• 对ChangeLog按CreatedAt做分区或定期转存
• 禁止业务直接写日志表，仅授权触发器所属架构
• 若表字段极多，可用FOR XML PATH序列化为JSON风格文本

五、注意事项

触发器内逻辑应尽量轻量，避免长事务；若批量更新百万级数据，需评估日志表写入压力。

通过上述方式，即可在SQL Server中用触发器实现清晰可读的全文日志追踪，平稳替代CDC的核心审计能力。

SQL_Server触发器全文日志追踪修改时间：2026-07-30 17:15:21