有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出
有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出上次导出之后变动的数据。 作为DBA，我们可采传统的触发器操作，来构建一个元数据表或一个时间戳列来监控数据的变化。 代码如下：Code Listing 1 该代码在 SQL 2005(SP3), SQL 2008 R2 (RTM with cu5)测试通过 复制代码 代码如下: ------------------- --Method 1: TRIGGER ------------------- --Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Create Audit Summary Table to hold Meta-Data IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, TableName sysname NOT NULL, LastUpdate DATETIME NOT NULL, LastExport DATETIME NOT NULL ) GO INSERT dbo.TableAuditSummary (TableName, LastUpdate, LastExport) VALUES ('dbo.CheckSumTest', GETDATE(), GETDATE()) GO --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport --Create Trigger on all Base Tables --This fires on any insert/update/delete and writes new LastUpdate column for the table set to Current Date and Time IF OBJECT_ID('dbo.trg_CheckSumTest_MaintainAuditSummary', 'TR') IS NOT NULL DROP TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary GO CREATE TRIGGER dbo.trg_CheckSumTest_MaintainAuditSummary ON dbo.CheckSumTest AFTER INSERT, UPDATE, DELETE AS BEGIN IF (object_id('dbo.CheckSumTest') IS NOT NULL) UPDATE dbo.TableAuditSummary SET LastUpdate=GETDATE() WHERE TableName='dbo.CheckSumTest' END GO --Make an Update UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='a', vc2='b' WHERE id=2 --Check Meta-Data SELECT * FROM dbo.TableAuditSummary WHERE LastUpdate>LastExport --When we have Exported the data, we run the following to reset MetaData UPDATE dbo.TableAuditSummary SET LastExport=GETDATE() WHERE LastUpdate>LastExport 最近我正在读关天SQLSERVER在线帮助（BOL）相关的知识, 我接触到了 SQL Server CHECKSUM(), BINARY_CHECKSUM(), and CHECKSUM_AGG() 这几个函数, 由此突然想到这些函数是不是也可以监控表的数据变化，而事实证明CHECKSUM_AGG() 函数尽管被描述为检测表的变化，但这里不适用. 使用 CheckSum() and CheckSum_Agg() 函数 CHECKSUM_AGG() 函数, 在Books OnLine 和许多相关的站点上是这样描述的, 通常用于检测一个表的数据是否更改. 这是一个代替触发器的更好的方法，只是该操作会引起表扫描的操作。于是我这次我仍然使用元数据来跟踪数据的变化，只是新建了列LastChkSum代替了LastUpdate,该列用于保存CHECKSUM_AGG(BINARY_CHECKSUM(*)),它将会在全表中产生一个唯一值，以区别数据的变化情况。 代码如下： Listing 2. 复制代码 代码如下: --------------------------------------------- --Method 2 : using CheckSum (not reliable) --------------------------------------------- --Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Create Audit Summary Table to hold Meta-Data IF OBJECT_ID('dbo.TableAuditSummary', 'U') IS NOT NULL DROP TABLE dbo.TableAuditSummary CREATE TABLE dbo.TableAuditSummary ( id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, TableName sysname NOT NULL, LastChkSum INT NOT NULL ) GO INSERT dbo.TableAuditSummary (TableName, LastChkSum) SELECT 'dbo.CheckSumTest', CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest GO --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALL ... --Make a Simple (Single row) Update UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=1 --Tables that need exporting SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALL ... --Reset MetaData UPDATE dbo.TableAuditSummary SET LastChkSum=(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) WHERE TableName='dbo.CheckSumTest' --Make a Symmetric change UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=2 --Tables that need exporting (no rows returned as CHECKSUM_AGG() has not changed!!) SELECT * FROM dbo.TableAuditSummary WHERE TableName='dbo.CheckSumTest' AND LastChkSum<>(SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM dbo.CheckSumTest) UNION ALLCode Listing 2 正如你所看到的那样，对于单个的变化的情况，CHECKSUM是使用比较好的，但是CHECKSUM_AGG()却不能反应数据的变化 代码如下：Code Listing 3 复制代码 代码如下: --Base Table Definition IF OBJECT_ID('CheckSumTest', 'U') IS NOT NULL DROP TABLE CheckSumTest GO CREATE TABLE CheckSumTest ( id int IDENTITY(1,1) NOT NULL PRIMARY KEY, vc1 varchar(1) NOT NULL, vc2 varchar(1) NOT NULL, chksum1 AS (CHECKSUM(id, vc1, vc2)), chksum2 AS (BINARY_CHECKSUM(id, vc1, vc2)) ) GO INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'a', 'b' INSERT dbo.CheckSumTest (vc1, vc2) SELECT 'b', 'a' GO --Show Computed Columns and CheckSum_Agg() value = 199555 SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest --Make a Simple (Single row) Update UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=1 --Show Computed Columns and CheckSum_Agg() value = 204816 (Ok) SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest --Make a Symmetric change UPDATE dbo.CheckSumTest SET vc1='b', vc2='a' WHERE id=1 UPDATE dbo.CheckSumTest SET vc1='c', vc2='a' WHERE id=2 --Show Computed Columns and CheckSum_Agg() value = 204816 (Not Ok!) SELECT * FROM CheckSumTest SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM CheckSumTest 我们会发现调整前后 CHECKSUM_AGG(BINARY_CHECKSUM(*)) 的值是一样的，不能区分 结论： CHECKSUM_AGG() 函数尽管被描述为能监测表数据的变化，在实际测试中是不行的。尤其是对表进行对称数据修改时，无法监测 作者：Tyler Ning
