在網(wǎng)上看到一篇關(guān)于監(jiān)測表中的插入，更新，刪除的方法，使用觸發(fā)器實(shí)現(xiàn)的，很有價值。
?
地址： http://www.dbaunion.com/u/livecoach/Blog.aspx/t-19
?
有時候，我們在某一重要的時間段需要監(jiān)控某張表的變化情況，包含插入、更新、刪除。舉例來說，當(dāng)我們把數(shù)據(jù)導(dǎo)出到外部的系統(tǒng)時，我們希望導(dǎo)出的是全部的數(shù)據(jù)，而且最好是導(dǎo)出上次導(dǎo)出之后變動的數(shù)據(jù)。
作為DBA，我們可采傳統(tǒng)的觸發(fā)器操作，來構(gòu)建一個元數(shù)據(jù)表或一個時間戳列來監(jiān)控數(shù)據(jù)的變化。
代碼如下：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
?
最近我正在讀關(guān)天SQLSERVER在線幫助（BOL）相關(guān)的知識, 我接觸到了 SQL Server CHECKSUM(), BINARY_CHECKSUM(), and CHECKSUM_AGG() 這幾個函數(shù), 由此突然想到這些函數(shù)是不是也可以監(jiān)控表的數(shù)據(jù)變化，而事實(shí)證明CHECKSUM_AGG() 函數(shù)盡管被描述為檢測表的變化，但這里不適用.
?
使用 CheckSum() and CheckSum_Agg() 函數(shù)
CHECKSUM_AGG() 函數(shù), 在Books OnLine 和許多相關(guān)的站點(diǎn)上是這樣描述的, 通常用于檢測一個表的數(shù)據(jù)是否更改. 這是一個代替觸發(fā)器的更好的方法，只是該操作會引起表掃描的操作。于是我這次我仍然使用元數(shù)據(jù)來跟蹤數(shù)據(jù)的變化，只是新建了列LastChkSum代替了LastUpdate,該列用于保存CHECKSUM_AGG(BINARY_CHECKSUM(*)),它將會在全表中產(chǎn)生一個唯一值，以區(qū)別數(shù)據(jù)的變化情況。
代碼如下： 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()卻不能反應(yīng)數(shù)據(jù)的變化
代碼如下：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
?
我們會發(fā)現(xiàn)調(diào)整前后 CHECKSUM_AGG(BINARY_CHECKSUM(*)) 的值是一樣的，不能區(qū)分
?
結(jié)論：
CHECKSUM_AGG() 函數(shù)盡管被描述為能監(jiān)測表數(shù)據(jù)的變化，在實(shí)際測試中是不行的。尤其是對表進(jìn)行對稱數(shù)據(jù)修改時，無法監(jiān)測
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
