MSSQL编程：COLUMNS_UPDATED()前往值的剖析
根据Ambrose所说，Sakila来自一种叫SiSwati的斯威士兰方言，也是在Ambrose的家乡乌干达附近的坦桑尼亚的Arusha的一个小镇的名字。需求碓:
客粢[某些特定的表]能[自定xA警蟾].
(在特定的表上)用艨啥x某些字段有修正r,向有P用舭l出动静警<热荽笾率xxxx翁的xx字段由old榱new>.最K目标是由动静把持模K向动静吸收人蟾孢@一更.
基A知R:
COLUMNS_UPDATED()是一H可在InsertorUpdatetrigger中{用的办法.
办法前往一varbinary的值,存α水次Insert或是Update|l器所挠在哪些字段上l生了Insertedorupdated.在SQLSERVER的C助和中有COLUMNS_UPDATED()办法的要形貌.
公司请求用TriggerF:(槊[特定的表]一特定的UPDATE|l器.)
次要yc是FeIFUPDATE(column)的办法不成行.其他IFo}.
后碜屑揣摩COLUMNS_UPDATED()所前往的值,}失掉解Q.
@e只是v述COLUMNS_UPDATED()所前往的值的剖析和用.就不考]用糁付ㄗ更字段及拔出到动静表的那部分F^程了.
--y.
Ifexists(select*fromsysobjectswhereid=object_id(N.)andxtype=u)
DROPTableT_Test
go
CREATETableT_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
)
go
INSERTINTOT_Test(f_char)values(001)
INSERTINTOT_Test(f_char)values(002)
go
--Update|l器
Ifexists(select*fromsysobjectswhereid=object_id(N.)andobjectproperty(id,Nistrigger)=1)
DROPTRIGGERTri_Test_Upd
go
CREATETRIGGERTri_Test_UpdONT_Test--WITHENCRYPTION
FORUPDATE
AS
DECLARE@iRowCntINT
SET@iRowCnt=@@rowcount
IF@iRowCnt<1
RETURN
DECLARE
@sTableVARCHAR(128),
@sPKNameVARCHAR(32),
@sColNameVARCHAR(128)
DECLARE
@iColCntINT,
@iColIdINT
DECLARE
@iTINYINT,
@jTINYINT,
@iSegmentTINYINT,
@iValTINYINT,
@iLog2TINYINT
DECLARE
@sSQLVARCHAR(8000)
SET@sTable=t_test
SET@sPKName=f_id
--求得前表列
SELECT@iColCnt=Count(1)FROMsyscolumnsWHEREid=object_id(@sTable)
--以8字段橐恍《
SET@iSegment=CASE
WHEN@iColCnt/8=@iColCnt/8.0
THEN
@iColCnt/8
ELSE
@iColCnt/8+1
END
--嫒Rr表
SELECT*INTO#InsertedFROMInserted
SELECT*INTO#DeletedFROMDeleted
--中g
CREATETABLE#Temp(
f_PKValvarchar(254)notnullprimarykey,
f_OldValvarchar(254),
f_NewValvarchar(254)
)
SET@i=0
WHILE@i<@iSegment
BEGIN
IF@iColCnt<9
SET@iVal=COLUMNS_UPDATED()
ELSE
SET@iVal=SubString(COLUMNS_UPDATED(),@i+1,1)
--等於0,t暗示前小所8字段o一被改.
IF@iVal=0
BEGIN
SET@i=@i+1
CONTINUE
END
WHILE@iVal>0
BEGIN
SET@j=0
SET@iLog2=@iVal/2
WHILE@iLog2>0
BEGIN
SET@j=@j+1
SET@iLog2=@iLog2/2
END
--失掉被Update的列ID
SET@iColId=8*@i+@j+1
--Update列名x予@sColName
SELECT@sColName=S.name
FROMInsertedasI,
DeletedasD,
SyscolumnsasS
WHEREI.F_id=D.F_id
ANDS.id=object_id(@sTable)
ANDS.colid=@iColId
Truncatetable#Temp
--拼成BZ句
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.+@sColName
EXEC(@sSQL)
--y出
Selectf_pkVal,@sColNameasf_column_name,f_oldVal,f_newValFROM#temp
--H上用⑿畔⑻砗蟛迦胂⒈
/*
.....
INSERTINTOT_Message(....)
SELECT要M的热
FROM#temp
*/
SET@iVal=@iVal-Power(2,@j)
END
SET@i=@i+1
END
DROPTABLE#Inserted
DROPTABLE#Deleted
DROPTABLE#Temp
go
--y
UpdateT_testSetf_datetime=getdate(),f_float=0.0123,f_int=1
--下面UpdateZ句共修正了三列
--H出
1.)
1f_int01
2f_int01
2.)
1f_datetimeMay1520045:30PMMay1520045:31PM
2f_datetimeMay1520045:30PMMay1520045:31PM
3.)
1f_float00.0123
2f_float00.0123
--算法
COLUMNS_UPDATED()办法前往的varbinary,是以每小存8字段(的修正B)的体例了前|l器一切列的修正情况.因而程序以8字段橐黄硌h硭凶侄.
SET@iVal=SubString(COLUMNS_UPDATED(),@i+1,1)
程序用下面Z句⒁恍」D化檎.ylF:
且@一小片只要一字段有修正r
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字段有修正r:
@iVal=2^(1-1)+2^(2-1)=3;
而第2,5,8三字段有修正r:
@iVal=2^(2-1)+2^(5-1)+2^(8-1)=146;
...
8字段都有修正r:
@iVal=2^(1-1)+2^(2-1)+...+2^(8-1)=255;
也就是fo怎有薷,@iVal的值,不过乎是2^n-1(n>0andn<9,int)@一到M型成的[和M合](M合r每到M成T最多出F一次).因而反^硗扑:@iVal按2^n分化,便可算得被修正列的列表.
至此,完成了整Trigger的算法.
既能够作为一个单独的应用程序应用在客户端服务器网络环境中，也能够作为一个库而嵌入到其他的软件中。 varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。 分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。 呵呵，这就是偶想说的 XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！） 如果是将来做数据库的开发设计，就应该详细学习T-SQL的各种细节，包括T-SQL的程序设计、存储过程、触发器以及具体使用某个开发语言来访问数据库。 也可谈一下你是怎么优化存储过程的? 以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了 我个人认为就是孜孜不懈的学习
页:
[1]