如何在Microsoft SQL Server中创建触发器，用单审计表跟踪全库删除数据

要实现不用为每张表单独编写触发器，就能把所有表的删除数据记录到单个审计表的需求，我们可以按以下步骤操作：

1. 首先创建审计表 #

这个表用来存储所有删除操作的审计信息，包含关键的元数据和删除数据的XML：

CREATE TABLE dbo.AuditDeletedData (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    TableName NVARCHAR(128) NOT NULL,
    DeletedDateTime DATETIME2(3) NOT NULL DEFAULT SYSDATETIME(),
    DeletedBy NVARCHAR(128) NOT NULL DEFAULT SUSER_SNAME(),
    DeletedDataXML XML NOT NULL,
    OperationType NVARCHAR(10) NOT NULL DEFAULT 'DELETE'
);

• AuditID：自增主键，唯一标识每条审计记录
• TableName：记录被删除数据所属的表名
• DeletedDateTime：删除操作发生的时间（精确到毫秒）
• DeletedBy：执行删除操作的数据库用户
• DeletedDataXML：以XML格式存储被删除的完整数据行
• OperationType：固定标记为DELETE，方便后续扩展其他操作的审计

2. 编写通用的DELETE触发器模板 #

这个触发器会捕获当前表的删除数据，转成XML后插入审计表。我们可以把它做成可复用的模板，后续批量或自动生成到目标表上：

CREATE TRIGGER trg_[TableName]_DeleteAudit
ON dbo.[TableName]
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 避免审计表自身触发循环
    IF OBJECT_NAME(@@PROCID) LIKE 'trg_AuditDeletedData_%'
        RETURN;

    INSERT INTO dbo.AuditDeletedData (TableName, DeletedDataXML)
    VALUES (
        OBJECT_NAME(parent_id), -- 获取触发器所属的表名
        (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT('DeletedRows'))
    );
END;
GO

这里的[TableName]是占位符，后续会通过动态SQL替换成实际的表名。FOR XML AUTO, ELEMENTS, ROOT('DeletedRows')会把DELETED虚拟表中的所有数据转换成结构化的XML，保留字段名和值。

3. 批量为现有用户表创建DELETE触发器 #

运行下面的动态SQL脚本，会自动遍历数据库中所有用户自定义表（排除系统表和审计表本身），为它们创建对应的删除审计触发器：

DECLARE @SQL NVARCHAR(MAX) = N'';

SELECT @SQL += N'
IF NOT EXISTS (SELECT 1 FROM sys.triggers WHERE name = ''trg_' + QUOTENAME(t.name) + '_DeleteAudit'')
BEGIN
    CREATE TRIGGER trg_' + t.name + '_DeleteAudit
    ON dbo.' + QUOTENAME(t.name) + '
    AFTER DELETE
    AS
    BEGIN
        SET NOCOUNT ON;

        IF OBJECT_NAME(@@PROCID) LIKE ''trg_AuditDeletedData_%''
            RETURN;

        INSERT INTO dbo.AuditDeletedData (TableName, DeletedDataXML)
        VALUES (
            ''' + t.name + ''',
            (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT(''DeletedRows''))
        );
    END;
END;'
FROM sys.tables t
WHERE t.type = 'U' -- 只选择用户表
  AND t.name != 'AuditDeletedData'; -- 排除审计表本身

EXEC sp_executesql @SQL;
GO

运行这个脚本后，所有现有用户表都会自动带上删除审计触发器，后续删除数据时会自动记录到AuditDeletedData表中。

4. 创建DDL触发器，自动为新表添加审计触发器 #

为了确保未来新建的表也能自动获得审计能力，我们需要创建一个DDL触发器，监控CREATE_TABLE事件，自动为新表生成删除审计触发器：

CREATE TRIGGER trg_DDL_CreateTable_AddAudit
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @TableName NVARCHAR(128);
    -- 获取刚创建的表名
    SELECT @TableName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(128)');

    -- 跳过审计表本身
    IF @TableName = 'AuditDeletedData'
        RETURN;

    -- 生成创建触发器的SQL
    DECLARE @SQL NVARCHAR(MAX) = N'
    CREATE TRIGGER trg_' + @TableName + '_DeleteAudit
    ON dbo.' + QUOTENAME(@TableName) + '
    AFTER DELETE
    AS
    BEGIN
        SET NOCOUNT ON;

        IF OBJECT_NAME(@@PROCID) LIKE ''trg_AuditDeletedData_%''
            RETURN;

        INSERT INTO dbo.AuditDeletedData (TableName, DeletedDataXML)
        VALUES (
            ''' + @TableName + ''',
            (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT(''DeletedRows''))
        );
    END;';

    EXEC sp_executesql @SQL;
END;
GO

现在，只要在这个数据库中新建用户表，DDL触发器会自动为它创建对应的删除审计触发器，无需手动操作。

注意事项 #

• 权限：执行这些脚本需要CREATE TRIGGER、ALTER ANY TRIGGER等权限，确保你有足够的权限操作。
• 性能影响：如果在大表上执行批量删除，生成XML和写入审计表会带来一定的性能开销，建议在业务低峰期执行批量操作，或者考虑对审计表进行分区优化。
• XML大小：如果单条删除的数据量极大（比如包含大文本、二进制字段），XML可能会占用较多存储空间，此时可以考虑使用FOR XML RAW简化格式，或者对XML进行压缩存储。
• 触发器管理：如果需要临时维护表（比如批量清理数据），可以临时禁用触发器，完成后再启用：DISABLE TRIGGER trg_[TableName]_DeleteAudit ON dbo.[TableName];，启用用ENABLE TRIGGER。

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