在 SQL Server 中创建审计触发器 [英] Creating audit triggers in SQL Server

问题描述

我需要对我的 SQL Server 2005 数据库中的两个表实施更改跟踪.我需要审核添加、删除、更新(包含更新内容的详细信息).我打算使用 trigger 来做到这一点，但看起来很容易错误地做到这一点.

I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but it seams that this is easy to do this incorrectly.

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

Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:

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

...想法?

推荐答案

我只想说几点:

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

Use code generators You can't have a single procedure to track all tables, you will need to generate similar but distinct triggers on each tracked table. This kind of job is best suited for automated code generation. In your place I would use an XSLT transformation to generate the code from XML, and the XML can be generated automatically from metadata. This allows you to easily maintain the triggers by regenerating them each time you make a change to the audit logic/structure or a target table is added/altered.

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

Consider capacity planning for the audit. An audit table that tracks all value changes will be, by far, the biggest table in the database: it will contain all the current data and all the history of the current data. Such a table will increase the database size by 2-3 orders of magnitude (x10, x100). And the audit table will quickly become the bottleneck of everything:

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

考虑架构变化.一个名为Foo"的表可能会被删除，稍后可能会创建一个名为Foo"的不同表.审计跟踪必须能够区分两个不同的对象.最好使用缓慢变化的维度方法.

Take into account the schema changes. A table named 'Foo' may be dropped and later a different table named 'Foo' may be created. The audit trail has to be able to distinguish the two different objects. Better use a slow changing dimension approach.

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

Consider the need to efficiently delete audit records. When the retention period dictated by your application subject policies is due, you need to be able to delete the due audit records. It may not seem such a big deal now, but 5 years later when the first records are due the audit table has grown to 9.5TB it may be a problem.

考虑需要查询审核.必须准备审计表结构以有效响应审计查询.如果您的审计无法查询，则它没有价值.查询将完全由您的要求驱动，只有您知道这些，但大多数审计记录都是按时间间隔(昨天晚上 7 点到晚上 8 点之间发生了什么变化?")、按对象(此记录发生了什么变化"表?")或作者(鲍勃在数据库中做了什么更改?").

Consider the need to query the audit. The audit table structure has to be prepared to respond efficiently to the queries on audit. If your audit cannot be queried then it has no value. The queries will be entirely driven by your requirements and only you know those, but most audit records are queried for time intervals ('what changes occurred between 7pm and 8pm yesterday?'), by object ('what changes occurred to this record in this table?') or by author ('what changes did Bob in the database?').

这篇关于在 SQL Server 中创建审计触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！