检查SQL Server表的更改？ [英] Check for changes to an SQL Server table?

问题描述

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

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 .NET and C#.

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

I'd like to be able to support any SQL Server 2000 SP4 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.

由更改为表我的意思是对表数据的更改，而不是表结构的更改。

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.

根据我的要求，最好的行动方案（没有触发器或模式修改，SQL Server 2000和2005）似乎是在 BINARY_CHECKSUM 函数wiki / Transact-SQLrel =noreferrer> T-SQL 。我计划实施的方式是：

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_CHECKSUM function in T-SQL. The way I plan to implement is this:

每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.

推荐答案

查看CHECKSUM命令：

Take a look at the CHECKSUM command:

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

以下是如何在更改表格时重建缓存依赖关系：

ASP.NET 1.1数据库缓存依赖关系（无触发器）

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

这篇关于检查SQL Server表的更改？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！