提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

SQL点滴24监测表的变化

SQL 变化 监测 24 点滴
2023-06-13 09:14:30 时间
有时候，我们在某一重要的时间段需要监控某张表的变化情况，包含插入、更新、删除。举例来说，当我们把数据导出到外部的系统时，我们希望导出的是全部的数据，而且最好是导出上次导出之后变动的数据。
作为DBA，我们可采传统的触发器操作，来构建一个元数据表或一个时间戳列来监控数据的变化。
代码如下：CodeListing1
该代码在SQL2005(SP3),SQL2008R2(RTMwithcu5)测试通过
复制代码代码如下:

-------------------
--Method1:TRIGGER
-------------------
--BaseTableDefinition
IFOBJECT_ID("CheckSumTest","U")ISNOTNULLDROPTABLECheckSumTest
GO
CREATETABLECheckSumTest
(
idintIDENTITY(1,1)NOTNULLPRIMARYKEY,
vc1varchar(1)NOTNULL,
vc2varchar(1)NOTNULL
)
GO
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"a","b"
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"b","a"
GO
--CreateAuditSummaryTabletoholdMeta-Data
IFOBJECT_ID("dbo.TableAuditSummary","U")ISNOTNULLDROPTABLEdbo.TableAuditSummary
CREATETABLEdbo.TableAuditSummary
(idINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
TableNamesysnameNOTNULL,
LastUpdateDATETIMENOTNULL,
LastExportDATETIMENOTNULL
)
GO
INSERTdbo.TableAuditSummary(TableName,LastUpdate,LastExport)VALUES("dbo.CheckSumTest",GETDATE(),GETDATE())
GO
--Tablesthatneedexporting
SELECT*FROMdbo.TableAuditSummaryWHERELastUpdate>LastExport
--CreateTriggeronallBaseTables
--Thisfiresonanyinsert/update/deleteandwritesnewLastUpdatecolumnforthetablesettoCurrentDateandTime
IFOBJECT_ID("dbo.trg_CheckSumTest_MaintainAuditSummary","TR")ISNOTNULLDROPTRIGGERdbo.trg_CheckSumTest_MaintainAuditSummary
GO
CREATETRIGGERdbo.trg_CheckSumTest_MaintainAuditSummary
ONdbo.CheckSumTest
AFTERINSERT,UPDATE,DELETE
AS
BEGIN
IF(object_id("dbo.CheckSumTest")ISNOTNULL)
UPDATEdbo.TableAuditSummarySETLastUpdate=GETDATE()WHERETableName="dbo.CheckSumTest"
END
GO
--MakeanUpdate
UPDATEdbo.CheckSumTestSETvc1="b",vc2="a"WHEREid=1
UPDATEdbo.CheckSumTestSETvc1="a",vc2="b"WHEREid=2
--CheckMeta-Data
SELECT*FROMdbo.TableAuditSummaryWHERELastUpdate>LastExport
--WhenwehaveExportedthedata,werunthefollowingtoresetMetaData
UPDATEdbo.TableAuditSummarySETLastExport=GETDATE()WHERELastUpdate>LastExport

最近我正在读关天SQLSERVER在线帮助（BOL）相关的知识,我接触到了SQLServerCHECKSUM(),BINARY_CHECKSUM(),andCHECKSUM_AGG()这几个函数,由此突然想到这些函数是不是也可以监控表的数据变化，而事实证明CHECKSUM_AGG()函数尽管被描述为检测表的变化，但这里不适用.
使用CheckSum()andCheckSum_Agg()函数
CHECKSUM_AGG()函数,在BooksOnLine和许多相关的站点上是这样描述的,通常用于检测一个表的数据是否更改.这是一个代替触发器的更好的方法，只是该操作会引起表扫描的操作。于是我这次我仍然使用元数据来跟踪数据的变化，只是新建了列LastChkSum代替了LastUpdate,该列用于保存CHECKSUM_AGG(BINARY_CHECKSUM(*)),它将会在全表中产生一个唯一值，以区别数据的变化情况。
代码如下：Listing2.
复制代码代码如下:

---------------------------------------------
--Method2:usingCheckSum(notreliable)
---------------------------------------------
--BaseTableDefinition
IFOBJECT_ID("CheckSumTest","U")ISNOTNULLDROPTABLECheckSumTest
GO
CREATETABLECheckSumTest
(
idintIDENTITY(1,1)NOTNULLPRIMARYKEY,
vc1varchar(1)NOTNULL,
vc2varchar(1)NOTNULL
)
GO
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"a","b"
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"b","a"
GO
--CreateAuditSummaryTabletoholdMeta-Data
IFOBJECT_ID("dbo.TableAuditSummary","U")ISNOTNULLDROPTABLEdbo.TableAuditSummary
CREATETABLEdbo.TableAuditSummary
(idINTIDENTITY(1,1)NOTNULLPRIMARYKEY,
TableNamesysnameNOTNULL,
LastChkSumINTNOTNULL
)
GO
INSERTdbo.TableAuditSummary(TableName,LastChkSum)
SELECT"dbo.CheckSumTest",CHECKSUM_AGG(BINARY_CHECKSUM(*))FROMdbo.CheckSumTest
GO
--Tablesthatneedexporting
SELECT*FROMdbo.TableAuditSummaryWHERETableName="dbo.CheckSumTest"
ANDLastChkSum<>(SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMdbo.CheckSumTest)
UNIONALL
...
--MakeaSimple(Singlerow)Update
UPDATEdbo.CheckSumTestSETvc1="c",vc2="a"WHEREid=1
--Tablesthatneedexporting
SELECT*FROMdbo.TableAuditSummaryWHERETableName="dbo.CheckSumTest"
ANDLastChkSum<>(SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMdbo.CheckSumTest)
UNIONALL
...
--ResetMetaData
UPDATEdbo.TableAuditSummarySETLastChkSum=(SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMdbo.CheckSumTest)
WHERETableName="dbo.CheckSumTest"
--MakeaSymmetricchange
UPDATEdbo.CheckSumTestSETvc1="b",vc2="a"WHEREid=1
UPDATEdbo.CheckSumTestSETvc1="c",vc2="a"WHEREid=2
--Tablesthatneedexporting(norowsreturnedasCHECKSUM_AGG()hasnotchanged!!)
SELECT*FROMdbo.TableAuditSummaryWHERETableName="dbo.CheckSumTest"
ANDLastChkSum<>(SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMdbo.CheckSumTest)
UNIONALLCodeListing2

正如你所看到的那样，对于单个的变化的情况，CHECKSUM是使用比较好的，但是CHECKSUM_AGG()却不能反应数据的变化
代码如下：CodeListing3
复制代码代码如下:
--BaseTableDefinition
IFOBJECT_ID("CheckSumTest","U")ISNOTNULLDROPTABLECheckSumTest
GO
CREATETABLECheckSumTest
(
idintIDENTITY(1,1)NOTNULLPRIMARYKEY,
vc1varchar(1)NOTNULL,
vc2varchar(1)NOTNULL,
chksum1AS(CHECKSUM(id,vc1,vc2)),
chksum2AS(BINARY_CHECKSUM(id,vc1,vc2))
)
GO
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"a","b"
INSERTdbo.CheckSumTest(vc1,vc2)SELECT"b","a"
GO
--ShowComputedColumnsandCheckSum_Agg()value=199555
SELECT*FROMCheckSumTest
SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMCheckSumTest
--MakeaSimple(Singlerow)Update
UPDATEdbo.CheckSumTestSETvc1="c",vc2="a"WHEREid=1
--ShowComputedColumnsandCheckSum_Agg()value=204816(Ok)
SELECT*FROMCheckSumTest
SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMCheckSumTest
--MakeaSymmetricchange
UPDATEdbo.CheckSumTestSETvc1="b",vc2="a"WHEREid=1
UPDATEdbo.CheckSumTestSETvc1="c",vc2="a"WHEREid=2
--ShowComputedColumnsandCheckSum_Agg()value=204816(NotOk!)
SELECT*FROMCheckSumTest
SELECTCHECKSUM_AGG(BINARY_CHECKSUM(*))FROMCheckSumTest

我们会发现调整前后CHECKSUM_AGG(BINARY_CHECKSUM(*))的值是一样的，不能区分
结论：
CHECKSUM_AGG()函数尽管被描述为能监测表数据的变化，在实际测试中是不行的。尤其是对表进行对称数据修改时，无法监测
作者：TylerNing

相关文章