觸發(fā)器的操作與管理

觸發(fā)器的操作與管理第一頁(yè)，共三十二頁(yè)，編輯于2023年，星期五TriggerViewTriggerisaspecialkindofstoredprocedure,itcanbeeffectiveautomaticallywhileexecutingLanguageevent.觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，它在執(zhí)行語(yǔ)言事件時(shí)自動(dòng)生效。SQLServer2005包括兩大類(lèi)觸發(fā)器：DML觸發(fā)器和DDL觸發(fā)器。

（1）

DMLTriggerwillstartwhileoperatingdataindatabase.DMLeventsincludestheINSERT,UPDATEandDELETEstatement.DMLtrigger,DMLtriggercanqueryothertables,canincludecomplexTransact-SQLstatement.TheTriggerandthestatementbelongstothetriggeristreatedasasingletransactionwhichcanberolledback.Ifthefaultisdetected(forexamplediskisfull),thewholetransactionwillberolledback.DML觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)將啟用。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT語(yǔ)句、UPDATE語(yǔ)句或DELETE語(yǔ)句。DML觸發(fā)器可以查詢(xún)其他表，還可以包含復(fù)雜的Transact-SQL語(yǔ)句。將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤（例如，磁盤(pán)空間不足），則整個(gè)事務(wù)即自動(dòng)回滾。

第二頁(yè)，共三十二頁(yè)，編輯于2023年，星期五（2）DDLTriggeristhenewincreasedfunctionofSQLServer2005.

DMLTriggerwillstartwhileDDLeventoccurs.

DDL觸發(fā)器是SQLServer2005的新增功能。當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義語(yǔ)言(DDL)事件時(shí)將調(diào)用這些觸發(fā)器。第三頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1DML觸發(fā)器的創(chuàng)建和應(yīng)用

TriggerwillbecalledWhentheDMLeventofdatabaseoccurs,itmakessuredataoperationtoobeytheruleofsuchSQLstatement.

當(dāng)數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)將調(diào)用DML觸發(fā)器。從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。第四頁(yè)，共三十二頁(yè)，編輯于2023年，星期五

DML觸發(fā)器的主要優(yōu)點(diǎn)如下：（1）DMLTriggercanmaketherelatedtablescascadechange.Forexample,Youcanmakeadeletingtriggeronthecolomnsnoofthetablestudent,whenyoudeletethedataofstudenttable,therelatedrecordsofthetablescwillbealsodeleted.

DML觸發(fā)器可通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。例如，可以在student表的sno列上寫(xiě)入一個(gè)刪除觸發(fā)器，以使其他表中的各匹配行采取刪除操作。該觸發(fā)器用sno列作為惟一鍵，在sc表中對(duì)各匹配行進(jìn)行定位。第五頁(yè)，共三十二頁(yè)，編輯于2023年，星期五（2）DMLtriggercanpreventthemaliceorthefaultoftheoperationsofINSERT,UPDATEandDELETE,andenforcetoexecutethemorecomplexconstraintthenalimitationofacheck.

DML觸發(fā)器可以防止惡意或錯(cuò)誤的INSERT、UPDATE以及DELETE操作，并強(qiáng)制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其他限制。與CHECK約束不同，DML觸發(fā)器可以引用其他表中的列。（3）DMLtriggercanevaluatethestatesofatablebeforechangingorafterchanging,itcantakesomeactionsaccordingtothedifference.

DML觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。

第六頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建

Whenyoucreateatrigger,thefollowingoptionsmustbedenoted:當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定如下選項(xiàng)：

（1）Anameofthetrigger名稱(chēng)；（2）Thetablewhichthetriggerisdefinedon

在其上定義觸發(fā)器的表；（3）Thetimewhenthetriggeristriggered:觸發(fā)器將何時(shí)激發(fā)；（4）Thedatachangingstatementwhichcanwakethetrigger,moredatachangingstatementscanwakeonesametrigger.激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句，有效選項(xiàng)為INSERT、UPDATE或DELETE，多個(gè)數(shù)據(jù)修改語(yǔ)句可激活同一個(gè)觸發(fā)器；（5）Theprogrammingstatementwhichexecutetriggeroperation.執(zhí)行觸發(fā)操作的編程語(yǔ)句。

第七頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建DMLtriggeruseadeletedlogicaltableandainsertedlogicaltable.Theyhavethesamestructureasthebasictable,SQLServercancreateandmanagethetablesautomatically.Thetwologicaltableswhichstayatthememorytemporarycanbeusedtotesttheeffectiveofdatachangingandcansettheconditionoftriggeroperation.

DML觸發(fā)器使用deleted和inserted邏輯表。它們?cè)诮Y(jié)構(gòu)上和觸發(fā)器所在的表的結(jié)構(gòu)相同，SQLServer會(huì)自動(dòng)創(chuàng)建和管理這些表。可以使用這兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件。 第八頁(yè)，共三十二頁(yè)，編輯于2023年，星期五DeletedtableisusedtostorethecopyofthedataDeletedorupdated.Whenthestatementexecutes,thedataisdeletedandstoredintothedeletedtable.表用于存儲(chǔ)delete，update語(yǔ)句所影響的行的副本。在執(zhí)行delete或update語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。Insertedtableisusedtostorethecopyofthedatainsertedorupdated.Inainsertingoraupdatingtransaction,thenewrecordwillbeinsertedintotheinsertedtableandthebasictable.Therecordoftheinsertedtableisthedatacopyofthebasictable.表用于存儲(chǔ)Insert或update語(yǔ)句所影響的行的副本，在一個(gè)插入或更新事務(wù)處理中，新建的行被同時(shí)添加到Inserted表和觸發(fā)器表中。Inserted表中的行是觸發(fā)器表中新行的副本。第九頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建使用SQLServer管理平臺(tái)創(chuàng)建觸發(fā)器的過(guò)程如下：在SQLServer管理平臺(tái)中，展開(kāi)指定的服務(wù)器和數(shù)據(jù)庫(kù)項(xiàng)，然后展開(kāi)表，選擇并展開(kāi)要在其上創(chuàng)建觸發(fā)器的表，如圖9-1所示，右擊觸發(fā)器選項(xiàng)，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器創(chuàng)建窗口，如圖9-2所示。最后，單擊“執(zhí)行”按鈕，即可成功創(chuàng)建觸發(fā)器。圖9-1新建觸發(fā)器對(duì)話(huà)框

圖9-2新建觸發(fā)器窗口

第十頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建使用CREATETRIGGER命令創(chuàng)建DML觸發(fā)器的語(yǔ)法形式如下：

CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITH[ENCRYPTION]EXECUTEASClause][,...n]]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS{sql_statement[;][...n]|EXTERNALNAME<methodspecifier[;]>}<method_specifier>::=assembly_name.class_name.method_name

第十一頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建例9-1示例說(shuō)明inserted，deleted表的作用。執(zhí)行結(jié)果如右圖。程序清單如下：createtablesc(snochar(10),cnochar(2),scorereal)GoCREATETRIGGERtr1ONscFORINSERT,UPDATE,DELETEASPRINT‘inserted表：’Select*frominsertedPRINT‘deleted表：’Select*fromdeletedGo圖9-3觸發(fā)器的執(zhí)行結(jié)果

第十二頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.1DML觸發(fā)器創(chuàng)建例9-2

createatrigger,whichisforinsertingandupdatingdataoftables

創(chuàng)建一個(gè)觸發(fā)器，在s表上創(chuàng)建一個(gè)插入、更新類(lèi)型的觸發(fā)器。程序清單如下：CREATETRIGGERtr_sONsFORINSERT,UPDATEASBeginDECLARE@bhvarchar(6)SELECT@bh=inserted.snoFROMinserted/*獲取插入或更新操作時(shí)的新值（學(xué)號(hào)）*/End

第十三頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

1.使用INSERT觸發(fā)器INSERT觸發(fā)器通常被用來(lái)更新時(shí)間標(biāo)記字段，或者驗(yàn)證被觸發(fā)器監(jiān)控的字段中數(shù)據(jù)滿(mǎn)足要求的標(biāo)準(zhǔn)，以確保數(shù)據(jù)的完整性。例9-3建立一個(gè)觸發(fā)器，當(dāng)向sc表中添加數(shù)據(jù)時(shí)，如果添加的數(shù)據(jù)與s表中的數(shù)據(jù)不匹配（沒(méi)有對(duì)應(yīng)的學(xué)號(hào)），則將此數(shù)據(jù)刪除。(thistriggerisusedtoreplacetheforeignconstraint)程序清單如下：CREATETRIGGERsc_insONscFORINSERTASBEGINDECLARE@bhchar(5)Select@bh=Inserted.snofromInsertedIfnotexists(selectsnofromswheres.sno=@bh)Deletescwheresno=@bhEND

第十四頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

1.使用INSERT觸發(fā)器例9-4創(chuàng)建一個(gè)觸發(fā)器，當(dāng)插入或更新成績(jī)列時(shí)，該觸發(fā)器檢查插入的數(shù)據(jù)是否處于設(shè)定的范圍內(nèi)(thisistriggerisusedtoreplacecheckconstraint)。程序清單如下：CREATETRIGGERsc_insupdONscFORINSERT,UPDATEASDECLARE@cjint,SELECT@cj=inserted.scorefrominsertedIF（@cj<0or@cj>100）BEGINRAISERROR（'成績(jī)的取值必須在0到100之間',16,1）

ROLLBACKTRANSACTIONEND

第十五頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

2.使用UPDATE觸發(fā)器

當(dāng)在一個(gè)有UPDATE觸發(fā)器的表中修改記錄時(shí)，表中原來(lái)的記錄被移動(dòng)到刪除表中，修改過(guò)的記錄插入到了插入表中，觸發(fā)器可以參考刪除表和插入表以及被修改的表，以確定如何完成數(shù)據(jù)庫(kù)操作。例9-5創(chuàng)建一個(gè)修改觸發(fā)器，該觸發(fā)器防止用戶(hù)修改表s的入學(xué)成績(jī)。程序清單如下：createtriggertri_s_updonsforupdateasifupdate（escore）beginraiserror（‘不能修改入學(xué)成績(jī)’,16,10）rollbacktransactionendgo

第十六頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

使用UPDATE觸發(fā)例9-6DAS數(shù)據(jù)庫(kù)由存放實(shí)時(shí)數(shù)據(jù)的數(shù)據(jù)表以及存放歷史數(shù)據(jù)的歷史表組成。由于存放實(shí)時(shí)數(shù)據(jù)的數(shù)據(jù)表不斷更新，為了保存更新過(guò)的數(shù)據(jù)，在實(shí)時(shí)表和歷史表之間建立了觸發(fā)器。程序清單如下：CREATETRIGGERDasD_UTRIGGERONDasDFORUPDATEASBEGINIFUpdate(TV)/*數(shù)據(jù)更新*/BEGINUPDATEDasDSETUT=getdate() /*更新時(shí)間*/FROMDasD,insertedWHEREDasD.ID=inserted.ID INSERTDasDHis(ID,TV,UT)SELECTinserted.ID,inserted.TV,DasD.UTFROMDasD,insertedWHEREDasD.ID=inserted.ID/*將更新過(guò)的數(shù)據(jù)送入歷史庫(kù)*/ENDEND第十七頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

3.使用DELETE觸發(fā)器

DELETE觸發(fā)器通常用于兩種情況，第一種情況是為了防止那些確實(shí)需要?jiǎng)h除但會(huì)引起數(shù)據(jù)一致性問(wèn)題的記錄的刪除，第二種情況是執(zhí)行可刪除主記錄的子記錄的級(jí)聯(lián)刪除操作。例9-8建立一個(gè)與s表結(jié)構(gòu)一樣的表s1，當(dāng)刪除表s中的記錄時(shí)，自動(dòng)將刪除掉的記錄存放到s1表中。程序清單如下：CREATETRIGGERtr_delONs/*建立觸發(fā)器FORDELETE /*對(duì)表刪除操作ASinserts1select*fromdeleted/*將刪除掉的數(shù)據(jù)送入表s1中*/GO第十八頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.1.2

DML觸發(fā)器的應(yīng)用

3.使用DELETE觸發(fā)器

例9-9當(dāng)刪除表s中的記錄時(shí)，自動(dòng)刪除表sc中對(duì)應(yīng)學(xué)號(hào)的記錄。程序清單如下：CREATETRIGGERtr_del_sONsFORDELETEBEGINDECLARE@bhchar(5)Select@bh=deleted.snofromdeletedDeletescwheresno=@bhEND第十九頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.2DDL觸發(fā)器的創(chuàng)建和應(yīng)用

DDL觸發(fā)器會(huì)為響應(yīng)多種數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句而激發(fā)。這些語(yǔ)句主要是以CREATE、ALTER和DROP開(kāi)頭的語(yǔ)句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。DDL觸發(fā)器一般用于以下目的：（1）防止對(duì)數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些更改；（2）希望數(shù)據(jù)庫(kù)中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改；（3）要記錄數(shù)據(jù)庫(kù)架構(gòu)中的更改或事件。僅在運(yùn)行觸發(fā)DDL觸發(fā)器的DDL語(yǔ)句后，DDL觸發(fā)器才會(huì)激發(fā)。DDL觸發(fā)器無(wú)法作為INSTEADOF觸發(fā)器使用。

第二十頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.2.1創(chuàng)建DDL觸發(fā)器

使用CREATETRIGGER命令創(chuàng)建DDL觸發(fā)器的語(yǔ)法形式如下：

CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}[WITH<ddl_trigger_option>[,...n]]{FOR|AFTER}{event_type|event_group}[,...n]AS{sql_statement[;][...n]|EXTERNALNAME<methodspecifier>[;]}其中：

<ddl_trigger_option>::=[ENCRYPTION]EXECUTEASClause]<method_specifier>::=assembly_name.class_name.method_name第二十一頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.2.2DDL觸發(fā)器的應(yīng)用

在響應(yīng)當(dāng)前數(shù)據(jù)庫(kù)或服務(wù)器中處理的Transact-SQL事件時(shí)，可以激發(fā)DDL觸發(fā)器。觸發(fā)器的作用域取決于事件。例9-11使用DDL觸發(fā)器來(lái)防止數(shù)據(jù)庫(kù)中的任一表被修改或刪除。程序清單如下：

CREATETRIGGERsafetyONDATABASEFORDROP_TABLE,ALTER_TABLEASPRINT'YoumustdisableTrigger"safety"todroporaltertables!'ROLLBACK第二十二頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.2.2DDL觸發(fā)器的應(yīng)用例9-12使用DDL觸發(fā)器來(lái)防止在數(shù)據(jù)庫(kù)中創(chuàng)建表。程序清單如下：

CREATETRIGGERsafetyONDATABASEFORCREATE_TABLEASPRINT'CREATETABLEIssued.'SELECTEVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')RAISERROR('Newtablescannotbecreatedinthisdatabase.',16,1)ROLLBACK第二十三頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3DDL查看、修改和刪除觸發(fā)器11.3.1查看觸發(fā)器11.3.2修改觸發(fā)器11.3.3刪除觸發(fā)器第二十四頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3.1查看觸發(fā)器

如果要顯示作用于表上的觸發(fā)器究竟對(duì)表有哪些操作，必須查看觸發(fā)器信息。在SQLServer中，有多種方法可以查看觸發(fā)器信息，其中最常用的有如下兩種：（1）使用SQLServer管理平臺(tái)查看觸發(fā)器信息；（2）使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器。第二十五頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3.1查看觸發(fā)器（1）使用SQLServer管理平臺(tái)查看觸發(fā)器信息。在SQLServer管理平臺(tái)中，展開(kāi)服務(wù)器和數(shù)據(jù)庫(kù)，選擇并展開(kāi)表，然后展開(kāi)觸發(fā)器選項(xiàng)，右擊需要查看的觸發(fā)器名稱(chēng)，如圖9-4所示，從彈出的快捷菜單中，選擇“編寫(xiě)觸發(fā)器腳本為→create到→新查詢(xún)編輯器窗口”，則可以看到觸發(fā)器的源代碼。圖9-4查看觸發(fā)器

第二十六頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3.1查看觸發(fā)器（2）使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器。系統(tǒng)存儲(chǔ)過(guò)程sp_help、sp_helptext和sp_depends分別提供有關(guān)觸發(fā)器的不同信息。其具體用途和語(yǔ)法形式如下。sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱(chēng)、屬性、類(lèi)型和創(chuàng)建時(shí)間。

sp_help‘觸發(fā)器名稱(chēng)’sp_helptext：用于查看觸發(fā)器的正文信息。

sp_helptext‘觸發(fā)器名稱(chēng)’sp_depends：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。

sp_depends‘觸發(fā)器名稱(chēng)’

sp_depends‘表名’第二十七頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3.2修改觸發(fā)器通過(guò)SQLServer管理平臺(tái)、存儲(chǔ)過(guò)程，可以修改觸發(fā)器的正文和名稱(chēng)。1．使用SQLServer管理平臺(tái)修改觸發(fā)器正文。在管理平臺(tái)中，展開(kāi)指定的表，右擊要修改的觸發(fā)器，從彈出的快捷菜單中選擇“修改”選項(xiàng)，則會(huì)出現(xiàn)觸發(fā)器修改窗口，如圖9-5所示。在文本框中修改觸發(fā)器的SQL語(yǔ)句，單擊“語(yǔ)法檢查”按鈕，可以檢查語(yǔ)法是否正確，單擊“執(zhí)行”按鈕，可以成功修改此觸發(fā)器。圖9-5觸發(fā)器修改窗口

第二十八頁(yè)，共三十二頁(yè)，編輯于2023年，星期五11.3.2修改觸發(fā)器修改DML觸發(fā)器的語(yǔ)法形式如下：

ALTERTRIGGERschema_name.trigger_nameON(table|view)[WITH<dml_trigger_option>[,...n]](FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]AS

{sql_statement[;][...n]|EXTERNALNAME<methodspecifier>[;]}<dml_trigger_option>::=[ENCRYPTION][&lEXECUTEASClause>]<method_specifier>::=assembly_name.class_name.method