SQLServer触发器

1、SQLServer触发器测试表1>CREATETABLEtest_trigger_table(2>idINT,3>nameVARCHAR(10),4>valINT5>);6>goINSERT注：SQLServer中。没有BEFOREINSERT或者AFTERINSERT根据文档显示，以及代码的测试。基本上类似于Oracle的AFTERINSERTSQLSercer触发器没有FOREACHROW关键字一次更新一条，还是多条，取决于INSERTED里面的内容。1>CREATETRIGGERBefore

2、InsertTesttest_trigger_table3>FORINSERT4>AS5>DECLARE6>IdINT,7>NameVARCHAR(10);8>BEGIN9>PRINT('BEFOREINSERT');10>SELECTId=id,Name=nameFROMINSERTED;11>PRINT('NewName='+Name);12>UPDATEtest_trigger_tableSETval=100WHERE

3、id=Id;13>END;14>go1>2>INSERTINTOtest_trigger_table(id,name)VALUES(1,'ABC'）;3>goBEFOREINSERTNewName=ABC（1行受影响）1>select*fromtest_trigger_table;2>goidnameval1ABC100（1行受影响）UPDATE1>CREATETRIGGERAfterUpdateTest2>ONtest_trigger_table3&a

4、mp;gt;FORUPDATE4>AS6>OldNameVARCHAR(10),7>NewNameVARCHAR(10);8>BEGIN9>PRINT('AFTERUPDATE');10>SELECTNewName=nameFROMinserted;11>SELECTOldName=nameFROMdeleted;12>PRINT('OldName='+OldName);13>PRINT('NewName='+NewNam

5、e);14>END;15>go1>2>UPDATEtest_trigger_tableSETname='XYZ'WHEREid=1;3>goAFTERUPDATEOldName=ABC(1行受影响)NewName=XYZDELETE1>CREATETRIGGERAfterDeleteTest2>ONtest_trigger_table3>FORDELETE4>AS5>DECLARE6>OldNameVARCHAR(10);7&

6、amp;gt;BEGIN8>PRINT('AFTERDELETE');9>SELECTOldName=nameFROMdeleted;10>PRINT('OldName='+OldName);11>END;12>go1>DELETEFROMtest_trigger_tableWHEREid=1;2>goDELETE(1行受影响)OldName=XYZINSERT/UPDATE/DELETE行为判断测试此处时，先删除前面的3个触发器1>CREATETRI

7、GGERAfterAllTest2>ONtest_trigger_table3>FORINSERT,UPDATE,DELETE4>AS5>BEGIN6>PRINT('AFTERALL');7>IFEXISTS(SELECT1FROMinserted)ANDNOTEXISTS(SELECT1FROMdeleted)8>BEGIN9>PRINT('INSERTING');10>END;11>IFEXISTS(SELECT1FRO

8、Minserted)ANDEXISTS(SELECT1FROMdeleted)12>BEGIN13>PRINT('UPDATING');14>END;15>IFNOTEXISTS(SELECT1FROMinserted)ANDEXISTS(SELECT1FROMdeleted)16>BEGIN17>PRINT('DELETING');18>END;19>END;20>go1>INSERTINTOtest_trigger_ta

9、ble(id,name)VALUES(1,'ABC');2>UPDATEtest_trigger_tableSETname='XYZ'WHEREid=1;3>DELETEFROMtest_trigger_tableWHEREid=1;4>goINSERTING(1行受影响)AFTERALLUPDATINGAFTERALLDELETING1>SQLServerINSERT/UPDATE/DELETE多行1>CREATETRIGGERAfterAllTest22>ONtest_tri

10、gger_table3>FORINSERT,UPDATE,DELETE4>AS5>DECLARE6>InsertCountINT,7>DeleteCountINT;8>BEGIN9>PRINT('AFTERALL2');10>SELECTInsertCount=COUNT(1)FROMinserted;11>SELECTDeleteCount=COUNT(2)FROMdeleted;12>PRINT('InsertCount=

11、9;+STR(InsertCount);13>PRINT('DeleteCount='+STR(DeleteCount);14>END;15>go1>INSERTINTOtest_trigger_table(id,name)VALUES(1,'ABC');2>goAFTERALLINSERTINGAFTERALL2InsertCount=1(1行受影响)DeleteCount=01>INSERTINTOtest_trigger_table(id,name)VALUES(2,

12、9;DEF');2>goAFTERALLINSERTINGAFTERALL2InsertCount=1（1行受影响）DeleteCount=01>insertintotest_trigger_tableselect*fromtest_trigger_table;2>goAFTERALLINSERTINGAFTERALL2InsertCount=2（2行受影响）DeleteCount=01>UPDATEtest_trigger_tableSETname='XYZ'WHEREid=1;2>goUPDATI

13、NGAFTERALL2InsertCount=2(2行受影响)DeleteCount=21>针对特定列的触发测试此处时，先删除其它的触发器SQLServer通过COLUMNS_UPDATED来判断哪些列被更新COLUMNS_UPDATED函数以从左到右的顺序返回位，最左边的为最不重要的位。最左边的位表示表中的第一列；向右的下一位表示第二列，依此类推。如果在表上创建的触发器包含8列以上，则COLUMNS_UPDATED返回多个字节，最左边的为最不重要的字节。在INSERT操作中COLUMNS_UPDATED将对所有列返回TRUE值，因为这些列插入了显式值或隐性(NULL)值。2&a

14、mp;gt;go1>CREATETRIGGERAfterUpdateTest2>ONtest_trigger_table3>FORINSERT,UPDATE4>AS5>DECLARE6>OldValVARCHAR(10),7>NewValVARCHAR(10);8>BEGIN9>10>IF(COLUMNS_UPDATED()&4)>011>BEGIN12>PRINT('AFTERUPDATE

15、OnlyVal');13>SELECTNewVal=valFROMinserted;14>SELECTOldVal=valFROMdeleted;OldVal);16>PRINT('NewVal='+NewVal);17>END;18>END19>go1>INSERTINTOtest_trigger_table(id,name,val)VALUES(1,'ABC',1);2>goAFTERUPDATEOnlyVal(1行受影响)NewVal=1

16、1>2>UPDATEtest_trigger_tableSETname='XYZ'WHEREid=1;3>go(1行受影响)1>2>UPDATEtest_trigger_tableSETval=20WHEREid=1;3>goAFTERUPDATEOnlyValOldVal=1(1行受影响)NewVal=20已创建的触发器的查询1>select2>name3>from4>sysobjects5>where6>xt

17、ype='TR'7>andparent_obj=object_id('table_1')8>gonameAfterInsertTable1(1行受影响)DDL触发器数据库级别1>CREATETRIGGERCretaeTableTrigger2>ONDATABASE3>FORCREATE_TABLE4>AS5>BEGIN6>DECLAREEventDataASxml;7>8>DECLAREEventTypeASvarchar(

18、100);9>DECLAREPostTimeASvarchar(100);10>DECLAREServerNameASvarchar(100);11>DECLARELoginNameASvarchar(100);12>DECLAREDatabaseNameASvarchar(100);13>DECLARECommandTextASvarchar(100);14>'varchar(100)' ),'varchar(100)' ),'varchar(100)' ),&

19、#39;varchar(100)' ),EVENTDATA();16>17>SELECT18>EventTypet.c.value('(EventType)1',19>PostTimet.c.value('(PostTime)1',20>ServerNamet.c.value('(ServerName)1',21>LoginNamet.c.value('(LoginName)1',22>DatabaseName=t.c.val

20、ue('(DatabaseName)1','varchar(100)'),23>CommandText=t.c.value('(TSQLCommand/CommandText)1','varchar(100)')24>FROM25>t(c);EventData.nodes('/EVENT_INSTANCE')27>PRINT('CREATE_TABLE');28>PRINT(EventType);29>PRINT(P

21、ostTime);30>PRINT(ServerName);31>PRINT(LoginName);32>PRINT(DatabaseName);33>PRINT(CommandText);34>END35>go1>createtableyyy(aINT);2>goCREATE_TABLECREATE_TABLE2010-10-01T21:32:24.843HOME-BED592453CSQLEXPRESSHOME-BED592453CwzqStockcreatetableyyy(a

22、INT);DDL触发器服务器级别1>CREATETRIGGERddl_trig_database2>ONALLSERVER3>FORCREATE_DATABASE4>AS5>BEGIN6>DECLAREEventDataASxml;7>DECLAREDatabaseNameASvarchar(100);8>DECLARESqlNVARCHAR(200);9>10>SETEventData=EVENTDATA();12>SELECT13>Databa