关于在 SQL Server 中实现审计表的建议？
我过去使用的一种简单方法基本上只是创建第二个表，其结构反映了我想要审核的表，然后在主表上创建一个更新/删除触发器。 在更新/删除记录之前，当前状态通过触发器保存到审计表中。
审计表中的数据虽然有效，但并不是最有用或最容易报告的。 我想知道是否有人有更好的方法来审核数据更改？
这些记录不应有太多更新，但这是高度敏感的信息，因此对客户而言，所有更改都经过审核并易于报告非常重要。
One simple method I've used in the past is basically just creating a second table whose structure mirrors the one I want to audit, and then create an update/delete trigger on the main table. Before a record is updated/deleted, the current state is saved to the audit table via the trigger.
While effective, the data in the audit table is not the most useful or simple to report off of. I'm wondering if anyone has a better method for auditing data changes?
There shouldn't be too many updates of these records, but it is highly sensitive information, so it is important to the customer that all changes are audited and easily reported on.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
有内置的审计包吗？ Oracle 有一个很好的软件包，它甚至可以将审核更改发送到任何修改 SQL 的坏人无法访问的单独服务器上。
他们的例子很棒......它展示了如何对任何修改审计表的人发出警报。
Are there any built-in audit packages? Oracle has a nice package, which will even send audit changes off to a separate server outside the access of any bad guy who is modifying the SQL.
Their example is awesome... it shows how to alert on anybody modifying the audit tables.
OmniAudit 可能是满足您需求的一个很好的解决方案。 我以前从未使用过它，因为我很高兴编写自己的审计例程，但听起来不错。
OmniAudit might be a good solution for you need. I've never used it before because I'm quite happy writing my own audit routines, but it sounds good.
我使用 Greg 在他的 answer 中描述的方法并使用从表触发器调用的存储过程填充审计表。
I use the approach described by Greg in his answer and populate the audit table with a stored procedure called from the table triggers.
我发现这两个链接很有用：
使用 CLR 和单个审核表。
创建使用 SQL 2005 CLR 的通用审计触发器
对每个正在审计的表使用触发器和单独的审计表。
如何审核对 SQL Server 数据的更改？
I have found these two links useful:
Using CLR and single audit table.
Creating a generic audit trigger with SQL 2005 CLR
Using triggers and separate audit table for each table being audited.
How do I audit changes to SQL Server data?
为此，我们使用两个表设计。
一张表保存有关事务的数据（数据库、表名、架构、列、触发事务的应用程序、启动事务的登录主机名、日期、受影响的行数等等）。
第二个表仅用于存储数据更改，以便我们可以在需要时撤消更改并报告旧/新值。
另一种选择是为此使用第三方工具，例如 ApexSQL Audit 或更改数据捕获功能SQL 服务器。
We are using two table design for this.
One table is holding data about transaction (database, table name, schema, column, application that triggered transaction, host name for login that started transaction, date, number of affected rows and couple more).
Second table is only used to store data changes so that we can undo changes if needed and report on old/new values.
Another option is to use a third party tool for this such as ApexSQL Audit or Change Data Capture feature in SQL Server.
您预计该表的写入量和阅读量是多少？
我使用了一个审计表，其中包含表、列、OldValue、NewValue、User 和 ChangeDateTime 列 - 足够通用，可以处理数据库中的任何其他更改，并且虽然大量数据写入该表，但报告这些数据足够稀疏，可以在一天中的低使用时段运行。
添加：
如果数据量与报告是一个问题，审计表可以复制到只读数据库服务器，允许您在必要时运行报告，而不会妨碍主服务器完成工作。
How much writing vs. reading of this table(s) do you expect?
I've used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime - generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were sparse enough that they could be run at low-use periods of the day.
Added:
If the amount of data vs. reporting is a concern, the audit table could be replicated to a read-only database server, allowing you to run reports whenever necessary without bogging down the master server from doing their work.