SQL点滴24 监测表的变化

时间:2022-05-31 02:54:03

有时候,我们在某一重要的时间段需要监控某张表的变化情况,包含插入、更新、删除。举例来说,当我们把数据导出到外部的系统时,我们希望导出的是全部的数据,而且最好是导出上次导出之后变动的数据。 
作为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