原文:
SQL点滴24—监测表的变化（转载）
在网上看到一篇关于监测表中的插入，更新，删除的方法，使用触发器实现的，很有价值。
地址： http://www.dbaunion.com/u/livecoach/Blog.aspx/t-19
有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出上次导出之后变动的数据。
作为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() 函数尽管被描述为能监测表数据的变化，在实际测试中是不行的。尤其是对表进行对称数据修改时，无法监测
