存儲(chǔ)過(guò)程與觸發(fā)器綜合概述

第8章存儲(chǔ)過(guò)程與觸發(fā)器本章學(xué)習(xí)目標(biāo)l理解存儲(chǔ)過(guò)程和觸發(fā)器的作用l

熟練創(chuàng)建和管理存儲(chǔ)過(guò)程及觸發(fā)器l

靈活運(yùn)用存儲(chǔ)過(guò)程和觸發(fā)器提高系統(tǒng)開(kāi)發(fā)效率

8.1存儲(chǔ)過(guò)程

8.1.1存儲(chǔ)過(guò)程基礎(chǔ)知識(shí)

1．存儲(chǔ)過(guò)程簡(jiǎn)介

存儲(chǔ)過(guò)程是存儲(chǔ)在服務(wù)器上的由SQL語(yǔ)句和控制流語(yǔ)句組成的一個(gè)預(yù)編譯集合。存儲(chǔ)過(guò)程劃分為系統(tǒng)存儲(chǔ)過(guò)程以及用戶存儲(chǔ)過(guò)程。一個(gè)存儲(chǔ)過(guò)程可以作為一個(gè)獨(dú)立的單元進(jìn)行處理。在SQLServer中可以通過(guò)存儲(chǔ)過(guò)程來(lái)完成很多管理任務(wù)，利用一些常用的系統(tǒng)存儲(chǔ)過(guò)程可以很方便的查看、操作數(shù)據(jù)庫(kù)對(duì)象。系統(tǒng)存儲(chǔ)過(guò)程的命名通常以”sp_”作為前綴，并且存儲(chǔ)于Master數(shù)據(jù)庫(kù)中，如果用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫(kù)中調(diào)用這些存儲(chǔ)過(guò)程。

存儲(chǔ)過(guò)程提供了一種封裝某一個(gè)需要重復(fù)執(zhí)行任務(wù)的方法。一旦定義了一個(gè)存儲(chǔ)過(guò)程之后，在應(yīng)用程序中就可以對(duì)其進(jìn)行調(diào)用。在存儲(chǔ)過(guò)程中，不僅可以包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢，而且也可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集。所以通過(guò)設(shè)計(jì)自定義用戶存儲(chǔ)過(guò)程，用戶可以使其實(shí)現(xiàn)強(qiáng)大的編程功能。

使用存儲(chǔ)過(guò)程可以比單獨(dú)的SQL語(yǔ)句完成更為復(fù)雜的功能，并且系統(tǒng)會(huì)對(duì)存儲(chǔ)過(guò)程中的SQL語(yǔ)句進(jìn)行了預(yù)編譯處理，使得執(zhí)行速度有了大幅度的提升。存儲(chǔ)過(guò)程被第一次調(diào)用后，會(huì)保存在高速緩沖區(qū)中，這樣再次執(zhí)行同一個(gè)存儲(chǔ)過(guò)程時(shí)，會(huì)提高了重復(fù)調(diào)用的效率。在實(shí)際應(yīng)用中，可以將復(fù)雜的商業(yè)規(guī)則封裝在存儲(chǔ)過(guò)程中，從而提高程序語(yǔ)句的利用率。

2．存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程一旦執(zhí)行一次后，其執(zhí)行的計(jì)劃就會(huì)駐留在計(jì)算機(jī)的高速緩沖存儲(chǔ)器中。其后對(duì)同一個(gè)存儲(chǔ)過(guò)程的調(diào)用就可以直接利用編譯后在高速緩存中的二進(jìn)制形式來(lái)完成操作?？梢栽趩蝹€(gè)存儲(chǔ)過(guò)程中執(zhí)行一系列SQL語(yǔ)句，因而可以用于設(shè)計(jì)、封裝企業(yè)的功能模塊。應(yīng)用程序使用相同的存儲(chǔ)過(guò)程進(jìn)行操作，確保了在數(shù)據(jù)訪問(wèn)、操縱的一致性?？梢栽诋?dāng)前的存儲(chǔ)過(guò)程內(nèi)部引用其它存儲(chǔ)過(guò)程，這樣可以將復(fù)雜語(yǔ)句進(jìn)行簡(jiǎn)化。提供了一種數(shù)據(jù)庫(kù)訪問(wèn)的途徑，只需要用戶擁有執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，那么通過(guò)使用存儲(chǔ)過(guò)程就可以完成對(duì)數(shù)據(jù)庫(kù)的各種操作，如添加數(shù)據(jù)、修改數(shù)據(jù)、刪除數(shù)據(jù)等，而不需要考慮用戶是否擁有存儲(chǔ)過(guò)程所處理的數(shù)據(jù)對(duì)象的訪問(wèn)權(quán)限。3．使用存儲(chǔ)過(guò)程的注意事項(xiàng)在命名自定義存儲(chǔ)過(guò)程時(shí)避免與系統(tǒng)存儲(chǔ)過(guò)程名相同。存儲(chǔ)過(guò)程最多能夠支持32層的嵌套。命名存儲(chǔ)過(guò)程中的標(biāo)識(shí)符時(shí)，長(zhǎng)度不能超過(guò)128個(gè)字符。存儲(chǔ)過(guò)程中參數(shù)的個(gè)數(shù)不能超過(guò)2100。8.1.2創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程

在SQLServer中，既可以通過(guò)企業(yè)管理器，也可以通過(guò)使用CREATEPROCEDRUE語(yǔ)句的方式來(lái)創(chuàng)建存儲(chǔ)過(guò)程。

1．使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程

步驟：

①在企業(yè)管理器中，展開(kāi)服務(wù)器組、服務(wù)器節(jié)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)點(diǎn)。

②選擇某一個(gè)數(shù)據(jù)庫(kù)并在該節(jié)點(diǎn)上點(diǎn)擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲(chǔ)過(guò)程】級(jí)聯(lián)菜單，將彈出如圖8-1所示的對(duì)話框。圖8-1新建存儲(chǔ)過(guò)程對(duì)話框③在【存儲(chǔ)過(guò)程屬性】對(duì)話框中的【文本】編輯框中，對(duì)[OWNER]以及[PROCEDURENAME]部分進(jìn)行修改，分別修改為該存儲(chǔ)過(guò)程的所有者和存儲(chǔ)過(guò)程名稱，并且輸入該存儲(chǔ)過(guò)程所包含的SQL語(yǔ)句。如圖8-2所示為創(chuàng)建了一個(gè)用于查詢學(xué)生信息的存儲(chǔ)過(guò)程。圖8-2用于查詢學(xué)生信息的存儲(chǔ)過(guò)程④如果需要驗(yàn)證存儲(chǔ)過(guò)程中的SQL語(yǔ)句的正確性，可以單擊【檢查語(yǔ)法】按鈕，如果沒(méi)有語(yǔ)法錯(cuò)誤則會(huì)彈出如圖8-3所示的“語(yǔ)法檢查成功”的對(duì)話框。圖8-3“語(yǔ)法檢查成功”的提示對(duì)話框⑤當(dāng)存儲(chǔ)過(guò)程中沒(méi)有語(yǔ)法錯(cuò)誤后，可以單擊【確定】按鈕來(lái)保存自定義的存儲(chǔ)過(guò)程。2．使用CREATEPROCEDURE語(yǔ)句來(lái)創(chuàng)建存儲(chǔ)過(guò)程

該語(yǔ)句可以創(chuàng)建永久使用的存儲(chǔ)過(guò)程，也可以創(chuàng)建局部臨時(shí)過(guò)程、全局臨時(shí)過(guò)程。

語(yǔ)法格式為：

CREATEPROCEDURE存儲(chǔ)過(guò)程名稱[;數(shù)值]

[{@參數(shù)數(shù)據(jù)類型}

[VARYING][=參數(shù)的默認(rèn)值][OUTPUT][,…n]

[WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASSQL語(yǔ)句[...n]]其中：存儲(chǔ)過(guò)程名稱必須符合標(biāo)識(shí)符命名規(guī)則，而且對(duì)于數(shù)據(jù)庫(kù)以及所有者命名必須唯一。一個(gè)完整的命名不能超過(guò)128個(gè)字符?！纠?-1】使用CREATEPROCEDURE語(yǔ)句創(chuàng)建一個(gè)名稱為proc_bjrs的存儲(chǔ)過(guò)程，用于檢索現(xiàn)有班級(jí)及人數(shù)。

CREATEPROCEDUREproc_bjrsAS

SELECTDISTINCT(班級(jí)表.班級(jí)編號(hào)),班級(jí)表.班級(jí)名稱，人數(shù)=COUNT(學(xué)生基本信息表.學(xué)號(hào))

FROM學(xué)生基本信息表,班級(jí)表

WHERE班級(jí)表.班級(jí)編號(hào)=學(xué)生基本信息表.班級(jí)編號(hào)

GROUPBY班級(jí)表.班級(jí)編號(hào),班級(jí)表.班級(jí)名稱

GO

在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲(chǔ)過(guò)程EXECUTEproc_bjrs，運(yùn)行結(jié)果如圖8-4所示。圖8-4檢檢索現(xiàn)有班班級(jí)以及人數(shù)數(shù)【例8-2】設(shè)計(jì)一個(gè)帶帶有參數(shù)的存存儲(chǔ)過(guò)程，該該參數(shù)用于傳傳遞班級(jí)編號(hào)號(hào)，根據(jù)該參參數(shù)在學(xué)生基基本信息表中中檢索出某一一個(gè)班級(jí)中所所有學(xué)生的信信息。CREATEPROCEDUREproc_bjcx@bjbhVARCHAR(8)ASSELECT*FROM學(xué)生基本本信息表WHERE班班級(jí)編號(hào)=@bjbh

GO其其中參數(shù)@bjbh用于于接收班級(jí)編編號(hào)，在實(shí)際際調(diào)用中根據(jù)據(jù)該參數(shù)將會(huì)會(huì)返回一個(gè)結(jié)結(jié)果集。例如如，EXECUTEproc_bjcx'20051003'，將會(huì)顯示示班級(jí)編號(hào)為為20051003的學(xué)學(xué)生信息。運(yùn)運(yùn)行結(jié)果如圖圖8-5所示示圖8-5用用于傳遞班班級(jí)編號(hào)的存存儲(chǔ)過(guò)程修修改與刪刪除存儲(chǔ)過(guò)程程在SQLServer中，可以通通過(guò)企業(yè)管理理器或SQL語(yǔ)句兩種方方式修改或刪刪除存儲(chǔ)過(guò)程程。1．使用企業(yè)業(yè)管理器修改改存儲(chǔ)過(guò)程①在企業(yè)管理理器中，展開(kāi)開(kāi)服務(wù)器組、、服務(wù)器以及及數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)。

②選擇擇某一個(gè)數(shù)據(jù)據(jù)庫(kù)，展開(kāi)該該數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)后點(diǎn)擊【存存儲(chǔ)過(guò)程】，，在右側(cè)的窗窗口中將會(huì)顯顯示出存儲(chǔ)過(guò)過(guò)程的列表。。右擊需要修修改的存儲(chǔ)過(guò)過(guò)程，在彈出出菜單中選擇擇【屬性】，，彈出存儲(chǔ)過(guò)過(guò)程屬性對(duì)話話框，如圖8-6所示。。圖8-6【【存儲(chǔ)過(guò)程程屬性】對(duì)話話框③在【存儲(chǔ)過(guò)過(guò)程屬性】對(duì)對(duì)話框的【文文本】編輯框框中修改包含含的SQL語(yǔ)語(yǔ)句。需要注注意的是，存存儲(chǔ)過(guò)程的名名稱不能修改改。

④在檢檢查了語(yǔ)法的的正確性之后后，可以單擊擊【確定】按按鈕保存修改改并關(guān)閉對(duì)話話框。2．使用企業(yè)業(yè)管理器重命命名存儲(chǔ)過(guò)程程①在企業(yè)管理理器中，展開(kāi)開(kāi)服務(wù)器組、、服務(wù)器以及及數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)。

②選擇擇某一個(gè)數(shù)據(jù)據(jù)庫(kù)，展開(kāi)該該數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)后點(diǎn)擊【存存儲(chǔ)過(guò)程】，，在右側(cè)的窗窗口中將會(huì)顯顯示出存儲(chǔ)過(guò)過(guò)程的列表。。右擊需要重重命名的存儲(chǔ)儲(chǔ)過(guò)程，并選選擇彈出菜單單中的【重命命名】命令，，如圖8-7所示。圖8-7彈彈出菜單中中的【重命名名】命令③重新輸入存存儲(chǔ)過(guò)程的名名稱后，按下下回車鍵完成成修改。3．使用ALTERPROCEDURE語(yǔ)句句修改存儲(chǔ)過(guò)過(guò)程ALTERPROCEDURE語(yǔ)語(yǔ)句可以對(duì)數(shù)數(shù)據(jù)庫(kù)中已有有的存儲(chǔ)過(guò)程程進(jìn)行修該，，但不會(huì)更改改權(quán)限，也不不影響相關(guān)的的存儲(chǔ)過(guò)程或或觸發(fā)器。該該語(yǔ)句的語(yǔ)法法格式為：ALTERPROCEDURE存儲(chǔ)過(guò)過(guò)程名稱[;數(shù)值][{@參數(shù)數(shù)數(shù)據(jù)類型型}[VARYING][=參數(shù)的的默認(rèn)值][OUTPUT][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASSQL語(yǔ)句[...n]]

其中中，存儲(chǔ)過(guò)程程名稱為數(shù)據(jù)據(jù)庫(kù)中已經(jīng)存存在的過(guò)程名名，語(yǔ)法中的的常用選項(xiàng)與與CREATEPROCEDURE語(yǔ)句一致致?！纠?-3】修改已經(jīng)創(chuàng)創(chuàng)建的存儲(chǔ)過(guò)過(guò)程proc_bjcx及其功能，，將其修改為為可以根據(jù)系系部編號(hào)來(lái)查查詢某一個(gè)系系部中所有學(xué)學(xué)生的信息。。修改后將存存儲(chǔ)過(guò)程名稱稱重命名為proc_xbcx。在在查詢分析析器中運(yùn)行如如下命令，即即可完成重命命名：

ALTERPROCEDUREproc_bjcx@xbbhVARCHAR(2)

ASSELECT*FROM學(xué)學(xué)生基本信息息表WHERE班級(jí)編編號(hào)IN(SELECT班級(jí)編編號(hào)FROM班級(jí)表表WHERE系部編編號(hào)=@xbbh)GOSP_RENAME'proc_bjcx','proc_xbcx'

GO使用SP_RENAME可以重新命命名一個(gè)現(xiàn)有有的存儲(chǔ)過(guò)程程。SP_RENAME的語(yǔ)法格式式為：

SP_RENAME原原名稱,新新名稱。完完成修改后后查詢系部編編號(hào)為’01’的學(xué)生信信息。

EXECproc_xbcx'01'4．使用企業(yè)業(yè)管理器刪除除存儲(chǔ)過(guò)程①在企業(yè)管理理器中，展開(kāi)開(kāi)服務(wù)器組、、服務(wù)器以及及數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)。

②選擇擇某一個(gè)數(shù)據(jù)據(jù)庫(kù)，展開(kāi)該該數(shù)據(jù)庫(kù)后點(diǎn)點(diǎn)擊【存儲(chǔ)過(guò)過(guò)程】，在右右側(cè)的窗口中中顯示出存儲(chǔ)儲(chǔ)過(guò)程的列表表。右擊需要要?jiǎng)h除的存儲(chǔ)儲(chǔ)過(guò)程，并在在彈出菜單中中選擇【刪除除】命令，將將彈出【除去去對(duì)象】對(duì)話話框，如圖8-8所示。。

③在【除除去對(duì)象】對(duì)對(duì)話框中單擊擊【全部除去去】按鈕將會(huì)會(huì)完成對(duì)存儲(chǔ)儲(chǔ)過(guò)程的刪除除操作。圖8-8【【除去對(duì)象象】對(duì)話框5．使用DROPPROCEDURE語(yǔ)句刪刪除存儲(chǔ)過(guò)程程使用DROPPROCEDURE語(yǔ)句可以從從當(dāng)前的數(shù)據(jù)據(jù)庫(kù)中刪除一一個(gè)或多個(gè)存存儲(chǔ)過(guò)程。DROPPROCEDURE語(yǔ)句句的語(yǔ)法格式式為：

DROPPROCEDURE{存存儲(chǔ)過(guò)程名名稱}[,...n]

【例8-4】同時(shí)刪刪除當(dāng)前數(shù)據(jù)據(jù)庫(kù)中的兩個(gè)個(gè)存儲(chǔ)過(guò)程，，proc_a和proc_b。在在查詢分析析器中運(yùn)行如如下命令，即即可完成刪除除操作：DROPPROCEDUREproc_a,proc_bGO8.2觸觸發(fā)器觸觸發(fā)器及及其作用1．觸發(fā)器簡(jiǎn)簡(jiǎn)介觸發(fā)器屬于一一種特殊的存存儲(chǔ)過(guò)程，可可以在其中包包含復(fù)雜的SQL語(yǔ)句。。觸發(fā)器與存存儲(chǔ)過(guò)程的區(qū)區(qū)別在于觸發(fā)發(fā)器能夠自動(dòng)動(dòng)執(zhí)行并且不不含有參數(shù)。。通常可以在在觸發(fā)器內(nèi)編編寫(xiě)一段自動(dòng)動(dòng)執(zhí)行的程序序，用于保證證數(shù)據(jù)操作的的完整性，從從而擴(kuò)展了對(duì)對(duì)默認(rèn)值、約約束和規(guī)則的的完整性檢查查。對(duì)表進(jìn)行行包括添加數(shù)數(shù)據(jù)、刪除數(shù)數(shù)據(jù)、更新數(shù)數(shù)據(jù)中的一種種或多種操作作時(shí)，觸發(fā)器器就會(huì)自動(dòng)執(zhí)執(zhí)行。觸觸發(fā)器可可以劃分為三三種類別：INSERT觸發(fā)器、DELETE觸發(fā)器、UPDATE觸發(fā)器。這這三種觸發(fā)器器分別在發(fā)生生數(shù)據(jù)的添加加、刪除、修修改行為時(shí)自自動(dòng)執(zhí)行。2．觸發(fā)器的的優(yōu)點(diǎn)觸發(fā)器有助于于在添加、更更新或刪除表表中的記錄時(shí)時(shí)保留表之間間已定義的關(guān)關(guān)系。觸發(fā)器可以自自動(dòng)調(diào)用，當(dāng)當(dāng)發(fā)生了對(duì)數(shù)數(shù)據(jù)所作的任任何修改時(shí)，，與之相關(guān)的的觸發(fā)器就會(huì)會(huì)立刻被激活活?？梢酝瓿蓴?shù)據(jù)據(jù)庫(kù)中相關(guān)表表之間的級(jí)聯(lián)聯(lián)修改。級(jí)聯(lián)聯(lián)修改是指為為了保證數(shù)據(jù)據(jù)之間的邏輯輯性以及依賴賴關(guān)系，在對(duì)對(duì)一張表進(jìn)行行修改的同時(shí)時(shí)，其他表中中需要進(jìn)行的的修改能夠自自動(dòng)實(shí)現(xiàn)。觸發(fā)器可以對(duì)對(duì)需要存儲(chǔ)的的數(shù)據(jù)加以限限制，并且能能夠?qū)崿F(xiàn)比CHECK約約束更為復(fù)雜雜的功能。在在CHECK約束中不允允許引用其他他表中的列，，而觸發(fā)器可可以引用。3．觸發(fā)方式式觸發(fā)器的觸發(fā)發(fā)方式可以分分為后觸發(fā)和和替代觸發(fā)兩兩種方式。后后觸發(fā)：指指能夠引發(fā)觸觸發(fā)器的修改改操作在完成成之后才執(zhí)行行觸發(fā)器的行行為方式。創(chuàng)創(chuàng)建該類觸發(fā)發(fā)器，需要使使用AFTER關(guān)鍵字或或者FOR關(guān)關(guān)鍵字。替代觸發(fā)：指指定執(zhí)行一個(gè)個(gè)觸發(fā)器，而而不是執(zhí)行SQL語(yǔ)句，，這種替代觸觸發(fā)語(yǔ)句的方方式稱為替代代觸發(fā)方式。。創(chuàng)創(chuàng)建觸發(fā)發(fā)器可以使用企業(yè)業(yè)管理器以及及CREATETRIGGER語(yǔ)語(yǔ)句兩種方式式來(lái)創(chuàng)建觸發(fā)發(fā)器。1．使用企業(yè)業(yè)管理器創(chuàng)建建觸發(fā)器①打開(kāi)企業(yè)管管理器，在控控制臺(tái)根目錄錄下依次展開(kāi)開(kāi)服務(wù)器組、、服務(wù)器節(jié)點(diǎn)點(diǎn)、數(shù)據(jù)庫(kù)節(jié)節(jié)點(diǎn)。

②選選擇某一個(gè)數(shù)數(shù)據(jù)庫(kù)，并雙雙擊【表】節(jié)節(jié)點(diǎn)，在右側(cè)側(cè)窗口的列表表中選擇其中中的一張表。。

③右擊所所選中的表，，在彈出的下下拉菜單中選選擇【所有任任務(wù)】，并繼繼續(xù)選擇級(jí)聯(lián)聯(lián)菜單中的【【管理觸發(fā)器器】命令，將將打開(kāi)圖8-9所示的【【觸發(fā)器屬性性】對(duì)話框。。圖8-9【【觸發(fā)器屬屬性】對(duì)話框框④在【觸發(fā)器器屬性】對(duì)話話框的文本框框中，將[TRIGGERNAME]修改為為新建觸發(fā)器器的名稱，并并輸入觸發(fā)器器所包含的SQL語(yǔ)句。。

⑤通過(guò)點(diǎn)點(diǎn)擊【檢查語(yǔ)語(yǔ)法】按鈕來(lái)來(lái)判斷觸發(fā)器器的語(yǔ)法是否否正確，最后后，點(diǎn)擊【確確定】按鈕保保存新建的觸觸發(fā)器。2．使用CREATETRIGGER語(yǔ)句創(chuàng)創(chuàng)建觸發(fā)器CREATETRIGGER語(yǔ)句句的部分語(yǔ)法法格式：CREATETRIGGER觸觸發(fā)器名稱ON{表表名|視視圖名}

[WITHENCRYPTION]FOR|AFTER|INSTEADOF[DELETE][,][INSERT][,][UPDATE]

ASSQL語(yǔ)句[...n]其中：觸發(fā)器名稱必必須符合合標(biāo)識(shí)符命名名規(guī)則，并且且在同一個(gè)數(shù)數(shù)據(jù)庫(kù)中觸發(fā)發(fā)器的名稱不不允許重復(fù)。。觸發(fā)器名稱稱中可以省略略所有者名稱稱。表名或視圖名名稱是指指建立觸發(fā)器器所依賴的對(duì)對(duì)象。也可以以稱為觸發(fā)器器表或觸發(fā)器器視圖。WITHENCRYPTION該該選項(xiàng)的的作用為對(duì)觸觸發(fā)器中所包包含的SQL語(yǔ)句進(jìn)行加加密。FOR或AFTER代代表后觸發(fā)發(fā)方式，即當(dāng)當(dāng)滿足所有的的引用級(jí)聯(lián)操操作和約束檢檢查后并且完完成了SQL語(yǔ)句中指指定的所有操操作后，指定定的觸發(fā)器才才會(huì)被執(zhí)行。。在視圖上不不允許采用后后觸發(fā)方式。。INSTEADOF表表示替代代觸發(fā)方式，，每一個(gè)INSERT、、UPDATE、DELETE語(yǔ)語(yǔ)句只能定義義一個(gè)INSTEADOF觸觸發(fā)器。DELETE、INSERT、UPDATE三三個(gè)選項(xiàng)項(xiàng)中應(yīng)該至少少選擇其一，，用于表示在在表或視圖上上執(zhí)行了哪一一類的操作時(shí)時(shí)會(huì)將觸發(fā)器器激活。SQL語(yǔ)句為為觸發(fā)器器中包含的條條件以及需要要執(zhí)行的操作作。在執(zhí)行觸發(fā)器器時(shí)，系統(tǒng)會(huì)會(huì)自動(dòng)創(chuàng)建兩兩張臨時(shí)表INSERTED、DELETED，這兩張表表的結(jié)構(gòu)與觸觸發(fā)器所依賴賴的表類似，，用于保存在在用戶操作過(guò)過(guò)程中被插入入或被刪除的的數(shù)據(jù)。每一一個(gè)觸發(fā)器在在執(zhí)行過(guò)程中中都會(huì)產(chǎn)生與與之相關(guān)的上上述兩張臨時(shí)時(shí)表，并且在在執(zhí)行結(jié)束后后，兩個(gè)臨時(shí)時(shí)表會(huì)自動(dòng)被被系統(tǒng)刪除。。對(duì)臨時(shí)表可可以進(jìn)行查詢?cè)儾僮鳎鏢ELECT*FROMDELETED，但是不能能對(duì)臨時(shí)表進(jìn)進(jìn)行修改【例8-5】在數(shù)據(jù)庫(kù)XSCJ中設(shè)設(shè)計(jì)一個(gè)觸發(fā)發(fā)器，該觸發(fā)發(fā)器的作用為為：當(dāng)在班級(jí)級(jí)表中刪除某某一個(gè)班級(jí)時(shí)時(shí)，在學(xué)生基基本信息表中中該班級(jí)所包包含的學(xué)生信信息也全部被被刪除。提提示：在此例例中，由于涉涉及到了班級(jí)級(jí)表的刪除操操作，因而需需要設(shè)計(jì)一個(gè)個(gè)DELETE類型的觸觸發(fā)器。在在查詢分析器器中運(yùn)行如下下命令：USEXSCJ

GO

CREATETRIGGERdel_bjON班級(jí)表AFTERDELETE

ASDELETEFROM學(xué)生基本本信息表WHERE班班級(jí)編號(hào)IN(SELECT班班級(jí)編號(hào)FROMDELETED)

GO

運(yùn)行結(jié)果果如圖8-10所示。圖8-10一一個(gè)DELETE類類型的觸發(fā)器器【例8-6】在數(shù)據(jù)庫(kù)XSCJ中設(shè)設(shè)計(jì)一個(gè)觸發(fā)發(fā)器，該觸發(fā)發(fā)器能夠保證證在學(xué)生基本本信息表中添添加新的紀(jì)錄錄時(shí)，新學(xué)生生的班級(jí)編號(hào)號(hào)必須已經(jīng)存存在于班級(jí)表表中。提示：設(shè)計(jì)計(jì)該觸發(fā)器器有助于實(shí)實(shí)現(xiàn)學(xué)生信信息的完整整性。在此此例中由于于涉及到了了學(xué)生基本本信息表中中的添加操操作，因而而需要設(shè)計(jì)計(jì)一個(gè)INSERT類型的觸觸發(fā)器。在在查詢分分析器中運(yùn)運(yùn)行如下命命令：USEXSCJGOCREATETRIGGERinsert_xsON學(xué)學(xué)生基本信信息表AFTERINSERTASIFEXISTS(SELECT*FROMINSERTEDWHERE班級(jí)級(jí)編號(hào)IN(SELECT班級(jí)級(jí)編號(hào)FROM班班級(jí)表))PRINT'添加成功功！'ELSEBEGINPRINT'班級(jí)級(jí)編號(hào)與現(xiàn)現(xiàn)有的班級(jí)級(jí)不符！'ROLLBACKTRANSACTIONEND運(yùn)行結(jié)果如如圖8-11所示。。圖8-11一個(gè)個(gè)INSERT類型型的觸發(fā)器器創(chuàng)建了觸發(fā)發(fā)器insert_xs之后后，我們可可以添加新新的學(xué)生紀(jì)紀(jì)錄進(jìn)行測(cè)測(cè)試，例如如：

INSERTINTO學(xué)生生基本信息息表(學(xué)學(xué)號(hào),姓名名,性別,族別,班班級(jí)編號(hào))VALUES('000108','王松松濤','男','漢','20081001')由由于“班級(jí)級(jí)表”中不不存在編號(hào)號(hào)為20081001的班級(jí)級(jí)，因而添添加操作將將會(huì)被取消消。ROLLBACKTRANSACTION用于回回滾已經(jīng)完完成的操作作。運(yùn)行結(jié)結(jié)果如圖8-12所所示。圖8-12添加加操作被取取消8.2.4修改改與刪除觸觸發(fā)器1．使用企企業(yè)管理器器修改觸發(fā)發(fā)器①在控制臺(tái)臺(tái)根目錄下下依次展開(kāi)開(kāi)服務(wù)器組組、服務(wù)器器節(jié)點(diǎn)、數(shù)數(shù)據(jù)庫(kù)節(jié)點(diǎn)點(diǎn)。

②選選擇某一個(gè)個(gè)數(shù)據(jù)庫(kù)，，并雙擊【【表】節(jié)點(diǎn)點(diǎn)，在右側(cè)側(cè)窗口的列列表中選擇擇某一張需需要對(duì)其所所建立的觸觸發(fā)器進(jìn)行行修該的表表。

③右右擊所選中中的表，在在彈出的下下拉菜單中中選擇【所所有任務(wù)】】，并繼續(xù)續(xù)選擇級(jí)聯(lián)聯(lián)菜單中的的【管理觸觸發(fā)器】命命令，將打打開(kāi)【觸發(fā)發(fā)器】屬性性對(duì)話框，，如圖8-13所示示。

④在在【觸發(fā)器器屬性】對(duì)對(duì)話框中，，在【名稱稱】下拉列列表框選擇擇一個(gè)已有有的觸發(fā)器器，并在【【文本】編編輯框中對(duì)對(duì)其所包含含的SQL語(yǔ)句進(jìn)行行修改。圖8-13【觸發(fā)發(fā)器】屬性性對(duì)話框⑤在語(yǔ)法檢檢查無(wú)誤的的情況下，，點(diǎn)擊【確確定】按鈕鈕保存修改改。

⑥如如果需要?jiǎng)h刪除當(dāng)前觸觸發(fā)器，可可以點(diǎn)擊【【刪除】按按鈕。2．使用ALTERTRIGGER語(yǔ)句修改改觸發(fā)器ALTERTRIGGER語(yǔ)句與與CREATETRIGGER語(yǔ)句句的語(yǔ)法格格式及其參參數(shù)類似，，部分語(yǔ)法法格式為：：

ALTERTRIGGER觸觸發(fā)器名稱稱ON{表表名|視圖名名}

[WITHENCRYPTION]FOR|AFTER|INSTEADOF[DELETE][,][INSERT][,][UPDATE]

ASSQL語(yǔ)語(yǔ)句[...n]【例8-7】修改del_bj觸發(fā)器，，使得在班班級(jí)表中刪刪除某一個(gè)個(gè)班級(jí)時(shí)，，不僅在學(xué)學(xué)生基本信信息表中該該班級(jí)所包包含的學(xué)生生信息將被被刪除，而而且在成績(jī)績(jī)表中與學(xué)學(xué)生相關(guān)的的數(shù)據(jù)也將將被刪除。。

在查詢?cè)兎治銎髦兄羞\(yùn)行如下下命令：ALTERTRIGGERdel_bjON班班級(jí)表AFTERDELETE

ASDELETEFROM成成績(jī)表WHERE學(xué)號(hào)IN(SELECT學(xué)號(hào)號(hào)FROM學(xué)生生基本信息息表WHERE班班級(jí)編號(hào)號(hào)IN(SELECT班級(jí)級(jí)編號(hào)FROMDELETED))DELETEFROM學(xué)生基基本信息表表WHERE班班級(jí)編號(hào)IN(SELECT班級(jí)級(jí)編號(hào)FROMDELETED)

運(yùn)行結(jié)結(jié)果如圖8-14所所示。圖8-14修修改del_bj觸觸發(fā)器3．使用DROPTRIGGER語(yǔ)語(yǔ)句刪除觸觸發(fā)器移除觸發(fā)器器時(shí)，與觸觸發(fā)器有關(guān)關(guān)的信息將將從sysobjects和和syscomments系系統(tǒng)表中刪刪除。可以以通過(guò)刪除除觸發(fā)器或或刪除表兩兩種方式移移除觸發(fā)器器。刪除表表時(shí)，將除除去所有與與表相關(guān)聯(lián)聯(lián)的觸發(fā)器器。DROPTRIGGER語(yǔ)語(yǔ)句的語(yǔ)法法格式為：：

DROPTRIGGER觸發(fā)發(fā)器名稱[,...n]

觸發(fā)器器名稱為數(shù)數(shù)據(jù)庫(kù)中現(xiàn)現(xiàn)有的觸發(fā)發(fā)器。可以以指定一個(gè)個(gè)或多個(gè)觸觸發(fā)器名稱稱來(lái)刪除一一個(gè)或多個(gè)個(gè)觸發(fā)器。?！纠?-8】刪除觸觸發(fā)器del_bj以及insert_xs。。

在查詢?cè)兎治銎髦兄羞\(yùn)行如下下命令：USEXSCJ

GODROPTRIGGERdel_bj,insert_xs

運(yùn)行結(jié)結(jié)果如圖8-15所所示。圖8-15刪除除觸發(fā)器del_bj以及insert_xs本章小結(jié)本章學(xué)習(xí)了了存儲(chǔ)過(guò)程程與觸發(fā)器器的相關(guān)知知識(shí)，存儲(chǔ)儲(chǔ)過(guò)程中可可以將多條條SQL語(yǔ)語(yǔ)句集中在在一起完成成復(fù)雜的功功能，從而而用戶可以以在不具有有對(duì)數(shù)據(jù)對(duì)對(duì)象訪問(wèn)的的權(quán)限下調(diào)調(diào)用這些存存儲(chǔ)過(guò)程完完成的特定定的操作。。觸發(fā)器可可以幫助用用戶完成數(shù)數(shù)據(jù)的驗(yàn)證證功能，從從而能夠保保證數(shù)據(jù)的的一致性、、完整性。。練習(xí)與上機(jī)機(jī)一．選擇題題1．下面關(guān)關(guān)于存儲(chǔ)過(guò)過(guò)程的描述述中哪些是是正確的（（）A．自定義義存儲(chǔ)過(guò)程程與系統(tǒng)存存儲(chǔ)過(guò)程名名稱可以相相同

B．．存儲(chǔ)過(guò)程程最多能夠夠支持64層的嵌套套

C．命命名存儲(chǔ)過(guò)過(guò)程中的標(biāo)標(biāo)識(shí)符時(shí)，，長(zhǎng)度不能能超過(guò)256個(gè)字符符

D．存存儲(chǔ)過(guò)程中中參數(shù)的個(gè)個(gè)數(shù)不能超超過(guò)2100

2．．用于創(chuàng)建建存儲(chǔ)過(guò)程程的SQL語(yǔ)句為（（）A．CREATEDATABASEB．CREATETRIGGER

C．．CREATEPROCEDURED．CREATETABLE3．用于修修改存儲(chǔ)過(guò)過(guò)程的SQL語(yǔ)句為為（）A．ALTERTABLEB．ALTERDATABASEC．ALTERTRIGGERD．ALTERPROCEDURE4．下列對(duì)對(duì)觸發(fā)器的的描述中哪哪一個(gè)是錯(cuò)錯(cuò)誤的（））

A．．觸發(fā)器屬屬于一種特特殊的存儲(chǔ)儲(chǔ)過(guò)程B．觸發(fā)器器與存儲(chǔ)過(guò)過(guò)程的區(qū)別別在于觸發(fā)發(fā)器能夠自自動(dòng)執(zhí)行并并且不含有有參數(shù)C．觸發(fā)器器有助于在在添加、更更新或刪除除表中的記記錄時(shí)保留留表之間已已定義的關(guān)關(guān)系

D．．既可以對(duì)對(duì)INSERTED、DELETED臨時(shí)表進(jìn)進(jìn)行查詢，，也可以進(jìn)進(jìn)行修改二．填空題題1．一個(gè)存存儲(chǔ)過(guò)程的的名稱不能能超過(guò)_______個(gè)字符符。

2．．使用_______語(yǔ)句可可以對(duì)存儲(chǔ)儲(chǔ)過(guò)程進(jìn)行行重命名。。

3．觸觸發(fā)器有_______、_______觸發(fā)發(fā)方式。4．用_________語(yǔ)句可以以刪除觸發(fā)發(fā)器。5．觸發(fā)器器可以劃分分為三種類類別____________、____________、____________。。三．簡(jiǎn)答題題1．簡(jiǎn)述什什么是存儲(chǔ)儲(chǔ)過(guò)程？2．簡(jiǎn)要要說(shuō)明存儲(chǔ)儲(chǔ)過(guò)程的語(yǔ)語(yǔ)法格式？？

3．簡(jiǎn)簡(jiǎn)述什么是是觸發(fā)器？？

4．簡(jiǎn)簡(jiǎn)要說(shuō)明觸觸發(fā)器的語(yǔ)語(yǔ)法格式？？項(xiàng)目實(shí)訓(xùn)1．創(chuàng)建一一個(gè)存儲(chǔ)過(guò)過(guò)程，該存存儲(chǔ)過(guò)程能能夠?qū)崿F(xiàn)根根據(jù)系部的的編號(hào)查詢?cè)兂鱿挡恐兄心猩⑴娜藬?shù)數(shù)。

2．．創(chuàng)建一個(gè)個(gè)INSERT觸發(fā)發(fā)器，該觸觸發(fā)器能夠夠在向成績(jī)績(jī)表中添加加數(shù)據(jù)時(shí)，，自動(dòng)判斷斷學(xué)號(hào)、課課程編號(hào)、、成績(jī)是否否合法，如如果非法則則對(duì)插入操操作進(jìn)行回回滾。3.修改存存儲(chǔ)過(guò)程的的名字。4．建立立一個(gè)存儲(chǔ)儲(chǔ)過(guò)程，來(lái)來(lái)修改student數(shù)據(jù)庫(kù)庫(kù)的c、sc兩張表表，當(dāng)用戶戶修改c表表中的cno后，修修改cno表中對(duì)應(yīng)應(yīng)的數(shù)據(jù)。。9、靜夜四無(wú)鄰鄰，荒居舊業(yè)業(yè)貧。。12月-2212月-22Friday,December23,202210、雨中黃葉葉樹(shù)，燈下下白頭人。。。23:43:2423:43:2423:4312/23/202211:43:24PM11、以我獨(dú)沈沈久，愧君君相見(jiàn)頻。。。12月-2223:43:2423:43Dec-2223-Dec-2212、故故人人江江海海別別，，幾幾度度隔隔山山川川。。。。23:43:2423:43:2423:43Friday,December23,202213、乍乍見(jiàn)見(jiàn)翻翻疑疑夢(mèng)夢(mèng)，，相相悲悲各各問(wèn)問(wèn)年年。。。。12月月-2212月月-2223:43:2423:43:24December23,202214、他鄉(xiāng)鄉(xiāng)生白白發(fā)，，舊國(guó)國(guó)見(jiàn)青青山。。。23十十二二月202211:43:24下下午午23:43:2412月月-2215、比不不了得得就不不比，，得不不到的的就不不要。。。。十二月月2211:43下下午12月月-2223:43December23,202216、行動(dòng)出出成果，，工作出出財(cái)富。。。2022/12/2323:43:2423:43:2423December202217、做前，能夠夠環(huán)視四周；；做時(shí)，你只只能或者最好好沿著以腳為為起點(diǎn)的射線線向前。。11:43:24下午午11:43下下午23:43:2412月-229、沒(méi)有有失敗敗，只只有暫暫時(shí)停停止成成功?。?。12月月-2212月月-22Friday,December23,202210、很多多事情情努力力了未未必有有結(jié)果果，但但是不不努力力卻什什么改改變也也沒(méi)有有。。。23:43:2423:43:2423:4312/23/202211:43:24PM11、成功就是是日復(fù)一日日那一點(diǎn)點(diǎn)點(diǎn)小小努力力的積累。。。12月-2223:43:2423:43Dec-2223-Dec-2212、世間成事事，不求其其絕對(duì)圓滿