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