SQL点滴24 监测表的变化

复制代码 代码如下:

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

复制代码 代码如下:

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

复制代码 代码如下: