SQL Server SQL点滴24 监测表的变化示例

发布时间：2020-12-12 07:46:48 所属栏目：MsSql教程来源：网络整理

导读：感兴趣的小伙伴，下面一起跟随编程之家 jb51.cc的小编两巴掌来看看吧！有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出上次导出之

感兴趣的小伙伴，下面一起跟随编程之家 52php.cn的小编两巴掌来看看吧！

有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出上次导出之后变动的数据。
作为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,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',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',vc2 varchar(1) NOT NULL,chksum1 AS (CHECKSUM(id,vc1,vc2)),chksum2 AS (BINARY_CHECKSUM(id,vc2)) 
) 
GO 
INSERT dbo.CheckSumTest (vc1,'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=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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!