數(shù)據(jù)庫存儲(chǔ)過程與觸發(fā)器ppt課件

1、第第8 8章章 存儲(chǔ)過程與觸發(fā)器存儲(chǔ)過程與觸發(fā)器本章學(xué)習(xí)目的本章學(xué)習(xí)目的l 了解存儲(chǔ)過程和觸發(fā)器的作用了解存儲(chǔ)過程和觸發(fā)器的作用l 熟練創(chuàng)建和管理存儲(chǔ)過程及觸發(fā)熟練創(chuàng)建和管理存儲(chǔ)過程及觸發(fā)器器l 靈敏運(yùn)用存儲(chǔ)過程和觸發(fā)器提高靈敏運(yùn)用存儲(chǔ)過程和觸發(fā)器提高系統(tǒng)開發(fā)效率系統(tǒng)開發(fā)效率 8.1 存儲(chǔ)過程8.1.1 存儲(chǔ)過程根底知識(shí)1存儲(chǔ)過程簡介 存儲(chǔ)過程是存儲(chǔ)在效力器上的由SQL語句和控制流語句組成的一個(gè)預(yù)編譯集合。存儲(chǔ)過程劃分為系統(tǒng)存儲(chǔ)過程以及用戶存儲(chǔ)過程。一個(gè)存儲(chǔ)過程可以作為一個(gè)獨(dú)立的單元進(jìn)展處置。在SQL Server中可以經(jīng)過存儲(chǔ)過程來完成很多管理義務(wù)，利用一些常用的系統(tǒng)存儲(chǔ)過程可以很方便的查看

2、、操作數(shù)據(jù)庫對(duì)象。系統(tǒng)存儲(chǔ)過程的命名通常以sp_作為前綴，并且存儲(chǔ)于Master數(shù)據(jù)庫中，假設(shè)用戶擁有足夠的權(quán)限，就可以在任何數(shù)據(jù)庫中調(diào)用這些存儲(chǔ)過程。存儲(chǔ)過程提供了一種封裝某一個(gè)需求反復(fù)執(zhí)行義務(wù)的方法。一旦定義了一個(gè)存儲(chǔ)過程之后，在運(yùn)用程序中就可以對(duì)其進(jìn)展調(diào)用。在存儲(chǔ)過程中，不僅可以包含程序流、邏輯以及對(duì)數(shù)據(jù)庫的查詢，而且也可以接受參數(shù)、輸出參數(shù)、前往單個(gè)或多個(gè)結(jié)果集。 所以經(jīng)過設(shè)計(jì)自定義用戶存儲(chǔ)過程，用戶可以使其實(shí)現(xiàn)強(qiáng)大的編程功能。 運(yùn)用存儲(chǔ)過程可以比單獨(dú)的SQL語句完成更為復(fù)雜的功能，并且系統(tǒng)會(huì)對(duì)存儲(chǔ)過程中的SQL語句進(jìn)展了預(yù)編譯處置，使得執(zhí)行速度有了大幅度的提升。存儲(chǔ)過程被第一次調(diào)用后

3、，會(huì)保管在高速緩沖區(qū)中，這樣再次執(zhí)行同一個(gè)存儲(chǔ)過程時(shí)，會(huì)提高了反復(fù)調(diào)用的效率。在實(shí)踐運(yùn)用中，可以將復(fù)雜的商業(yè)規(guī)那么封裝在存儲(chǔ)過程中，從而提高程序語句的利用率。2存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程一旦執(zhí)行一次后，其執(zhí)行的方案就會(huì)駐留在計(jì)算機(jī)存儲(chǔ)過程一旦執(zhí)行一次后，其執(zhí)行的方案就會(huì)駐留在計(jì)算機(jī)的高速緩沖存儲(chǔ)器中。其后對(duì)同一個(gè)存儲(chǔ)過程的調(diào)用就可以的高速緩沖存儲(chǔ)器中。其后對(duì)同一個(gè)存儲(chǔ)過程的調(diào)用就可以直接利用編譯后在高速緩存中的二進(jìn)制方式來完成操作。直接利用編譯后在高速緩存中的二進(jìn)制方式來完成操作?？梢栽趩蝹€(gè)存儲(chǔ)過程中執(zhí)行一系列可以在單個(gè)存儲(chǔ)過程中執(zhí)行一系列SQL語句，因此可以用于語句，因此可以用于設(shè)

4、計(jì)、封裝企業(yè)的功能模塊。運(yùn)用程序運(yùn)用一樣的存儲(chǔ)過程設(shè)計(jì)、封裝企業(yè)的功能模塊。運(yùn)用程序運(yùn)用一樣的存儲(chǔ)過程進(jìn)展操作，確保了在數(shù)據(jù)訪問、支配的一致性。進(jìn)展操作，確保了在數(shù)據(jù)訪問、支配的一致性?？梢栽诋?dāng)前的存儲(chǔ)過程內(nèi)部援用其它存儲(chǔ)過程，這樣可以將復(fù)雜語句進(jìn)展簡化。提供了一種數(shù)據(jù)庫訪問的途徑，只需求用戶擁有執(zhí)行存儲(chǔ)過程的權(quán)限，那么經(jīng)過運(yùn)用存儲(chǔ)過程就可以完成對(duì)數(shù)據(jù)庫的各種操作，如添加數(shù)據(jù)、修正數(shù)據(jù)、刪除數(shù)據(jù)等，而不需求思索用戶能否擁有存儲(chǔ)過程所處置的數(shù)據(jù)對(duì)象的訪問權(quán)限。3運(yùn)用存儲(chǔ)過程的本卷須知運(yùn)用存儲(chǔ)過程的本卷須知在命名自定義存儲(chǔ)過程時(shí)防止與系統(tǒng)存儲(chǔ)過程名一樣。在命名自定義存儲(chǔ)過程時(shí)防止與系統(tǒng)存儲(chǔ)過程名一

5、樣。存儲(chǔ)過程最多可以支持存儲(chǔ)過程最多可以支持32層的嵌套。層的嵌套。命名存儲(chǔ)過程中的標(biāo)識(shí)符時(shí)，長度不能超越命名存儲(chǔ)過程中的標(biāo)識(shí)符時(shí)，長度不能超越128個(gè)字符。個(gè)字符。存儲(chǔ)過程中參數(shù)的個(gè)數(shù)不能超越存儲(chǔ)過程中參數(shù)的個(gè)數(shù)不能超越2100。8.1.2 創(chuàng)建和執(zhí)行存儲(chǔ)過程創(chuàng)建和執(zhí)行存儲(chǔ)過程在在SQL Server中，既可以經(jīng)過企業(yè)管理器，也可以經(jīng)過運(yùn)用中，既可以經(jīng)過企業(yè)管理器，也可以經(jīng)過運(yùn)用CREATE PROCEDRUE語句的方式來創(chuàng)建存儲(chǔ)過程。語句的方式來創(chuàng)建存儲(chǔ)過程。1運(yùn)用企業(yè)管理器創(chuàng)建存儲(chǔ)過程運(yùn)用企業(yè)管理器創(chuàng)建存儲(chǔ)過程步驟：步驟：在企業(yè)管理器中，展開效力器組、效力器節(jié)點(diǎn)、數(shù)據(jù)庫節(jié)在企業(yè)管理器中，

6、展開效力器組、效力器節(jié)點(diǎn)、數(shù)據(jù)庫節(jié)點(diǎn)。點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫并在該節(jié)點(diǎn)上點(diǎn)擊鼠標(biāo)右鍵，選擇【新選擇某一個(gè)數(shù)據(jù)庫并在該節(jié)點(diǎn)上點(diǎn)擊鼠標(biāo)右鍵，選擇【新建】菜單下的【存儲(chǔ)過程】級(jí)聯(lián)菜單，將彈出如圖建】菜單下的【存儲(chǔ)過程】級(jí)聯(lián)菜單，將彈出如圖8-1所示的所示的對(duì)話框。對(duì)話框。圖8-1新建存儲(chǔ)過程對(duì)話框在【存儲(chǔ)過程屬性】對(duì)話框中的【文本】編輯框中，對(duì)OWNER以及PROCEDURE NAME部分進(jìn)展修正，分別修正為該存儲(chǔ)過程的一切者和存儲(chǔ)過程稱號(hào)，并且輸入該存儲(chǔ)過程所包含的SQL語句。如圖8-2所示為創(chuàng)建了一個(gè)用于查詢學(xué)生信息的存儲(chǔ)過程。圖8-2用于查詢學(xué)生信息的存儲(chǔ)過程假設(shè)需求驗(yàn)證存儲(chǔ)過程中的SQL語句的

7、正確性，可以單擊【檢查語法】按鈕，假設(shè)沒有語法錯(cuò)誤那么會(huì)彈出如圖8-3所示的“語法檢查勝利的對(duì)話框。圖8-3“語法檢查勝利的提示對(duì)話框當(dāng)存儲(chǔ)過程中沒有語法錯(cuò)誤后，可以單擊【確定】按鈕來 保管自定義的存儲(chǔ)過程。2運(yùn)用運(yùn)用CREATE PROCEDURE語句來創(chuàng)建存儲(chǔ)過程語句來創(chuàng)建存儲(chǔ)過程該語句可以創(chuàng)建永久運(yùn)用的存儲(chǔ)過程，也可以創(chuàng)建部分暫時(shí)該語句可以創(chuàng)建永久運(yùn)用的存儲(chǔ)過程，也可以創(chuàng)建部分暫時(shí)過程、全局暫時(shí)過程。過程、全局暫時(shí)過程。語法格式為：語法格式為：CREATE PROCEDURE 存儲(chǔ)過程稱號(hào)存儲(chǔ)過程稱號(hào) ;數(shù)值數(shù)值參數(shù)參數(shù) 數(shù)據(jù)類型數(shù)據(jù)類型 VARYING =參數(shù)的默許值參數(shù)的默許值 OU

8、TPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS SQL語句語句 .n 其中：存儲(chǔ)過程稱號(hào) 必需符合標(biāo)識(shí)符命名規(guī)那么，而且對(duì)于數(shù)據(jù)庫以及一切者命名必需獨(dú)一。一個(gè)完好的命名不能超越128個(gè)字符?！纠?-1】運(yùn)用CREATE PROCEDURE語句創(chuàng)建一個(gè)稱號(hào)為proc_bjrs的存儲(chǔ)過程，用于檢索現(xiàn)有班級(jí)及人數(shù)。CREATE PROCEDURE proc_bjrs AS SELECT DISTINCT(班級(jí)表班級(jí)表.班級(jí)編號(hào)班級(jí)編號(hào)),班級(jí)表班級(jí)表.班級(jí)班級(jí) 稱號(hào)，稱號(hào)， 人數(shù)人數(shù)=CO

9、UNT(學(xué)生根本信息表學(xué)生根本信息表.學(xué)號(hào)學(xué)號(hào)) FROM 學(xué)生根本信息表學(xué)生根本信息表,班級(jí)表班級(jí)表 WHERE 班級(jí)表班級(jí)表.班級(jí)編號(hào)班級(jí)編號(hào)=學(xué)生根本信息表學(xué)生根本信息表.班級(jí)編班級(jí)編號(hào)號(hào) GROUP BY 班級(jí)表班級(jí)表.班級(jí)編號(hào)班級(jí)編號(hào),班級(jí)表班級(jí)表.班級(jí)稱號(hào)班級(jí)稱號(hào)GO在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲(chǔ)過在查詢分析器中執(zhí)行以上命令后，然后執(zhí)行該存儲(chǔ)過程程EXECUTE proc_bjrs，運(yùn)轉(zhuǎn)結(jié)果如圖，運(yùn)轉(zhuǎn)結(jié)果如圖8-4所示。所示。圖8-4 檢索現(xiàn)有班級(jí)以及人數(shù)【例8-2】設(shè)計(jì)一個(gè)帶有參數(shù)的存儲(chǔ)過程，該參數(shù)用于傳送班級(jí)編號(hào)，根據(jù)該參數(shù)在學(xué)生根本信息表中檢索出某一個(gè)班級(jí)中一切

10、學(xué)生的信息。CREATE PROCEDURE proc_bjcx bjbh VARCHAR(8)AS SELECT * FROM 學(xué)生根本信息表 WHERE 班級(jí)編號(hào)=bjbhGO其中參數(shù)bjbh用于接納班級(jí)編號(hào)，在實(shí)踐調(diào)用中根據(jù)該參數(shù)將會(huì)前往一個(gè)結(jié)果集。例如，EXECUTE proc_bjcx 20191003，將會(huì)顯示班級(jí)編號(hào)為20191003的學(xué)生信息。運(yùn)轉(zhuǎn)結(jié)果如圖8-5所示圖8-5 用于傳送班級(jí)編號(hào)的存儲(chǔ)過程8.1.3 修正與刪除存儲(chǔ)過程修正與刪除存儲(chǔ)過程在在SQL Server中，可以經(jīng)過企業(yè)管理器或中，可以經(jīng)過企業(yè)管理器或SQL語句兩種方式語句兩種方式修正或刪除存儲(chǔ)過程。修正或刪除

11、存儲(chǔ)過程。1運(yùn)用企業(yè)管理器修正存儲(chǔ)過程運(yùn)用企業(yè)管理器修正存儲(chǔ)過程在企業(yè)管理器中，展開效力器組、效力器以及數(shù)據(jù)庫節(jié)點(diǎn)。在企業(yè)管理器中，展開效力器組、效力器以及數(shù)據(jù)庫節(jié)點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過選擇某一個(gè)數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過程的列表。右擊需程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過程的列表。右擊需求修正的存儲(chǔ)過程，在彈出菜單中選擇【屬性】，彈出存儲(chǔ)求修正的存儲(chǔ)過程，在彈出菜單中選擇【屬性】，彈出存儲(chǔ)過程屬性對(duì)話框，如圖過程屬性對(duì)話框，如圖8-6所示。所示。圖8-6 【存儲(chǔ)過程屬性】對(duì)話框在【存儲(chǔ)過程屬性】對(duì)話框的【文本】編輯

12、框中修正包含的SQL語句。需求留意的是，存儲(chǔ)過程的稱號(hào)不能修正。在檢查了語法的正確性之后，可以單擊【確定】按鈕保管修正并封鎖對(duì)話框。2運(yùn)用企業(yè)管理器重命名存儲(chǔ)過程在企業(yè)管理器中，展開效力器組、效力器以及數(shù)據(jù)庫節(jié)點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫，展開該數(shù)據(jù)庫節(jié)點(diǎn)后點(diǎn)擊【存儲(chǔ)過程】，在右側(cè)的窗口中將會(huì)顯示出存儲(chǔ)過程的列表。右擊需求重命名的存儲(chǔ)過程，并選擇彈出菜單中的【重命名】命令，如圖8-7所示。圖8-7 彈出菜單中的【重命名】命令重新輸入存儲(chǔ)過程的稱號(hào)后，按下回車鍵完成修正。3運(yùn)用ALTER PROCEDURE語句修正存儲(chǔ)過程ALTER PROCEDURE語句可以對(duì)數(shù)據(jù)庫中已有的存儲(chǔ)過程進(jìn)展修該，但不會(huì)更改

13、權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。該語句的語法格式為：ALTER PROCEDURE 存儲(chǔ)過程稱號(hào) ;數(shù)值參數(shù) 數(shù)據(jù)類型 VARYING =參數(shù)的默許值 OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS SQL語句 .n 其中，存儲(chǔ)過程稱號(hào)為數(shù)據(jù)庫中曾經(jīng)存在的過程名，語法中的常用選項(xiàng)與CREATE PROCEDURE語句一致?！纠?-3】修正曾經(jīng)創(chuàng)建的存儲(chǔ)過程proc_bjcx及其功能，將其修正為可以根據(jù)系部編號(hào)來查詢某一個(gè)系部中一切學(xué)生的信息。修正后將存儲(chǔ)過程稱號(hào)重命名為pr

14、oc_xbcx。在查詢分析器中運(yùn)轉(zhuǎn)如下命令，即可完成重命名：ALTER PROCEDURE proc_bjcx xbbh VARCHAR(2)AS SELECT * FROM 學(xué)生根本信息表 WHERE 班級(jí)編號(hào) IN (SELECT 班級(jí)編號(hào) FROM 班級(jí)表 WHERE 系部編號(hào)=xbbh) GOSP_RENAME proc_bjcx , proc_xbcxGO運(yùn)用SP_RENAME可以重新命名一個(gè)現(xiàn)有的存儲(chǔ)過程。SP_RENAME的語法格式為：SP_RENAME 原稱號(hào) , 新稱號(hào)。完成修正后查詢系部編號(hào)為01的學(xué)生信息。EXEC proc_xbcx 014運(yùn)用企業(yè)管理器刪除存儲(chǔ)過程在企

15、業(yè)管理器中，展開效力器組、效力器以及數(shù)據(jù)庫節(jié)點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫，展開該數(shù)據(jù)庫后點(diǎn)擊【存儲(chǔ)過程】，在右側(cè)的窗口中顯示出存儲(chǔ)過程的列表。右擊需求刪除的存儲(chǔ)過程，并在彈出菜單中選擇【刪除】命令，將彈出【除去對(duì)象】對(duì)話框，如圖8-8所示。在【除去對(duì)象】對(duì)話框中單擊【全部除去】按鈕將會(huì)完成對(duì)存儲(chǔ)過程的刪除操作。圖8-8 【除去對(duì)象】對(duì)話框5運(yùn)用運(yùn)用DROP PROCEDURE語句刪除存儲(chǔ)過程語句刪除存儲(chǔ)過程運(yùn)用運(yùn)用DROP PROCEDURE語句可以從當(dāng)前的數(shù)據(jù)庫中刪除語句可以從當(dāng)前的數(shù)據(jù)庫中刪除一個(gè)或多個(gè)存儲(chǔ)過程。一個(gè)或多個(gè)存儲(chǔ)過程。DROP PROCEDURE語句的語法格語句的語法格式為：式為：D

16、ROP PROCEDURE 存儲(chǔ)過程稱號(hào)存儲(chǔ)過程稱號(hào) ,.n 【例【例8-4】同時(shí)刪除當(dāng)前數(shù)據(jù)庫中的兩個(gè)存儲(chǔ)過程，】同時(shí)刪除當(dāng)前數(shù)據(jù)庫中的兩個(gè)存儲(chǔ)過程，proc_a和和proc_b。在查詢分析器中運(yùn)轉(zhuǎn)如下命令，即可完成刪除操作：在查詢分析器中運(yùn)轉(zhuǎn)如下命令，即可完成刪除操作：DROP PROCEDURE proc_a , proc_bGO8.2 8.2 觸發(fā)器觸發(fā)器8.2.1 8.2.1 觸發(fā)器及其作用觸發(fā)器及其作用1 1觸發(fā)器簡介觸發(fā)器簡介 觸發(fā)器屬于一種特殊的存儲(chǔ)過程，觸發(fā)器屬于一種特殊的存儲(chǔ)過程，可以在其中包含復(fù)雜的可以在其中包含復(fù)雜的SQLSQL語句。觸發(fā)器與存語句。觸發(fā)器與存儲(chǔ)過程的區(qū)

17、別在于觸發(fā)器可以自動(dòng)執(zhí)行并且儲(chǔ)過程的區(qū)別在于觸發(fā)器可以自動(dòng)執(zhí)行并且不含有參數(shù)。通?？梢栽谟|發(fā)器內(nèi)編寫一段不含有參數(shù)。通?？梢栽谟|發(fā)器內(nèi)編寫一段自動(dòng)執(zhí)行的程序，用于保證數(shù)據(jù)操作的完好自動(dòng)執(zhí)行的程序，用于保證數(shù)據(jù)操作的完好性，從而擴(kuò)展了對(duì)默許值、約束和規(guī)那么的性，從而擴(kuò)展了對(duì)默許值、約束和規(guī)那么的完好性檢查。對(duì)表進(jìn)展包括添加數(shù)據(jù)、刪除完好性檢查。對(duì)表進(jìn)展包括添加數(shù)據(jù)、刪除數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時(shí)，觸數(shù)據(jù)、更新數(shù)據(jù)中的一種或多種操作時(shí)，觸發(fā)器就會(huì)自動(dòng)執(zhí)行。發(fā)器就會(huì)自動(dòng)執(zhí)行。 觸發(fā)器可以劃分為三種類別：觸發(fā)器可以劃分為三種類別：INSERTINSERT觸發(fā)器、觸發(fā)器、DELETEDELETE

18、觸發(fā)器、觸發(fā)器、UPDATEUPDATE觸發(fā)觸發(fā)器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、器。這三種觸發(fā)器分別在發(fā)生數(shù)據(jù)的添加、刪除、修正行為時(shí)自動(dòng)執(zhí)行。刪除、修正行為時(shí)自動(dòng)執(zhí)行。2觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器有助于在添加、更新或刪除表中的記錄時(shí)保管表之間已觸發(fā)器有助于在添加、更新或刪除表中的記錄時(shí)保管表之間已定義的關(guān)系。定義的關(guān)系。觸發(fā)器可以自動(dòng)調(diào)用，當(dāng)發(fā)生了對(duì)數(shù)據(jù)所作的任何修正時(shí)，與觸發(fā)器可以自動(dòng)調(diào)用，當(dāng)發(fā)生了對(duì)數(shù)據(jù)所作的任何修正時(shí)，與之相關(guān)的觸發(fā)器就會(huì)立刻被激活。之相關(guān)的觸發(fā)器就會(huì)立刻被激活?？梢酝瓿蓴?shù)據(jù)庫中相關(guān)表之間的級(jí)聯(lián)修正。級(jí)聯(lián)修正是指為了可以完成數(shù)據(jù)庫中相關(guān)表之間的級(jí)聯(lián)修正。級(jí)聯(lián)修

19、正是指為了保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對(duì)一張表進(jìn)展修保證數(shù)據(jù)之間的邏輯性以及依賴關(guān)系，在對(duì)一張表進(jìn)展修正的同時(shí)，其他表中需求進(jìn)展的修正可以自動(dòng)實(shí)現(xiàn)。正的同時(shí)，其他表中需求進(jìn)展的修正可以自動(dòng)實(shí)現(xiàn)。觸發(fā)器可以對(duì)需求存儲(chǔ)的數(shù)據(jù)加以限制，并且可以實(shí)現(xiàn)比觸發(fā)器可以對(duì)需求存儲(chǔ)的數(shù)據(jù)加以限制，并且可以實(shí)現(xiàn)比CHECK約束更為復(fù)雜的功能。在約束更為復(fù)雜的功能。在CHECK約束中不允許援約束中不允許援用其他表中的列，而觸發(fā)器可以援用。用其他表中的列，而觸發(fā)器可以援用。3觸發(fā)方式觸發(fā)方式 觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。觸發(fā)器的觸發(fā)方式可以分為后觸發(fā)和替代觸發(fā)兩種方式。后觸發(fā)：指可以引發(fā)

20、觸發(fā)器的修正操作在完成之后才執(zhí)行觸發(fā)后觸發(fā)：指可以引發(fā)觸發(fā)器的修正操作在完成之后才執(zhí)行觸發(fā)器的行為方式。創(chuàng)建該類觸發(fā)器，需求運(yùn)用器的行為方式。創(chuàng)建該類觸發(fā)器，需求運(yùn)用AFTER關(guān)鍵字或關(guān)鍵字或者者FOR關(guān)鍵字。關(guān)鍵字。替代觸發(fā)：指定執(zhí)行一個(gè)觸發(fā)器，而不是執(zhí)行SQL語句，這種替代觸發(fā)語句的方式稱為替代觸發(fā)方式。8.2.3 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器可以運(yùn)用企業(yè)管理器以及可以運(yùn)用企業(yè)管理器以及CREATE TRIGGER語句兩語句兩種方式來創(chuàng)建觸發(fā)器。種方式來創(chuàng)建觸發(fā)器。1運(yùn)用企業(yè)管理器創(chuàng)建觸發(fā)器運(yùn)用企業(yè)管理器創(chuàng)建觸發(fā)器翻開企業(yè)管理器，在控制臺(tái)根目錄下依次展開效力翻開企業(yè)管理器，在控制臺(tái)根目錄下依次展開

21、效力器組、效力器節(jié)點(diǎn)、數(shù)據(jù)庫節(jié)點(diǎn)。器組、效力器節(jié)點(diǎn)、數(shù)據(jù)庫節(jié)點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗選擇某一個(gè)數(shù)據(jù)庫，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表中選擇其中的一張表。口的列表中選擇其中的一張表。右擊所選中的表，在彈出的下拉菜單中選擇【一切右擊所選中的表，在彈出的下拉菜單中選擇【一切義務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命義務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將翻開圖令，將翻開圖8-9所示的【觸發(fā)器屬性】對(duì)話框。所示的【觸發(fā)器屬性】對(duì)話框。圖8-9 【觸發(fā)器屬性】對(duì)話框在【觸發(fā)器屬性】對(duì)話框的文本框中，將TRIGGER NAME修正為新建觸發(fā)器的稱號(hào)，并輸入觸發(fā)器

22、所包含的SQL語句。經(jīng)過點(diǎn)擊【檢查語法】按鈕來判別觸發(fā)器的語法能否正確，最后，點(diǎn)擊【確定】按鈕保管新建的觸發(fā)器。2運(yùn)用CREATE TRIGGER語句創(chuàng)建觸發(fā)器CREATE TRIGGER語句的部分語法格式：CREATE TRIGGER 觸發(fā)器稱號(hào) ON 表名 | 視圖名 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATEAS SQL語句 .n 其中：觸發(fā)器稱號(hào) 必需符合標(biāo)識(shí)符命名規(guī)那么，并且在同一個(gè)數(shù)據(jù)庫中觸發(fā)器的稱號(hào)不允許反復(fù)。觸發(fā)器稱號(hào)中可以省略一切者稱號(hào)。表名或視圖稱號(hào) 是指建立觸發(fā)器所依賴的對(duì)象。也可以稱

23、為觸發(fā)器表或觸發(fā)器視圖。WITH ENCRYPTION 該選項(xiàng)的作用為對(duì)觸發(fā)器中所包含的SQL語句進(jìn)展加密。FOR或AFTER 代表后觸發(fā)方式，即當(dāng)滿足一切的援用級(jí)聯(lián)操作和約束檢查后并且完成了SQL 語句中指定的一切操作后，指定的觸發(fā)器才會(huì)被執(zhí)行。在視圖上不允許采用后觸發(fā)方式。INSTEAD OF 表示替代觸發(fā)方式，每一個(gè)INSERT、UPDATE、DELETE 語句只能定義一個(gè) INSTEAD OF 觸發(fā)器。 DELETE、INSERT、UPDATE 三個(gè)選項(xiàng)中應(yīng)該至少選擇其一，用于表示在表或視圖上執(zhí)行了哪一類的操作時(shí)會(huì)將觸發(fā)器激活。 SQL語句 為觸發(fā)器中包含的條件以及需求執(zhí)行的操作。 在

24、執(zhí)行觸發(fā)器時(shí)，系統(tǒng)會(huì)自動(dòng)創(chuàng)建兩張暫時(shí)表INSERTED、DELETED，這兩張表的構(gòu)造與觸發(fā)器所依賴的表類似，用于保管在用戶操作過程中被插入或被刪除的數(shù)據(jù)。每一個(gè)觸發(fā)器在執(zhí)行過程中都會(huì)產(chǎn)生與之相關(guān)的上述兩張暫時(shí)表，并且在執(zhí)行終了后，兩個(gè)暫時(shí)表會(huì)自動(dòng)被系統(tǒng)刪除。對(duì)暫時(shí)表可以進(jìn)展查詢操作，如SELECT * FROM DELETED，但是不能對(duì)暫時(shí)表進(jìn)展修正 【例8-5】在數(shù)據(jù)庫XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器的作用為：當(dāng)在班級(jí)表中刪除某一個(gè)班級(jí)時(shí)，在學(xué)生根本信息表中該班級(jí)所包含的學(xué)生信息也全部被刪除。提示：在此例中，由于涉及到了班級(jí)表的刪除操作，因此需求設(shè)計(jì)一個(gè)DELETE類型的觸發(fā)器。在查詢

25、分析器中運(yùn)轉(zhuǎn)如下命令：USE XSCJGOCREATE TRIGGER del_bj ON 班級(jí)表AFTER DELETEAS DELETE FROM 學(xué)生根本信息表 WHERE 班級(jí)編號(hào) IN (SELECT 班級(jí)編號(hào) FROM DELETED)GO運(yùn)轉(zhuǎn)結(jié)果如圖8-10所示。圖8-10 一個(gè)DELETE類型的觸發(fā)器【例8-6】在數(shù)據(jù)庫XSCJ中設(shè)計(jì)一個(gè)觸發(fā)器，該觸發(fā)器可以保證在學(xué)生根本信息表中添加新的紀(jì)錄時(shí)，新學(xué)生的班級(jí)編號(hào)必需曾經(jīng)存在于班級(jí)表中。提示：設(shè)計(jì)該觸發(fā)器有助于實(shí)現(xiàn)學(xué)生信息的完好性。在此例中由于涉及到了學(xué)生根本信息表中的添加操作，因此需求設(shè)計(jì)一個(gè)INSERT類型的觸發(fā)器。在查詢分析

26、器中運(yùn)轉(zhuǎn)如下命令：USE XSCJGOCREATE TRIGGER insert_xs ON 學(xué)生根本信息表AFTER INSERTAS IF EXISTS ( SELECT * FROM INSERTED WHERE 班級(jí)編號(hào) IN (SELECT 班級(jí)編號(hào) FROM 班級(jí)表) ) PRINT 添加勝利！ ELSE BEGIN PRINT 班級(jí)編號(hào)與現(xiàn)有的班級(jí)不符！ ROLLBACK TRANSACTION END運(yùn)轉(zhuǎn)結(jié)果如圖8-11所示。 圖8-11 一個(gè)INSERT類型的觸發(fā)器 創(chuàng)建了觸發(fā)器insert_xs之后，我們可以添加新的學(xué)生紀(jì)錄進(jìn)展測試，例如：INSERT INTO 學(xué)生根本信

27、息表 (學(xué)號(hào),姓名,性別,族別,班級(jí)編號(hào)) VALUES(000108,王松濤,男,漢,20191001)由于“班級(jí)表中不存在編號(hào)為20191001的班級(jí)，因此添加操作將會(huì)被取消。ROLLBACK TRANSACTION用于回滾曾經(jīng)完成的操作。運(yùn)轉(zhuǎn)結(jié)果如圖8-12所示。圖8-12 添加操作被取消8.2.4 修正與刪除觸發(fā)器修正與刪除觸發(fā)器1運(yùn)用企業(yè)管理器修正觸發(fā)器運(yùn)用企業(yè)管理器修正觸發(fā)器在控制臺(tái)根目錄下依次展開效力器組、效力器節(jié)在控制臺(tái)根目錄下依次展開效力器組、效力器節(jié)點(diǎn)、數(shù)據(jù)庫節(jié)點(diǎn)。點(diǎn)、數(shù)據(jù)庫節(jié)點(diǎn)。選擇某一個(gè)數(shù)據(jù)庫，并雙擊【表】節(jié)點(diǎn)，在右側(cè)選擇某一個(gè)數(shù)據(jù)庫，并雙擊【表】節(jié)點(diǎn)，在右側(cè)窗口的列表

28、中選擇某一張需求對(duì)其所建立的觸發(fā)器窗口的列表中選擇某一張需求對(duì)其所建立的觸發(fā)器進(jìn)展修該的表。進(jìn)展修該的表。右擊所選中的表，在彈出的下拉菜單中選擇【一右擊所選中的表，在彈出的下拉菜單中選擇【一切義務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】切義務(wù)】，并繼續(xù)選擇級(jí)聯(lián)菜單中的【管理觸發(fā)器】命令，將翻開【觸發(fā)器】屬性對(duì)話框，如圖命令，將翻開【觸發(fā)器】屬性對(duì)話框，如圖8-13所所示。示。在【觸發(fā)器屬性】對(duì)話框中，在【稱號(hào)】下拉列在【觸發(fā)器屬性】對(duì)話框中，在【稱號(hào)】下拉列表框選擇一個(gè)已有的觸發(fā)器，并在【文本】編輯框表框選擇一個(gè)已有的觸發(fā)器，并在【文本】編輯框中對(duì)其所包含的中對(duì)其所包含的SQL語句進(jìn)展修正。語

29、句進(jìn)展修正。圖8-13 【觸發(fā)器】屬性對(duì)話框在語法檢查無誤的情況下，點(diǎn)擊【確定】按鈕保管修正。假設(shè)需求刪除當(dāng)前觸發(fā)器，可以點(diǎn)擊【刪除】按鈕。2運(yùn)用ALTER TRIGGER語句修正觸發(fā)器ALTER TRIGGER語句與CREATE TRIGGER語句的語法格式及其參數(shù)類似，部分語法格式為：ALTER TRIGGER 觸發(fā)器稱號(hào) ON 表名 | 視圖名 WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATEAS SQL語句 .n 【例8-7】修正del_bj觸發(fā)器，使得在班級(jí)表中刪除某一個(gè)班級(jí)時(shí)，不僅在學(xué)生根本信息表中該

30、班級(jí)所包含的學(xué)生信息將被刪除，而且在成果表中與學(xué)生相關(guān)的數(shù)據(jù)也將被刪除。在查詢分析器中運(yùn)轉(zhuǎn)如下命令：ALTER TRIGGER del_bj ON 班級(jí)表AFTER DELETEAS DELETE FROM 成果表 WHERE 學(xué)號(hào) IN (SELECT 學(xué)號(hào) FROM 學(xué)生根本信息表 WHERE 班級(jí)編號(hào) IN (SELECT 班級(jí)編號(hào) FROM DELETED) DELETE FROM 學(xué)生根本信息表 WHERE 班級(jí)編號(hào) IN (SELECT 班級(jí)編號(hào) FROM DELETED)運(yùn)轉(zhuǎn)結(jié)果如圖8-14所示。圖8-14 修正del_bj觸發(fā)器3運(yùn)用運(yùn)用DROP TRIGGER語句刪除觸發(fā)器語

31、句刪除觸發(fā)器移除觸發(fā)器時(shí)，與觸發(fā)器有關(guān)的信息將從移除觸發(fā)器時(shí)，與觸發(fā)器有關(guān)的信息將從sysobjects和和syscomments系統(tǒng)表中刪除。可以經(jīng)過刪除觸發(fā)器或刪除表系統(tǒng)表中刪除。可以經(jīng)過刪除觸發(fā)器或刪除表兩種方式移除觸發(fā)器。刪除表時(shí)，將除去一切與表相關(guān)聯(lián)的觸兩種方式移除觸發(fā)器。刪除表時(shí)，將除去一切與表相關(guān)聯(lián)的觸發(fā)器。發(fā)器。DROP TRIGGER語句的語法格式為：DROP TRIGGER 觸發(fā)器稱號(hào) ,.n 觸發(fā)器稱號(hào)為數(shù)據(jù)庫中現(xiàn)有的觸發(fā)器?？梢灾付ㄒ粋€(gè)或多個(gè)觸發(fā)器稱號(hào)來刪除一個(gè)或多個(gè)觸發(fā)器。【例8-8】刪除觸發(fā)器del_bj以及insert_xs。在查詢分析器中運(yùn)轉(zhuǎn)如下命令：USE X

32、SCJGODROP TRIGGER del_bj , insert_xs運(yùn)轉(zhuǎn)結(jié)果如圖8-15所示。圖8-15 刪除觸發(fā)器del_bj以及insert_xs本章小結(jié)本章小結(jié)本章學(xué)習(xí)了存儲(chǔ)過程與觸發(fā)器的本章學(xué)習(xí)了存儲(chǔ)過程與觸發(fā)器的相關(guān)知識(shí)，存儲(chǔ)過程中可以將多相關(guān)知識(shí)，存儲(chǔ)過程中可以將多條條SQL語句集中在一同完成復(fù)雜語句集中在一同完成復(fù)雜的功能，從而用戶可以在不具有的功能，從而用戶可以在不具有對(duì)數(shù)據(jù)對(duì)象訪問的權(quán)限下調(diào)用這對(duì)數(shù)據(jù)對(duì)象訪問的權(quán)限下調(diào)用這些存儲(chǔ)過程完成的特定的操作。些存儲(chǔ)過程完成的特定的操作。觸發(fā)器可以協(xié)助用戶完成數(shù)據(jù)的觸發(fā)器可以協(xié)助用戶完成數(shù)據(jù)的驗(yàn)證功能，從而可以保證數(shù)據(jù)的驗(yàn)證功能，從而可以保證數(shù)據(jù)的一致性、完好性。一致性、完好性。練習(xí)與上機(jī)練習(xí)與上機(jī)一選擇題一選擇題1下面關(guān)于存儲(chǔ)過程的描畫中哪些是正下面關(guān)于存儲(chǔ)過程的描畫中哪些是正確的確的 A自定義存儲(chǔ)過程與系統(tǒng)存儲(chǔ)過程稱自定義存儲(chǔ)過程與系統(tǒng)存儲(chǔ)過程稱號(hào)可以一樣號(hào)可以一樣B存儲(chǔ)過程最多可以支持存儲(chǔ)過程最多可以支持64層的嵌套層的嵌套C命名存儲(chǔ)過程中的標(biāo)識(shí)符時(shí)，長度命名存儲(chǔ)過