sql-server - 在 SQL Server 中创建审核触发器

Question

我需要对 SQL Server 2005 数据库中的两个表实现更改跟踪。我需要审核添加、删除、更新(详细说明更新内容)。我原本计划使用触发器来执行此操作，但似乎很容易错误地执行此操作。

任何人都可以发布一个更新触发器的示例，以优雅的方式成功地完成此任务吗？我希望最终得到一个具有以下结构的审计表:

• 身份证
• 日志日期
• 表名
• 事务类型(更新/插入/删除)
• 记录ID
• 字段名称
• 旧值
• 新值(value)

...想法？

Answer 1

我只想指出几点:

使用代码生成器您无法使用单个过程来跟踪所有表，您需要在每个跟踪的表上生成类似但不同的触发器。这种工作最适合自动代码生成。在您的位置，我将使用 XSLT 转换从 XML 生成代码，并且可以从元数据自动生成 XML。这使您可以在每次更改审计逻辑/结构或添加/更改目标表时重新生成触发器，从而轻松维护触发器。

考虑审核的容量规划。到目前为止，跟踪所有值更改的审计表将是数据库中最大的表:它将包含所有当前数据和当前数据的所有历史记录。这样的表将使数据库大小增加 2-3 个数量级(x10、x100)。而审计表很快就会成为一切的瓶颈:

• 每个 DML 操作都需要在审核表中锁定
• 由于审核，所有管理和维护操作都必须适应数据库的大小

考虑架构更改。可以删除名为“Foo”的表，然后可以创建名为“Foo”的不同表。审计跟踪必须能够区分两个不同的对象。最好使用slow changing dimension方法。

考虑有效删除审核记录的需要。当您的应用程序主题策略规定的保留期到期时，您需要能够删除到期的审核记录。现在看来这似乎没什么大不了的，但 5 年后，当第一条记录到期时，审计表已增长到 9.5TB，这可能是一个问题。

考虑是否需要查询审核。必须准备审计表结构以有效地响应审计查询。如果您的审计无法被查询，那么它就没有值(value)。查询将完全由您的需求驱动，并且只有您知道这些，但大多数审计记录都是按时间间隔(“昨天晚上 7 点到 8 点之间发生了什么变化？”)、按对象(“此记录在此记录中发生了什么变化”)进行查询。表？”)或按作者(“Bob 在数据库中做了哪些更改？”)。