SQL點(diǎn)滴24 監(jiān)測表的變化

更新時(shí)間：2011年09月23日 01:24:25 作者：

在網(wǎng)上看到一篇關(guān)于監(jiān)測表中的插入，更新，刪除的方法，使用觸發(fā)器實(shí)現(xiàn)的，很有價(jià)值。

有時(shí)候，我們在某一重要的時(shí)間段需要監(jiān)控某張表的變化情況，包含插入、更新、刪除。舉例來說，當(dāng)我們把數(shù)據(jù)導(dǎo)出到外部的系統(tǒng)時(shí)，我們希望導(dǎo)出的是全部的數(shù)據(jù)，而且最好是導(dǎo)出上次導(dǎo)出之后變動(dòng)的數(shù)據(jù)。
作為DBA，我們可采傳統(tǒng)的觸發(fā)器操作，來構(gòu)建一個(gè)元數(shù)據(jù)表或一個(gè)時(shí)間戳列來監(jiān)控?cái)?shù)據(jù)的變化。
代碼如下：Code Listing 1
該代碼在 SQL 2005(SP3), SQL 2008 R2 (RTM with cu5)測試通過

復(fù)制代碼代碼如下:

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

最近我正在讀關(guān)天SQLSERVER在線幫助（BOL）相關(guān)的知識, 我接觸到了 SQL Server CHECKSUM(), BINARY_CHECKSUM(), and CHECKSUM_AGG() 這幾個(gè)函數(shù), 由此突然想到這些函數(shù)是不是也可以監(jiān)控表的數(shù)據(jù)變化，而事實(shí)證明CHECKSUM_AGG() 函數(shù)盡管被描述為檢測表的變化，但這里不適用.
使用 CheckSum() and CheckSum_Agg() 函數(shù)
CHECKSUM_AGG() 函數(shù), 在Books OnLine 和許多相關(guān)的站點(diǎn)上是這樣描述的, 通常用于檢測一個(gè)表的數(shù)據(jù)是否更改. 這是一個(gè)代替觸發(fā)器的更好的方法，只是該操作會(huì)引起表掃描的操作。于是我這次我仍然使用元數(shù)據(jù)來跟蹤數(shù)據(jù)的變化，只是新建了列LastChkSum代替了LastUpdate,該列用于保存CHECKSUM_AGG(BINARY_CHECKSUM(*)),它將會(huì)在全表中產(chǎn)生一個(gè)唯一值，以區(qū)別數(shù)據(jù)的變化情況。
代碼如下： Listing 2.

復(fù)制代碼代碼如下:

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

正如你所看到的那樣，對于單個(gè)的變化的情況，CHECKSUM是使用比較好的，但是CHECKSUM_AGG()卻不能反應(yīng)數(shù)據(jù)的變化
代碼如下：Code Listing 3

復(fù)制代碼代碼如下:

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

我們會(huì)發(fā)現(xiàn)調(diào)整前后 CHECKSUM_AGG(BINARY_CHECKSUM(*)) 的值是一樣的，不能區(qū)分
結(jié)論：
CHECKSUM_AGG() 函數(shù)盡管被描述為能監(jiān)測表數(shù)據(jù)的變化，在實(shí)際測試中是不行的。尤其是對表進(jìn)行對稱數(shù)據(jù)修改時(shí)，無法監(jiān)測
作者：Tyler Ning