在 SQL Server 中实施审计表的建议？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3823/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Suggestions for implementing audit tables in SQL Server?
提问by Brandon Wood
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.
这些记录不应有太多更新，但它是高度敏感的信息，因此所有更改都经过审核并易于报告对客户来说非常重要。
采纳答案by Greg Hurlman
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.
我使用了一个审计表，其中包含 Table、Column、OldValue、NewValue、User 和 ChangeDateTime 的列 - 足够通用以处理数据库中的任何其他更改，并且当大量数据写入该表时，报告该数据足够稀疏，可以在一天中的低使用率期间运行。
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.
补充：如果数据量与报告是一个问题，审计表可以复制到只读数据库服务器，允许您在必要时运行报告，而不会使主服务器停止工作。
回答by John Emeres
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 Auditor Change Data Capture feature in SQL Server.
另一种选择是为此使用第三方工具，例如SQL Server 中的ApexSQL 审计或变更数据捕获功能。
回答by HadleyHope
I have found these two links useful:
我发现这两个链接很有用：
Using CLR and single audit table.
Creating a generic audit trigger with SQL 2005 CLR
使用 CLR 和单个审计表。
使用 SQL 2005 CLR 创建通用审计触发器
Using triggers and separate audit table for each table being audited.
How do I audit changes to SQL Server data?
对每个被审计的表使用触发器和单独的审计表。
如何审核对 SQL Server 数据的更改？
回答by GateKiller
回答by Chris Miller
回答by Mark Harrison
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.
是否有任何内置的审计包？Oracle 有一个很好的包，它甚至可以将审计更改发送到任何正在修改 SQL 的坏人访问之外的单独服务器。
Their example is awesome... it shows how to alert on anybody modifying the audit tables.
他们的例子很棒……它展示了如何对修改审计表的任何人发出警报。
