有哪些方法可以监控SQL数据库记录？
我想监控 10 个表，每个表有 1000 条记录。我需要知道一条记录何时以及哪条记录发生了更改。
我已经研究过 SQL 依赖关系，但是 SQL 依赖关系似乎只能告诉我表发生了变化，而不能告诉我哪条记录发生了变化。然后我必须比较表中的所有记录才能找到修改的记录。我怀疑这对我来说是一个问题，因为记录不断变化。
我还研究了 SQL 触发器，但是我不确定触发器是否可以用于监视更改的记录。
我的另一个想法是创建一个“监视”表，每当修改记录时，都会通过应用程序代码向其中添加记录。
您还知道其他方法吗？
编辑： 我正在使用 SQL Server 2008
我研究了 SQL 2008 中提供的更改数据捕获功能，并由 Martin Smith 建议。变更数据捕获似乎是一个强大、易于实施且非常有吸引力的解决方案。我将在我的数据库上滚动 CDC。
I would like to monitor 10 tables with 1000 records per table. I need to know when a record, and which record changed.
I have looked into SQL Dependencies, however it appears that SQL Dependencies would only be able to tell me that the table changed, and not which record changed. I would then have to compare all the records in the table to find the modified record. I suspect this would be a problem for me as the records constantly change.
I have also looked into SQL Trigger's, however I am not sure if triggers would work for monitoring which record changed.
Another thought I had, is to create a "Monitoring" table which would have records added to it via the application code whenever a record is modified.
Do you know of any other methods?
EDIT:
I am using SQL Server 2008
I have looked into Change Data Capture which is available in SQL 2008 and suggested by Martin Smith. Change Data Capture appears to be a robust, easy to implement and very attractive solution. I am going to roll CDC on my database.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以添加触发器并让它们将行添加到审核表中。他们可以审核已更改行的主键，甚至有关更改的其他信息。例如，在更新的情况下，他们可以记录更改的列。
You can add triggers and have them add rows to an audit table. They can audit the primary key of the rows that changed, and even additional information about the changes. For instance, in the case of an UPDATE, they can record the columns that changed.
在编写/实现自己的代码之前，请先查看 AutoAudit ：
Before you write/implement your own take a look at AutoAudit :
我正在使用支持变更数据捕获的 SQL 2008。更改数据捕获是一种非常强大的方法，用于跟踪数据更改，正如我所希望的那样。感谢您的回答。
I am using SQL 2008 which supports Change Data Capture. Change Data Capture is a very robust method for tracking data changes as I would like to. Thanks for the answer.
这是一个想法。您可以在每个表上有一个标志，每次创建或更新记录时都用当前日期时间填充。然后，当您注意到记录已更改时，再次将其标志设置为 null。因此，未更改的记录在其标志字段中具有 null，您可以查询非空值以查看哪个记录已更改/创建以及何时更改/创建（并再次将其标志设置为 null ）。
Here's an idea.You can have a flag on each table that every time a record is created or updated is filled with current datetime. Then when you notice that a record has changed set its flag to null again.Thus unchanged records have null in their flag field and you can query not null values to see which record has changed/created and when (and set their flags to null again) .