检查 SQL Server 表的更改？

Check for changes to an SQL Server table?

提问by TimM

How can I monitor an SQL Server database for changes to a table without using triggers or modifying the structure of the database in any way? My preferred programming environment is .NETand C#.

如何在不使用触发器或以任何方式修改数据库结构的情况下监视 SQL Server 数据库的表更改？我首选的编程环境是.NET和 C#。

I'd like to be able to support any SQL Server 2000SP4 or newer. My application is a bolt-on data visualization for another company's product. Our customer base is in the thousands, so I don't want to have to put in requirements that we modify the third-party vendor's table at every installation.

我希望能够支持任何SQL Server 2000SP4 或更新版本。我的应用程序是另一个公司产品的附加数据可视化。我们的客户群数以千计，所以我不想要求我们在每次安装时修改第三方供应商的表。

By "changes to a table"I mean changes to table data, not changes to table structure.

通过“改变到餐桌”我平均变化表数据，而不是更改为表结构。

Ultimately, I would like the change to trigger an event in my application, instead of having to check for changes at an interval.

最终，我希望更改在我的应用程序中触发一个事件，而不是每隔一段时间检查一次更改。

---

---

The best course of action given my requirements (no triggers or schema modification, SQL Server 2000 and 2005) seems to be to use the BINARY_CHECKSUMfunction in T-SQL. The way I plan to implement is this:

考虑到我的要求（没有触发器或架构修改，SQL Server 2000 和 2005）的最佳行动方案似乎是使用T-SQL 中的BINARY_CHECKSUM函数。我计划实施的方式是这样的：

Every X seconds run the following query:

每 X 秒运行以下查询：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM sample_table
WITH (NOLOCK);

And compare that against the stored value. If the value has changed, go through the table row by row using the query:

并将其与存储的值进行比较。如果值已更改，请使用查询逐行查看表：

SELECT row_id, BINARY_CHECKSUM(*)
FROM sample_table
WITH (NOLOCK);

And compare the returned checksums against stored values.

并将返回的校验和与存储的值进行比较。

采纳答案by Jon Galloway

Take a look at the CHECKSUM command:

看看CHECKSUM命令：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

That will return the same number each time it's run as long as the table contents haven't changed. See my post on this for more information:

只要表内容没有改变，它每次运行时都会返回相同的数字。有关更多信息，请参阅我的帖子：

CHECKSUM

校验和

Here's how I used it to rebuild cache dependencies when tables changed:
ASP.NET 1.1 database cache dependency (without triggers)

以下是我在表更改时使用它重建缓存依赖项的方法：
ASP.NET 1.1 数据库缓存依赖项（无触发器）

回答by BitLauncher

Unfortunately CHECKSUM does not always work properly to detect changes.

不幸的是，CHECKSUM 并不总是能正常工作来检测变化。

It is only a primitive checksum and no cyclic redundancy check (CRC) calculation.

它只是一个原始校验和，没有循环冗余校验 (CRC) 计算。

Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM!

因此，您不能使用它来检测所有更改，例如对称更改会导致相同的 CHECKSUM！

E. g. the solution with CHECKSUM_AGG(BINARY_CHECKSUM(*))will always deliver 0 for all 3 tables with different content:

例如 解决方案 withCHECKSUM_AGG(BINARY_CHECKSUM(*))将始终为具有不同内容的所有 3 个表提供 0：

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 1 as numB
  UNION ALL
  SELECT 1 as numA, 1 as numB
)  q
-- delivers 0!


SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 1 as numA, 2 as numB
  UNION ALL
  SELECT 1 as numA, 2 as numB
)  q
-- delivers 0!

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM 
(
  SELECT 0 as numA, 0 as numB
  UNION ALL
  SELECT 0 as numA, 0 as numB
)  q
-- delivers 0!

回答by Nick Berardi

Why don't you want to use triggers? They are a good thing if you use them correctly. If you use them as a way to enforce referential integrity that is when they go from good to bad. But if you use them for monitoring, they are not really considered taboo.

你为什么不想使用触发器？如果您正确使用它们，它们是一件好事。如果您将它们用作强制参照完整性的一种方式，那么它们就会从好变为坏。但是，如果您将它们用于监控，它们就不会真正被视为禁忌。

回答by Chris Miller

How often do you need to check for changes and how large (in terms of row size) are the tables in the database? If you use the CHECKSUM_AGG(BINARY_CHECKSUM(*))method suggested by John, it will scan every row of the specified table. The NOLOCKhint helps, but on a large database, you are still hitting every row. You will also need to store the checksum for every row so that you tell one has changed.

您需要多久检查一次更改以及数据库中的表有多大（就行大小而言）？如果使用CHECKSUM_AGG(BINARY_CHECKSUM(*))约翰建议的方法，它将扫描指定表的每一行。该NOLOCK提示帮助，但对大型数据库，你还在打每一行。您还需要存储每一行​​的校验和，以便告诉您已更改。

Have you considered going at this from a different angle? If you do not want to modify the schema to add triggers, (which makes a sense, it's not your database), have you considered working with the application vendor that does make the database?

你有没有考虑从不同的角度来做这件事？如果您不想修改架构以添加触发器（这是有道理的，它不是您的数据库），您是否考虑过与制作数据库的应用程序供应商合作？

They could implement an API that provides a mechanism for notifying accessory apps that data has changed. It could be as simple as writing to a notification table that lists what table and which row were modified. That could be implemented through triggers or application code. From your side, ti wouldn't matter, your only concern would be scanning the notification table on a periodic basis. The performance hit on the database would be far less than scanning every row for changes.

他们可以实现一个 API，该 API 提供一种机制来通知附件应用数据已更改。它可以像写入一个通知表一样简单，该表列出了哪些表和哪一行被修改。这可以通过触发器或应用程序代码来实现。从你的角度来看，ti 无关紧要，你唯一关心的是定期扫描通知表。对数据库的性能影响远小于扫描每一行的变化。

The hard part would be convincing the application vendor to implement this feature. Since this can be handles entirely through SQL via triggers, you could do the bulk of the work for them by writing and testing the triggers and then bringing the code to the application vendor. By having the vendor support the triggers, it prevent the situation where your adding a trigger inadvertently replaces a trigger supplied by the vendor.

困难的部分是说服应用程序供应商实现此功能。由于这可以通过触发器完全通过 SQL 处理，您可以通过编写和测试触发器，然后将代码提供给应用程序供应商来为他们完成大部分工作。通过让供应商支持触发器，可以防止您添加触发器无意中替换了供应商提供的触发器的情况。

回答by caryden

Unfortunately, I do not think that there is a clean way to do this in SQL2000. If you narrow your requirements to SQL Server 2005 (and later), then you are in business. You can use the SQLDependencyclass in System.Data.SqlClient. See Query Notifications in SQL Server (ADO.NET).

不幸的是，我认为在 SQL2000 中没有一种干净的方法可以做到这一点。如果您将需求缩小到 SQL Server 2005（及更高版本），那么您就可以开展业务。您可以SQLDependency在System.Data.SqlClient. 请参阅SQL Server (ADO.NET) 中的查询通知。

回答by Yaakov Ellis

Have a DTS job (or a job that is started by a windows service) that runs at a given interval. Each time it is run, it gets information about the given table by using the system INFORMATION_SCHEMAtables, and records this data in the data repository. Compare the data returned regarding the structure of the table with the data returned the previous time. If it is different, then you know that the structure has changed.

有一个以给定时间间隔运行的 DTS 作业（或由 Windows 服务启动的作业）。每次运行时，它都会使用系统INFORMATION_SCHEMA表获取有关给定表的信息，并将此数据记录在数据存储库中。将返回的有关表结构的数据与上次返回的数据进行比较。如果不同，那么你就知道结构已经改变了。

Example query to return information regarding all of the columns in table ABC (ideally listing out just the columns from the INFORMATION_SCHEMA table that you want, instead of using *select ** like I do here):

返回有关表 ABC 中所有列的信息的示例查询（理想情况下只列出您想要的 INFORMATION_SCHEMA 表中的列，而不是像我在这里那样使用 *select **）：

select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'ABC'

You would monitor different columns and INFORMATION_SCHEMA views depending on how exactly you define "changes to a table".

您将监视不同的列和 INFORMATION_SCHEMA 视图，具体取决于您如何准确定义“对表的更改”。

回答by Orion Edwards

Wild guess here: If you don't want to modify the third party's tables, Can you create a view and then put a trigger on that view?

这里的疯狂猜测：如果您不想修改第三方的表，您可以创建一个视图，然后在该视图上放置触发器吗？

回答by ECE

Check the last commit date. Every database has a history of when each commit is made. I believe its a standard of ACID compliance.

检查上次提交日期。每个数据库都有每次提交时间的历史记录。我相信它是符合 ACID 的标准。