Oracle中对COLUMNS_UPDATED()返回值解析
本文详细讲述了Oracle中对COLUMNS_UPDATED()返回值解析。
需求来源
客户要求[某些特定的表]能[自定义预警报告]。
(在特定的表上)用户可定义某些字段有修改时，向有关用户发出消息警报<内容大致是xx单据的xx单号的xx字段由old变为了new>。最终目的是由消息控制模块向消息接收人报告这一变更。
基础知识:
COLUMNS_UPDATED()是一个仅可在InsertorUpdatetrigger中调用的方法。
该方法返回一个varbinary的值,存储了当次Insert或是Update触发器所对应的记录在哪些字段上发生了Insertedorupdated。在SQLSERVER的联机帮助[CREATETRIGGER]和[IFUPDATE]中有对COLUMNS_UPDATED()方法的简要描述。
公司要求用Trigger实现:(为每个[特定的表]编写一个特定的UPDATE触发器。)主要难点是穷举IFUPDATE(column)的方法不可行。其它业务实现无问题。后来仔细琢磨COLUMNS_UPDATED()所返回的值,问题得到解决。
这里只是讲述对COLUMNS_UPDATED()所返回的值的解析和运用，就不考虑用户指定变更字段及插入记录到消息表的那部分实现过程了。
测试数据准备
Ifexists(select*fromsysobjectswhereid=object_id(N'[dbo].[T_Test]')andxtype='u')DROPTableT_TestgoCREATETableT_Test(f_idintIDENTITY(1,1)PrimaryKey,f_charChar(8)default'',f_varcharvarchar(8)default'',f_nvarcharnvarchar(8)default'',f_datetimedatetimedefaultgetdate(),f_intintdefault0,f_bigintbigintdefault0,f_decimaldecimal(18,6)default0.00,f_numbernumeric(18,6)default0.00,f_floatfloatdefault0.00)goINSERTINTOT_Test(f_char)values('001')INSERTINTOT_Test(f_char)values('002')go
编写Update触发器
Ifexists(select*fromsysobjectswhereid=object_id(N'[dbo].[Tri_Test_Upd]')andobjectproperty(id,N'istrigger')=1)DROPTRIGGERTri_Test_UpdgoCREATETRIGGERTri_Test_UpdONT_Test--WITHENCRYPTIONFORUPDATEASDECLARE@iRowCntINTSET@iRowCnt=@@rowcountIF@iRowCnt<1RETURNDECLARE@sTableVARCHAR(128),@sPKNameVARCHAR(32),@sColNameVARCHAR(128)DECLARE@iColCntINT,@iColIdINTDECLARE@iTINYINT,@jTINYINT,@iSegmentTINYINT,@iValTINYINT,@iLog2TINYINTDECLARE@sSQLVARCHAR(8000)SET@sTable='t_test'SET@sPKName='f_id'--求得当前表列个数SELECT@iColCnt=Count(1)FROMsyscolumnsWHEREid=object_id(@sTable)--以8个字段为一小段SET@iSegment=CASEWHEN@iColCnt/8=@iColCnt/8.0THEN@iColCnt/8ELSE@iColCnt/8+1END--将数据存入临时表SELECT*INTO#InsertedFROMInsertedSELECT*INTO#DeletedFROMDeleted--中间处理数据用CREATETABLE#Temp(f_PKValvarchar(254)notnullprimarykey,f_OldValvarchar(254),f_NewValvarchar(254))SET@i=0WHILE@i<@iSegmentBEGINIF@iColCnt<9SET@iVal=COLUMNS_UPDATED()ELSESET@iVal=SubString(COLUMNS_UPDATED(),@i+1,1)--等于0,则表示当前小节所对应的8个字段无一被改.IF@iVal=0BEGINSET@i=@i+1CONTINUEENDWHILE@iVal>0BEGINSET@j=0SET@iLog2=@iVal/2WHILE@iLog2>0BEGINSET@j=@j+1SET@iLog2=@iLog2/2END--得到被Update的列IDSET@iColId=8*@i+@j+1--将Update列名赋予@sColNameSELECT@sColName=S.nameFROMInsertedasI,DeletedasD,SyscolumnsasSWHEREI.F_id=D.F_idANDS.id=object_id(@sTable)ANDS.colid=@iColIdTruncatetable#Temp--拼成动态语句SET@sSQL='INSERTINTO#Temp(f_PkVal,f_OldVal,f_NewVal)'+'SELECTConvert(varchar(200),I.'+@sPkName+'),'+'Convert(varchar(200),D.'+@sColName+'),'+'Convert(varchar(200),I.'+@sColName+')'+'FROM#InsertedasI,#DeletedasD'+'WHEREI.'+@sPKName+'=D.'+@sPKName+'ANDI.'+@sColName+'<>D.'+@sColNameEXEC(@sSQL)--测试输出Selectf_pkVal,@sColNameasf_column_name,f_oldVal,f_newValFROM#temp--实际上用将信息处理后插入消息表/*.....INSERTINTOT_Message(....)SELECT要组织的内容FROM#temp*/SET@iVal=@iVal-Power(2,@j)ENDSET@i=@i+1ENDDROPTABLE#InsertedDROPTABLE#DeletedDROPTABLE#Tempgo
测试数据
UpdateT_testSetf_datetime=getdate(),f_float=0.0123,f_int=1--上面Update语句共修改了三个列--实际输出1.)1f_int012f_int012.)1f_datetimeMay1520045:30PMMay1520045:31PM2f_datetimeMay1520045:30PMMay1520045:31PM3.)1f_float00.01232f_float00.0123
算法
COLUMNS_UPDATED()方法返回的varbinary，是以每个小节存储8个字段(的修改状态)的方式记录了当前触发器所有列的修改情形。因此程序以8个字段为一片段来循环处理所有字段。
SET@iVal=SubString(COLUMNS_UPDATED(),@i+1,1)程序用上面语句将一小节转化为整型，测试发现:（当且谨当这一小片只有一个字段有修改时）：
1,@iVal=1=2^(1-1);2,@iVal=2=2^(2-1);3,@iVal=4=2^(3-1);4,@iVal=8=2^(4-11);5,@iVal=16=2^(5-1);6,@iVal=32=2^(6-1);7,@iVal=64=2^(7-1);8,@iVal=128=2^(8-1);
而当且谨当1,2个字段有修改时:
@iVal=2^(1-1)+2^(2-1)=3;
而第2,5,8三个字段有修改时:
@iVal=2^(2-1)+2^(5-1)+2^(8-1)=146;...
当8个字段都有修改时:
@iVal=2^(1-1)+2^(2-1)+...+2^(8-1)=255;
也就是说无论怎样修改,@iVal的值，不外乎是2^n-1(n>0andn<9,int)这一数组型成的[和组合](组合时每个数组成员最多出现一次)。因此反过来推算:对@iVal按2^n分解,就可算得被修改列的列表。