SQLServer觸發(fā)器

1、SQLServer觸發(fā)器測(cè)試表1>CREATETABLEtest_trigger_table(2>idINT,3>nameVARCHAR(10),4>valINT5>);6>goINSERT注：SQLServer中。沒(méi)有BEFOREINSERT或者AFTERINSERT根據(jù)文檔顯示，以及代碼的測(cè)試?；旧项愃朴贠racle的AFTERINSERTSQLSercer觸發(fā)器沒(méi)有FOREACHROW關(guān)鍵字一次更新一條，還是多條，取決于INSERTED里面的內(nèi)容。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行為判斷測(cè)試此處時(shí)，先刪除前面的3個(gè)觸發(fā)器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>針對(duì)特定列的觸發(fā)測(cè)試此處時(shí)，先刪除其它的觸發(fā)器SQLServer通過(guò)COLUMNS_UPDATED來(lái)判斷哪些列被更新COLUMNS_UPDATED函數(shù)以從左到右的順序返回位，最左邊的為最不重要的位。最左邊的位表示表中的第一列；向右的下一位表示第二列，依此類推。如果在表上創(chuàng)建的觸發(fā)器包含8列以上，則COLUMNS_UPDATED返回多個(gè)字節(jié)，最左邊的為最不重要的字節(jié)。在INSERT操作中COLUMNS_UPDATED將對(duì)所有列返回TRUE值，因?yàn)檫@些列插入了顯式值或隱性(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已創(chuàng)建的觸發(fā)器的查詢1>select2>name3>from4>sysobjects5>where6>xt

17、ype='TR'7>andparent_obj=object_id('table_1')8>gonameAfterInsertTable1(1行受影響)DDL觸發(fā)器數(shù)據(jù)庫(kù)級(jí)別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觸發(fā)器服務(wù)器級(jí)別1>CREATETRIGGERddl_trig_database2>ONALLSERVER3>FORCREATE_DATABASE4>AS5>BEGIN6>DECLAREEventDataASxml;7>DECLAREDatabaseNameASvarchar(100);8>DECLARESqlNVARCHAR(200);9>10>SETEventData=EVENTDATA();12>SELECT13>Databa