检查 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)的最佳行动方案似乎是在 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 秒运行一次以下查询:

Every X seconds run the following query:

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屋！