在 SQL Server 中实施审计表的建议? [英] Suggestions for implementing audit tables in 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.

推荐答案

您期望多少写入与读取此表?

How much writing vs. reading of this table(s) do you expect?

我使用了一个审计表，其中包含 Table、Column、OldValue、NewValue、User 和 ChangeDateTime 的列 - 足够通用以处理数据库中的任何其他更改，并且大量数据被写入该表表中，关于该数据的报告非常稀疏，可以在一天中的低使用率时段运行.

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.

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