程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> 更多數據庫知識 >> SQL點滴24 監測表的變化

SQL點滴24 監測表的變化

編輯:更多數據庫知識

有時候,我們在某一重要的時間段需要監控某張表的變化情況,包含插入、更新、刪除。舉例來說,當我們把數據導出到外部的系統時,我們希望導出的是全部的數據,而且最好是導出上次導出之後變動的數據。
作為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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved