检查 SQL Server 表的更改?
如何在不使用触发器或以任何方式修改数据库结构的情况下监视 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)
相关文章