1、第第9章章 存儲(chǔ)過程與觸發(fā)器存儲(chǔ)過程與觸發(fā)器本章學(xué)習(xí)目標(biāo)了解存儲(chǔ)過程的概念、優(yōu)點(diǎn)及分類了解存儲(chǔ)過程的概念、優(yōu)點(diǎn)及分類了解觸發(fā)器的概念、優(yōu)點(diǎn)及分類了解觸發(fā)器的概念、優(yōu)點(diǎn)及分類掌握創(chuàng)建、執(zhí)行、查看、修改和刪除存儲(chǔ)過掌握創(chuàng)建、執(zhí)行、查看、修改和刪除存儲(chǔ)過程的方法程的方法了解創(chuàng)建觸發(fā)器時(shí)用到的兩個(gè)臨時(shí)表了解創(chuàng)建觸發(fā)器時(shí)用到的兩個(gè)臨時(shí)表掌握創(chuàng)建、執(zhí)行、查看、修改和刪除觸發(fā)器掌握創(chuàng)建、執(zhí)行、查看、修改和刪除觸發(fā)器的方法的方法9.1 存儲(chǔ)過程9.1.1 存儲(chǔ)過程概述1. 存儲(chǔ)過程的概念存儲(chǔ)過程的概念在使用在使用T-SQL語言編程的過程中，可以將某語言編程的過程中，可以將某些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代

2、碼些需要多次調(diào)用的實(shí)現(xiàn)某個(gè)特定任務(wù)的代碼段編寫成一個(gè)過程，將其保存在數(shù)據(jù)庫中，段編寫成一個(gè)過程，將其保存在數(shù)據(jù)庫中，并由并由SQL Server服務(wù)器通過過程名來調(diào)用它服務(wù)器通過過程名來調(diào)用它們，這些過程就叫做存儲(chǔ)過程。們，這些過程就叫做存儲(chǔ)過程。存儲(chǔ)過程在創(chuàng)建時(shí)就被編譯和優(yōu)化，調(diào)用一存儲(chǔ)過程在創(chuàng)建時(shí)就被編譯和優(yōu)化，調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時(shí)可以直接執(zhí)行。用時(shí)可以直接執(zhí)行。9.1.1 存儲(chǔ)過程概述存儲(chǔ)過程有以下特點(diǎn)：存儲(chǔ)過程有以下特點(diǎn)：存儲(chǔ)過程中可以包含一條或多條存儲(chǔ)過程中可以包含一條或多條T-SQL語句。語句。存儲(chǔ)過程可以接受輸入?yún)?/p>

3、數(shù)并可以返回輸出存儲(chǔ)過程可以接受輸入?yún)?shù)并可以返回輸出值。值。存儲(chǔ)過程可以嵌套調(diào)用。存儲(chǔ)過程可以嵌套調(diào)用。存儲(chǔ)過程可以返回執(zhí)行情況的狀態(tài)代碼給調(diào)存儲(chǔ)過程可以返回執(zhí)行情況的狀態(tài)代碼給調(diào)用它的程序。用它的程序。9.1.1 存儲(chǔ)過程概述2存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的優(yōu)點(diǎn) （1）實(shí)現(xiàn)了模塊化編程）實(shí)現(xiàn)了模塊化編程（2）存儲(chǔ)過程具有對(duì)數(shù)據(jù)庫立即訪問的功能）存儲(chǔ)過程具有對(duì)數(shù)據(jù)庫立即訪問的功能（3）加快程序的運(yùn)行速度）加快程序的運(yùn)行速度（4）可以減少網(wǎng)絡(luò)流量）可以減少網(wǎng)絡(luò)流量（5）可以提高數(shù)據(jù)庫的安全性）可以提高數(shù)據(jù)庫的安全性（6）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)9.1.1 存儲(chǔ)過程概述在

4、在SQL Server中的存儲(chǔ)過程分為：中的存儲(chǔ)過程分為：系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程擴(kuò)展的存儲(chǔ)過程擴(kuò)展的存儲(chǔ)過程用戶定義的存儲(chǔ)過程用戶定義的存儲(chǔ)過程 T-SQL存儲(chǔ)過程存儲(chǔ)過程 CLR存儲(chǔ)過程存儲(chǔ)過程9.1.2 創(chuàng)建存儲(chǔ)過程在在SQL Server中，可以使用兩種方法創(chuàng)建存中，可以使用兩種方法創(chuàng)建存儲(chǔ)過程：使用儲(chǔ)過程：使用SSMS和和T-SQL語句。語句。當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三當(dāng)創(chuàng)建存儲(chǔ)過程時(shí)，需要確定存儲(chǔ)過程的三個(gè)組成部分：個(gè)組成部分：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫的操作語句，包括調(diào)用其他被執(zhí)行的針對(duì)數(shù)據(jù)庫的操

5、作語句，包括調(diào)用其他存儲(chǔ)過程的語句。存儲(chǔ)過程的語句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。失敗。9.1.2 創(chuàng)建存儲(chǔ)過程1使用使用SSMS創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程在在SSMS中，選擇指定的服務(wù)器和數(shù)據(jù)庫，中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的展開數(shù)據(jù)庫中的“可編程性可編程性”文件夾，右擊文件夾，右擊其中的其中的“存儲(chǔ)過程存儲(chǔ)過程”，在彈出的快捷菜單中，在彈出的快捷菜單中選擇選擇“新建存儲(chǔ)過程新建存儲(chǔ)過程”選項(xiàng)。選項(xiàng)。例例9-1：創(chuàng)建一個(gè)名為：創(chuàng)建一個(gè)名為“proc_CustomerName”的存儲(chǔ)過程，要的存儲(chǔ)過程，要求完成以下功能：在

6、求完成以下功能：在Customer表中查詢電表中查詢電話號(hào)碼以話號(hào)碼以“245”開頭的客戶的姓名和電話開頭的客戶的姓名和電話字段的值。字段的值。9.1.2 創(chuàng)建存儲(chǔ)過程2使用使用T-SQL語句創(chuàng)建存儲(chǔ)過程語句創(chuàng)建存儲(chǔ)過程CREATE PROC|PROCEDUREschema_name. procedure_name parameter type_schema_name.data_type VARYING=default OUT|OUTPUT READONLY ,.n WITH ENCRYPTION,RECOMPILE FOR REPLICATIONAS ;.n;9.1.2 創(chuàng)建存儲(chǔ)過程:= B

7、EGIN END9.1.2 創(chuàng)建存儲(chǔ)過程其中，各參數(shù)的意義如下：其中，各參數(shù)的意義如下：schema_name：過程所屬架構(gòu)的名稱。過程所屬架構(gòu)的名稱。 procedure_name：新存儲(chǔ)過程的名稱。新存儲(chǔ)過程的名稱。 parameter：過程中的參數(shù)。過程中的參數(shù)。 type_schema_name. data_type：參數(shù)參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。以及所屬架構(gòu)的數(shù)據(jù)類型。VARYING：指定作為輸出參數(shù)支持的結(jié)果集。指定作為輸出參數(shù)支持的結(jié)果集。僅適用于僅適用于cursor參數(shù)。參數(shù)。9.1.2 創(chuàng)建存儲(chǔ)過程default：參數(shù)的默認(rèn)值。參數(shù)的默認(rèn)值。OUTPUT：指示參數(shù)是輸出參數(shù)

8、。指示參數(shù)是輸出參數(shù)。READONLY：指示不能在過程的主體中更新：指示不能在過程的主體中更新或修改參數(shù)?；蛐薷膮?shù)。RECOMPILE：指示：指示SQL Server每次運(yùn)行該每次運(yùn)行該過程時(shí)，將對(duì)其重新編譯。過程時(shí)，將對(duì)其重新編譯。ENCRYPTION：將將CREATE PROCEDURE語句的原始文本加密。語句的原始文本加密。9.1.2 創(chuàng)建存儲(chǔ)過程FOR REPLICATION：指定不能在訂閱服務(wù)：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程。器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程。sql_statement：要包含在過程中的一個(gè)或：要包含在過程中的一個(gè)或多個(gè)多個(gè)T-SQL語句。語句。9.1.

9、2 創(chuàng)建存儲(chǔ)過程例例9-2：創(chuàng)建一個(gè)存儲(chǔ)過程：創(chuàng)建一個(gè)存儲(chǔ)過程proc_GetAllProducts，要求完成以下功能：，要求完成以下功能：在表在表Product中查找所有產(chǎn)品的產(chǎn)品編號(hào)、中查找所有產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、標(biāo)準(zhǔn)成本和銷售價(jià)格等字段。此產(chǎn)品名稱、標(biāo)準(zhǔn)成本和銷售價(jià)格等字段。此存儲(chǔ)過程不使用任何參數(shù)。存儲(chǔ)過程不使用任何參數(shù)。例例9-3：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程proc_GetProductsCheck，要求完成以下，要求完成以下功能：在表功能：在表Product中查找指定名稱的產(chǎn)品中查找指定名稱的產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、標(biāo)準(zhǔn)成本和銷售價(jià)的產(chǎn)品編號(hào)、產(chǎn)

10、品名稱、標(biāo)準(zhǔn)成本和銷售價(jià)格等字段。并對(duì)定義信息進(jìn)行加密。格等字段。并對(duì)定義信息進(jìn)行加密。9.1.2 創(chuàng)建存儲(chǔ)過程例例9-4：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程：創(chuàng)建一個(gè)帶有參數(shù)的存儲(chǔ)過程proc_GetList，要求完成以下功能：在，要求完成以下功能：在Product表中查找價(jià)格不超過指定數(shù)值并且表中查找價(jià)格不超過指定數(shù)值并且名稱中包含指定字符的產(chǎn)品列表。名稱中包含指定字符的產(chǎn)品列表。9.1.3 執(zhí)行存儲(chǔ)過程存儲(chǔ)過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在存儲(chǔ)過程創(chuàng)建成功后，保存在數(shù)據(jù)庫中。在SQL Server中可以使用中可以使用EXECUTE命令來直命令來直接執(zhí)行存儲(chǔ)過程，語法形式如下：接執(zhí)行存儲(chǔ)過程，語法

11、形式如下：EXECUTE r e t u r n _ s t a t u s = procedure_name| p r o c e d u r e _ n a m e _ v a r parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE;9.1.3 執(zhí)行存儲(chǔ)過程其中，各參數(shù)的說明如下：其中，各參數(shù)的說明如下：EXECUTE：執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字。執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字。return_status：是一個(gè)可選的整型變量，保存存是一個(gè)可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。儲(chǔ)過程的返回狀態(tài)。procedure_name：指定執(zhí)行的

12、存儲(chǔ)過程的名稱。指定執(zhí)行的存儲(chǔ)過程的名稱。procedure_name_var：是局部定義變量名，代是局部定義變量名，代表存儲(chǔ)過程名稱。表存儲(chǔ)過程名稱。parameter：是在創(chuàng)建存儲(chǔ)過程時(shí)定義的過程參是在創(chuàng)建存儲(chǔ)過程時(shí)定義的過程參數(shù)。數(shù)。9.1.3 執(zhí)行存儲(chǔ)過程例例9-5：執(zhí)行前面創(chuàng)建的：執(zhí)行前面創(chuàng)建的proc_CustomerName存存儲(chǔ)過程，它是一個(gè)無參的存儲(chǔ)過程。儲(chǔ)過程，它是一個(gè)無參的存儲(chǔ)過程。例例9-6：執(zhí)行存儲(chǔ)過程：執(zhí)行存儲(chǔ)過程proc_GetProducts，該存儲(chǔ)，該存儲(chǔ)過程有一個(gè)輸入?yún)?shù)過程有一個(gè)輸入?yún)?shù)“產(chǎn)品名稱產(chǎn)品名稱”，在執(zhí)行時(shí)需要，在執(zhí)行時(shí)需要輸入一個(gè)產(chǎn)品名稱。輸入

13、一個(gè)產(chǎn)品名稱。例例9-7：執(zhí)行存儲(chǔ)過程：執(zhí)行存儲(chǔ)過程proc_GetList，該存儲(chǔ)過程，該存儲(chǔ)過程有兩個(gè)輸入?yún)?shù)和兩個(gè)輸出參數(shù)。有兩個(gè)輸入?yún)?shù)和兩個(gè)輸出參數(shù)。注意：如果省略注意：如果省略EXECUTE關(guān)鍵字，則存儲(chǔ)過程必關(guān)鍵字，則存儲(chǔ)過程必須是批處理中的第一條語句，否則會(huì)出錯(cuò)。須是批處理中的第一條語句，否則會(huì)出錯(cuò)。9.1.4 查看存儲(chǔ)過程1使用使用SSMS查看用戶定義的存儲(chǔ)過程查看用戶定義的存儲(chǔ)過程在在SSMS中，選擇指定的服務(wù)器和數(shù)據(jù)庫，中，選擇指定的服務(wù)器和數(shù)據(jù)庫，展開數(shù)據(jù)庫中的展開數(shù)據(jù)庫中的“可編程性可編程性”，選擇，選擇“存儲(chǔ)存儲(chǔ)過程過程”，可以顯示出數(shù)據(jù)庫中的所有存儲(chǔ)過，可以顯示出

14、數(shù)據(jù)庫中的所有存儲(chǔ)過程。右擊某個(gè)要查看的存儲(chǔ)過程，在快捷菜程。右擊某個(gè)要查看的存儲(chǔ)過程，在快捷菜單中選擇相應(yīng)的操作。單中選擇相應(yīng)的操作。9.1.4 查看存儲(chǔ)過程2使用系統(tǒng)存儲(chǔ)過程查看用戶定義的存儲(chǔ)過使用系統(tǒng)存儲(chǔ)過程查看用戶定義的存儲(chǔ)過程程例例9-8：使用系統(tǒng)存儲(chǔ)過程查看：使用系統(tǒng)存儲(chǔ)過程查看proc_GetAllProducts存儲(chǔ)過程的參數(shù)及其存儲(chǔ)過程的參數(shù)及其數(shù)據(jù)類型，查看數(shù)據(jù)類型，查看proc_GetProductsCheck存儲(chǔ)過程的定義信息。存儲(chǔ)過程的定義信息。9.4.1 查看存儲(chǔ)過程sp_helptext：用于顯示存儲(chǔ)過程的源代碼用于顯示存儲(chǔ)過程的源代碼sp_helptext ob

15、jname= name參數(shù)參數(shù)name為要查看的存儲(chǔ)過程的名稱。為要查看的存儲(chǔ)過程的名稱。9.1.5 修改存儲(chǔ)過程1使用使用SSMS修改存儲(chǔ)過程修改存儲(chǔ)過程使用使用SSMS可以很方便地修改存儲(chǔ)過程的定可以很方便地修改存儲(chǔ)過程的定義。在義。在SSMS中右擊要修改的存儲(chǔ)過程，從中右擊要修改的存儲(chǔ)過程，從彈出的快捷菜單中選擇彈出的快捷菜單中選擇“修改修改”選項(xiàng)，則會(huì)選項(xiàng)，則會(huì)出現(xiàn)與創(chuàng)建存儲(chǔ)過程時(shí)類似的窗口。在該窗出現(xiàn)與創(chuàng)建存儲(chǔ)過程時(shí)類似的窗口。在該窗口中，可以直接修改定義該存儲(chǔ)過程口中，可以直接修改定義該存儲(chǔ)過程SQL語語句。句。9.1.5 修改存儲(chǔ)過程2使用使用T-SQL語句修改存儲(chǔ)過程語句修改存

16、儲(chǔ)過程使用使用ALTER PROCEDURE語句可以更改存儲(chǔ)過程，語句可以更改存儲(chǔ)過程，但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)但不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過程或觸發(fā)器。其語法形式如下：器。其語法形式如下：ALTER PROC | PROCEDURE schema_name. procedure_nameparametertype_schema_name. data_type VARYING = default OUT|OUTPUT READONLY ,.n WITH ENCRYPTION,RECOMPILE FOR REPLICATIONAS sql_statement .n 9.

17、1.5 修改存儲(chǔ)過程修改存儲(chǔ)過程時(shí)，應(yīng)該注意以下幾點(diǎn)：修改存儲(chǔ)過程時(shí)，應(yīng)該注意以下幾點(diǎn)：如 果 原 來 的 過 程 定 義 是 使 用如 果 原 來 的 過 程 定 義 是 使 用 W I T H ENCRYPTION創(chuàng)建的，那么只有在創(chuàng)建的，那么只有在ALTER PROCEDURE中也包含這個(gè)選項(xiàng)時(shí)，這個(gè)選中也包含這個(gè)選項(xiàng)時(shí)，這個(gè)選項(xiàng)才有效。項(xiàng)才有效。每次只能修改一個(gè)存儲(chǔ)過程。每次只能修改一個(gè)存儲(chǔ)過程。用用ALTER PROCEDURE更改的存儲(chǔ)過程的更改的存儲(chǔ)過程的權(quán)限保持不變。權(quán)限保持不變。9.1.5 修改存儲(chǔ)過程例例9-9：修改存儲(chǔ)過程：修改存儲(chǔ)過程proc_GetProductsCh

18、eck，使之完成以下，使之完成以下功能：在表功能：在表Product中查找指定名稱的產(chǎn)品中查找指定名稱的產(chǎn)品的產(chǎn)品編號(hào)、產(chǎn)品名稱、標(biāo)準(zhǔn)成本、銷售價(jià)的產(chǎn)品編號(hào)、產(chǎn)品名稱、標(biāo)準(zhǔn)成本、銷售價(jià)格和開始銷售日期等字段。格和開始銷售日期等字段。9.1.6 刪除存儲(chǔ)過程1使用使用SSMS刪除存儲(chǔ)過程刪除存儲(chǔ)過程在打開的在打開的SSMS窗口中，右擊要?jiǎng)h除的存儲(chǔ)窗口中，右擊要?jiǎng)h除的存儲(chǔ)過程，從彈出的快捷菜單中選擇過程，從彈出的快捷菜單中選擇“刪除刪除”選選項(xiàng)，會(huì)彈出項(xiàng)，會(huì)彈出“刪除對(duì)象刪除對(duì)象”對(duì)話框。在該對(duì)話對(duì)話框。在該對(duì)話框中，單擊框中，單擊“確定確定”按鈕，即可完成刪除操按鈕，即可完成刪除操作。作。在刪除

19、該對(duì)象之前，單擊在刪除該對(duì)象之前，單擊“顯示依賴關(guān)系顯示依賴關(guān)系”按鈕，可以查看與該存儲(chǔ)過程有依賴關(guān)系的按鈕，可以查看與該存儲(chǔ)過程有依賴關(guān)系的其他數(shù)據(jù)庫對(duì)象名稱。其他數(shù)據(jù)庫對(duì)象名稱。9.1.6 刪除存儲(chǔ)過程2使用使用T-SQL語句刪除存儲(chǔ)過程語句刪除存儲(chǔ)過程DROP語句語句可以將一個(gè)或者多個(gè)存儲(chǔ)過程或可以將一個(gè)或者多個(gè)存儲(chǔ)過程或者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法者存儲(chǔ)過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：形式如下：DROP PROC | PROCEDURE schema_name. procedure ,.n 例例9-10：刪除：刪除AWLT數(shù)據(jù)庫中的數(shù)據(jù)庫中的proc_Custome

20、rName和和proc_GetProductsCheck兩個(gè)存儲(chǔ)過程。兩個(gè)存儲(chǔ)過程。9.2 觸發(fā)器9.2.1 觸發(fā)器概述1. 觸發(fā)器的概念觸發(fā)器的概念觸發(fā)器是一種特殊類型的觸發(fā)器是一種特殊類型的存儲(chǔ)過程存儲(chǔ)過程，當(dāng)某個(gè)，當(dāng)某個(gè)事件發(fā)生時(shí)，它自動(dòng)被觸發(fā)執(zhí)行。事件發(fā)生時(shí)，它自動(dòng)被觸發(fā)執(zhí)行。觸發(fā)器可以用于觸發(fā)器可以用于SQL Server約束、默認(rèn)值和約束、默認(rèn)值和規(guī)則的完整性檢查，還可以完成用普通約束規(guī)則的完整性檢查，還可以完成用普通約束難以實(shí)現(xiàn)的復(fù)雜功能。難以實(shí)現(xiàn)的復(fù)雜功能。觸發(fā)器和引起觸發(fā)器執(zhí)行的觸發(fā)器和引起觸發(fā)器執(zhí)行的SQL語句被當(dāng)作語句被當(dāng)作一次事務(wù)處理，如果這次事務(wù)未獲得成功，一次事務(wù)處

21、理，如果這次事務(wù)未獲得成功，SQL Server會(huì)自動(dòng)返回該事務(wù)執(zhí)行前的狀態(tài)。會(huì)自動(dòng)返回該事務(wù)執(zhí)行前的狀態(tài)。9.2.1 觸發(fā)器概述2觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器的優(yōu)點(diǎn)（1）觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行）觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。級(jí)聯(lián)更改。（2）觸發(fā)器可以防止惡意或錯(cuò)誤的）觸發(fā)器可以防止惡意或錯(cuò)誤的INSERT、UPDATE以及以及DELETE操作，并強(qiáng)制執(zhí)行比操作，并強(qiáng)制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其他限制。約束定義的限制更為復(fù)雜的其他限制。（3）觸發(fā)器可以比較表數(shù)據(jù)修改前后的狀態(tài)，）觸發(fā)器可以比較表數(shù)據(jù)修改前后的狀態(tài)，并根據(jù)該差異采取相應(yīng)措施。并根據(jù)該差異采取相應(yīng)措

22、施。9.2.1 觸發(fā)器概述在在SQL Server 2008中，觸發(fā)器可以中，觸發(fā)器可以分為分為:DML觸發(fā)器觸發(fā)器DDL觸發(fā)器觸發(fā)器登錄觸發(fā)器登錄觸發(fā)器9.2.1 觸發(fā)器概述（1）DML觸發(fā)器是在用戶使用數(shù)據(jù)操縱語觸發(fā)器是在用戶使用數(shù)據(jù)操縱語言（言（DML）事件編輯數(shù)據(jù)時(shí)發(fā)生。）事件編輯數(shù)據(jù)時(shí)發(fā)生。DML事件事件是針對(duì)表或視圖的是針對(duì)表或視圖的INSERT、UPDATE或或DELET操作。操作。DML觸發(fā)器有助于在表或視圖觸發(fā)器有助于在表或視圖中修改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(shù)據(jù)完整中修改數(shù)據(jù)時(shí)強(qiáng)制業(yè)務(wù)規(guī)則，擴(kuò)展數(shù)據(jù)完整性。性。DML觸發(fā)器又分為觸發(fā)器又分為AFTER觸發(fā)器和觸發(fā)器和INSTE

23、AD OF觸發(fā)器兩種。觸發(fā)器兩種。9.2.1 觸發(fā)器概述AFTER觸發(fā)器：觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)（這種類型的觸發(fā)器將在數(shù)據(jù)變動(dòng)（INSERT、UPDATE和和DELETE操作）完成以后才被觸操作）完成以后才被觸發(fā)?？梢詫?duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)發(fā)?？梢詫?duì)變動(dòng)的數(shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯(cuò)誤，將拒絕接受或回滾變動(dòng)的數(shù)據(jù)。錯(cuò)誤，將拒絕接受或回滾變動(dòng)的數(shù)據(jù)。AFTER觸發(fā)器只能在表上定義。在同一個(gè)數(shù)觸發(fā)器只能在表上定義。在同一個(gè)數(shù)據(jù)表中可以創(chuàng)建多個(gè)據(jù)表中可以創(chuàng)建多個(gè)AFTER觸發(fā)器。觸發(fā)器。9.2.1 觸發(fā)器概述INSTEAD OF觸發(fā)器觸發(fā)器:INSTEAD OF觸發(fā)器將在數(shù)據(jù)變動(dòng)以

24、前被觸觸發(fā)器將在數(shù)據(jù)變動(dòng)以前被觸發(fā)，并取代變動(dòng)數(shù)據(jù)的操作（發(fā)，并取代變動(dòng)數(shù)據(jù)的操作（INSERT、UPDATE和和DELETE操作），而去執(zhí)行觸發(fā)操作），而去執(zhí)行觸發(fā)器定義的操作。器定義的操作。INSTEAD OF觸發(fā)器可以在觸發(fā)器可以在表或視圖上定義。在表或視圖上，每個(gè)表或視圖上定義。在表或視圖上，每個(gè)INSERT、UPDATE和和DELETE語句最多可語句最多可以定義一個(gè)以定義一個(gè)INSTEAD OF觸發(fā)器。觸發(fā)器。9.2.1 觸發(fā)器概述（2）DDL觸發(fā)器用于響應(yīng)各種數(shù)據(jù)定義語言觸發(fā)器用于響應(yīng)各種數(shù)據(jù)定義語言（DDL）事件。這些事件主要對(duì)應(yīng)于）事件。這些事件主要對(duì)應(yīng)于T-SQL語言中的語言

25、中的CREATE、ALTER和和DROP操作，操作，以及執(zhí)行類似以及執(zhí)行類似DDL操作的某些系統(tǒng)存儲(chǔ)過程。操作的某些系統(tǒng)存儲(chǔ)過程。它們用于執(zhí)行管理任務(wù)，并強(qiáng)制影響數(shù)據(jù)庫它們用于執(zhí)行管理任務(wù)，并強(qiáng)制影響數(shù)據(jù)庫的業(yè)務(wù)規(guī)則。的業(yè)務(wù)規(guī)則。9.2.1 觸發(fā)器概述（3）登錄觸發(fā)器將為響應(yīng)）登錄觸發(fā)器將為響應(yīng)LOGON事件而激事件而激發(fā)存儲(chǔ)過程。與發(fā)存儲(chǔ)過程。與SQL Server實(shí)例建立用戶會(huì)實(shí)例建立用戶會(huì)話時(shí)將引發(fā)此事件。登錄觸發(fā)器將在登錄的話時(shí)將引發(fā)此事件。登錄觸發(fā)器將在登錄的身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立身份驗(yàn)證階段完成之后且用戶會(huì)話實(shí)際建立之前激發(fā)。因此，來自觸發(fā)器內(nèi)部且通常將之前激發(fā)。因

26、此，來自觸發(fā)器內(nèi)部且通常將到達(dá)用戶的所有消息（例如錯(cuò)誤消息和來自到達(dá)用戶的所有消息（例如錯(cuò)誤消息和來自PRINT語句的消息）會(huì)傳送到語句的消息）會(huì)傳送到SQL Server錯(cuò)錯(cuò)誤日志。如果身份驗(yàn)證失敗，將不激發(fā)登錄誤日志。如果身份驗(yàn)證失敗，將不激發(fā)登錄觸發(fā)器。觸發(fā)器。9.2.1 觸發(fā)器概述4兩個(gè)特殊的臨時(shí)表兩個(gè)特殊的臨時(shí)表DML觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表，它觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表，它們分別是們分別是inserted表和表和deleted表。表。這兩個(gè)表都存在于內(nèi)存中，它們?cè)诮Y(jié)構(gòu)上類這兩個(gè)表都存在于內(nèi)存中，它們?cè)诮Y(jié)構(gòu)上類似于定義了觸發(fā)器的表。似于定義了觸發(fā)器的表。在在inserted

27、表中存儲(chǔ)著被表中存儲(chǔ)著被INSERT和和UPDATE操作影響的新的數(shù)據(jù)行。操作影響的新的數(shù)據(jù)行。在在deleted表中存儲(chǔ)著被表中存儲(chǔ)著被DELETE和和UPDATE操作影響的舊數(shù)據(jù)行。操作影響的舊數(shù)據(jù)行。9.2.2 創(chuàng)建觸發(fā)器1使用使用SSMS創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定以下幾項(xiàng)內(nèi)容：當(dāng)創(chuàng)建一個(gè)觸發(fā)器時(shí)必須指定以下幾項(xiàng)內(nèi)容：觸發(fā)器的名稱；觸發(fā)器的名稱；在其上定義觸發(fā)器的表；在其上定義觸發(fā)器的表；觸發(fā)器將何時(shí)激發(fā)；觸發(fā)器將何時(shí)激發(fā)；執(zhí)行觸發(fā)操作的編程語句。執(zhí)行觸發(fā)操作的編程語句。9.2.2 創(chuàng)建觸發(fā)器1使用使用SSMS創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器在在SSMS中選擇指定的服

28、務(wù)器和數(shù)據(jù)庫，右中選擇指定的服務(wù)器和數(shù)據(jù)庫，右擊表中的擊表中的“觸發(fā)器觸發(fā)器”文件夾。從彈出的快捷菜文件夾。從彈出的快捷菜單中選擇單中選擇“新建觸發(fā)器新建觸發(fā)器”選項(xiàng)。選項(xiàng)。例例9-11：創(chuàng)建一個(gè)觸發(fā)器：創(chuàng)建一個(gè)觸發(fā)器“tr_Customer_Modify”，要求完成以下，要求完成以下功能：當(dāng)在功能：當(dāng)在Customer表中添加一條新記錄表中添加一條新記錄時(shí)，觸發(fā)該觸發(fā)器，并給出時(shí)，觸發(fā)該觸發(fā)器，并給出“你插入了一條新你插入了一條新記錄！記錄！”的提示信息。的提示信息。9.2.2 創(chuàng)建觸發(fā)器2使用使用T-SQL語句創(chuàng)建語句創(chuàng)建DML觸發(fā)器觸發(fā)器CREATE TRIGGER schema_nam

29、e.trigger_name ON table_name|view_name WITH ENCRYPTIONFOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS ;,.n9.2.2 創(chuàng)建觸發(fā)器其中，各參數(shù)的說明如下：其中，各參數(shù)的說明如下：schema_name：DML觸發(fā)器所屬架構(gòu)的名觸發(fā)器所屬架構(gòu)的名稱。稱。trigger_name：觸發(fā)器的名稱。觸發(fā)器的名稱。table | view：對(duì)其執(zhí)行對(duì)其執(zhí)行DML觸發(fā)器的表或視觸發(fā)器的表或視圖。圖。WITH ENCRYPTION：對(duì)對(duì)CREATE TRIGGER語句的文本進(jìn)行加密處理。語句的文本進(jìn)行加密處理。

30、9.2.2 創(chuàng)建觸發(fā)器FOR | AFTER：AFTER指定觸發(fā)器僅在觸發(fā)指定觸發(fā)器僅在觸發(fā)SQL語句中指定的所有操作都已成功執(zhí)行時(shí)語句中指定的所有操作都已成功執(zhí)行時(shí)才被觸發(fā)。不能對(duì)視圖定義才被觸發(fā)。不能對(duì)視圖定義AFTER觸發(fā)器。觸發(fā)器。INSTEAD OF：指定執(zhí)行指定執(zhí)行DML觸發(fā)器而不是觸發(fā)器而不是觸發(fā)觸發(fā)SQL語句。語句。DELETE , INSERT , UPDATE：指指定數(shù)據(jù)修改語句。必須至少指定一個(gè)選項(xiàng)。定數(shù)據(jù)修改語句。必須至少指定一個(gè)選項(xiàng)。sql_statement：觸發(fā)條件和操作。觸發(fā)條件和操作。9.2.2 創(chuàng)建觸發(fā)器例例9-12：創(chuàng)建一個(gè)：創(chuàng)建一個(gè)AFTER觸發(fā)器觸發(fā)器

31、tr_PostCheck，要求實(shí)現(xiàn)以下功能：當(dāng)在，要求實(shí)現(xiàn)以下功能：當(dāng)在Address表中添加或更新數(shù)據(jù)時(shí)，此觸發(fā)器表中添加或更新數(shù)據(jù)時(shí)，此觸發(fā)器將進(jìn)行檢查該地址的郵編字段是否超過將進(jìn)行檢查該地址的郵編字段是否超過10個(gè)個(gè)字符，如果未超過字符，如果未超過10個(gè)字符則顯示該地址的個(gè)字符則顯示該地址的第一地址、城市、省、國家和郵編，否則給第一地址、城市、省、國家和郵編，否則給出提示信息。出提示信息。9.2.2 創(chuàng)建觸發(fā)器例例9-13：創(chuàng)建一個(gè)：創(chuàng)建一個(gè)AFTER觸發(fā)器觸發(fā)器tr_Customer，要求實(shí)現(xiàn)以下功能：當(dāng)在，要求實(shí)現(xiàn)以下功能：當(dāng)在Customer表中刪除某一條記錄后，觸發(fā)該表中刪除某一

32、條記錄后，觸發(fā)該觸發(fā)器，在觸發(fā)器，在SalesOrderHeader表中刪除與表中刪除與此客戶相對(duì)應(yīng)的定單記錄。此客戶相對(duì)應(yīng)的定單記錄。例例9-14：創(chuàng)建一個(gè)：創(chuàng)建一個(gè)INSTEAD OF觸發(fā)器觸發(fā)器tr_NotAllowDelete，要求實(shí)現(xiàn)以下功能：當(dāng)，要求實(shí)現(xiàn)以下功能：當(dāng)在在ProductCategory表中刪除記錄時(shí)，觸發(fā)表中刪除記錄時(shí)，觸發(fā)該觸發(fā)器，顯示不允許刪除表中數(shù)據(jù)的提示該觸發(fā)器，顯示不允許刪除表中數(shù)據(jù)的提示信息。信息。10.2.2 使用Transact-SQL語句創(chuàng)建DML觸發(fā)器在在deleted表中存儲(chǔ)著被表中存儲(chǔ)著被DELETE和和UPDATE語句語句影響的舊數(shù)據(jù)行。在執(zhí)

33、行影響的舊數(shù)據(jù)行。在執(zhí)行DELETE或或UPDATE語句語句時(shí)，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到時(shí)，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到了了deleted表中。在基本表和表中。在基本表和deleted表中一般不會(huì)表中一般不會(huì)存在相同的數(shù)據(jù)行。存在相同的數(shù)據(jù)行。一個(gè)一個(gè)UPDATE操作實(shí)際上是由一個(gè)操作實(shí)際上是由一個(gè)DELETE操作和操作和一個(gè)一個(gè)INSERT操作組成的。在執(zhí)行操作組成的。在執(zhí)行UPDATE操作時(shí)，操作時(shí)，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后表中，然后將新的數(shù)據(jù)行同時(shí)插入基本表和將新的數(shù)據(jù)行同時(shí)插入基本表和inserted表中。

34、表中。9.2.3 查看觸發(fā)器1使用使用SSMS查看觸發(fā)器查看觸發(fā)器（1）查看觸發(fā)器定義信息）查看觸發(fā)器定義信息在在SSMS中，展開指定的服務(wù)器和數(shù)據(jù)庫，中，展開指定的服務(wù)器和數(shù)據(jù)庫，選擇指定的數(shù)據(jù)庫和表，單擊要查看的表，選擇指定的數(shù)據(jù)庫和表，單擊要查看的表，選擇其中的選擇其中的“觸發(fā)器觸發(fā)器”文件夾，就可以顯示文件夾，就可以顯示出此表中的所有觸發(fā)器，右擊某個(gè)觸發(fā)器名出此表中的所有觸發(fā)器，右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中選擇稱，從彈出的快捷菜單中選擇“修改修改”選項(xiàng)，選項(xiàng)，在打開的窗口中可以查看到定義觸發(fā)器的語在打開的窗口中可以查看到定義觸發(fā)器的語句。句。9.2.3 查看觸發(fā)器1使用使用S

35、SMS查看觸發(fā)器查看觸發(fā)器（2）查看與觸發(fā)器有依賴關(guān)系的其他數(shù)據(jù)庫）查看與觸發(fā)器有依賴關(guān)系的其他數(shù)據(jù)庫對(duì)象對(duì)象右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中右擊某個(gè)觸發(fā)器名稱，從彈出的快捷菜單中選擇選擇“查看依賴關(guān)系查看依賴關(guān)系”選項(xiàng)，在出現(xiàn)的選項(xiàng)，在出現(xiàn)的“對(duì)對(duì)象依賴關(guān)系象依賴關(guān)系”對(duì)話框中可以查看到依賴于此對(duì)話框中可以查看到依賴于此觸發(fā)器的對(duì)象和此觸發(fā)器依賴的對(duì)象。觸發(fā)器的對(duì)象和此觸發(fā)器依賴的對(duì)象。9.2.3 查看觸發(fā)器2使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器可以使用系統(tǒng)存儲(chǔ)過程可以使用系統(tǒng)存儲(chǔ)過程sp_help、sp_helptext和和sp_depends分別查看觸發(fā)器分別查看觸

36、發(fā)器的不同信息。具體方法同用戶定義存儲(chǔ)過程的不同信息。具體方法同用戶定義存儲(chǔ)過程的查看。的查看。 9.2.4 修改觸發(fā)器1使用使用SSMS修改觸發(fā)器修改觸發(fā)器在在SSMS中，右擊某個(gè)要修改的觸發(fā)器的名中，右擊某個(gè)要修改的觸發(fā)器的名稱，從彈出的快捷菜單中選擇稱，從彈出的快捷菜單中選擇“修改修改”選項(xiàng)，選項(xiàng)，在打開的窗口中可以修改觸發(fā)器的定義。在打開的窗口中可以修改觸發(fā)器的定義。9.2.4 修改觸發(fā)器2使用使用T-SQL語句修改觸發(fā)器語句修改觸發(fā)器ALTER TRIGGER schema_name.trigger_name ON table_name|view_name WITH ENCRYPTI

37、ONFOR|AFTER| NSTEAD OF INSERT,UPDATE,DELETE AS ;,.n9.2.4 修改觸發(fā)器例例9-15：修改觸發(fā)器：修改觸發(fā)器tr_Customer，要求實(shí)，要求實(shí)現(xiàn)以下功能：當(dāng)現(xiàn)以下功能：當(dāng)Customer表中數(shù)據(jù)發(fā)生修表中數(shù)據(jù)發(fā)生修改后，觸發(fā)該觸發(fā)器，計(jì)算修改的記錄條數(shù)改后，觸發(fā)該觸發(fā)器，計(jì)算修改的記錄條數(shù)并給出一條提示信息。并給出一條提示信息。9.2.5 刪除觸發(fā)器只有觸發(fā)器所有者才有權(quán)刪除觸發(fā)器，只有觸發(fā)器所有者才有權(quán)刪除觸發(fā)器，刪除已創(chuàng)建的觸發(fā)器常用的操作方法有刪除已創(chuàng)建的觸發(fā)器常用的操作方法有三種：三種：使用使用SSMS使用使用T-SQL語句語句直

38、接刪除觸發(fā)器所在的表直接刪除觸發(fā)器所在的表9.2.5 刪除觸發(fā)器1使用使用SSMS刪除觸發(fā)器刪除觸發(fā)器在打開的在打開的SSMS窗口中，選擇要?jiǎng)h除的觸發(fā)器，右窗口中，選擇要?jiǎng)h除的觸發(fā)器，右擊該觸發(fā)器名稱，從彈出的快捷菜單中選擇擊該觸發(fā)器名稱，從彈出的快捷菜單中選擇“刪除刪除”選項(xiàng)，會(huì)出現(xiàn)選項(xiàng)，會(huì)出現(xiàn)“刪除對(duì)象刪除對(duì)象”對(duì)話框。在對(duì)話框。在“刪除對(duì)象刪除對(duì)象”對(duì)話框中，單擊對(duì)話框中，單擊“確定確定”按鈕，即可刪除該視圖。按鈕，即可刪除該視圖。2使用使用T-SQL語句刪除觸發(fā)器語句刪除觸發(fā)器使用使用T-SQL中的中的DROP TRIGGER語句刪除指定的語句刪除指定的觸發(fā)器。其語法格式如下：觸發(fā)器。

39、其語法格式如下：DROP TRIGGER schema_name.trigger_name,.n;9.2.5 刪除觸發(fā)器例例9-16：使用：使用DROP TRIGGER命令刪除名命令刪除名為為tr_Customer的觸發(fā)器。的觸發(fā)器。3直接刪除觸發(fā)器所在的表直接刪除觸發(fā)器所在的表刪除觸發(fā)器的另一個(gè)方法是直接刪除觸發(fā)器刪除觸發(fā)器的另一個(gè)方法是直接刪除觸發(fā)器所在的表。所在的表。本章小結(jié)存儲(chǔ)過程定義存儲(chǔ)過程定義 存儲(chǔ)過程優(yōu)點(diǎn)存儲(chǔ)過程優(yōu)點(diǎn)存儲(chǔ)過程分類存儲(chǔ)過程分類系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程用戶定義的存儲(chǔ)過程用戶定義的存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程本章小結(jié)在在SQL Server 2008中創(chuàng)建存儲(chǔ)過程常

40、用兩種方法：中創(chuàng)建存儲(chǔ)過程常用兩種方法：使用使用SSMS使用使用T-SQL中的中的CREATE PROC語句語句CREATE PROC|PROCEDURE schema_name. procedure_name parameter type_schema_name.data_type VARYING=default OUT|OUTPUT READONLY ,.n WITH ENCRYPTION,RECOMPILE FOR REPLICATIONAS ;.n;本章小結(jié)執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程使用使用EXECUTE語句執(zhí)行：語句執(zhí)行： EXECUTE return_status= procedur

41、e_name|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE;本章小結(jié)查看存儲(chǔ)過程查看存儲(chǔ)過程使用使用SSMS使用系統(tǒng)存儲(chǔ)過程使用系統(tǒng)存儲(chǔ)過程修改存儲(chǔ)過程修改存儲(chǔ)過程使用使用SSMS使用使用T-SQL中的中的ALTER PROC語句語句刪除存儲(chǔ)過程刪除存儲(chǔ)過程使用使用SSMS使用使用T-SQL中的中的DROP PROC語句語句本章小結(jié)觸發(fā)器是一種特殊類型的觸發(fā)器是一種特殊類型的存儲(chǔ)過程存儲(chǔ)過程使用觸發(fā)器的有點(diǎn)使用觸發(fā)器的有點(diǎn)觸發(fā)器的分類觸發(fā)器的分類DML觸發(fā)器觸發(fā)器DDL觸發(fā)器觸發(fā)器登錄觸發(fā)

42、器登錄觸發(fā)器本章小結(jié)DML觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表：觸發(fā)器可以使用兩個(gè)特殊的臨時(shí)表：inserted表和表和deleted表。這兩個(gè)表都存在表。這兩個(gè)表都存在于內(nèi)存中，在結(jié)構(gòu)上類似于定義了觸發(fā)器于內(nèi)存中，在結(jié)構(gòu)上類似于定義了觸發(fā)器的表。在的表。在deleted表和表和inserted表中保存了表中保存了可能會(huì)被用戶更改的行的舊值或新值?？赡軙?huì)被用戶更改的行的舊值或新值。本章小結(jié)創(chuàng)建觸發(fā)器多的方法創(chuàng)建觸發(fā)器多的方法使用使用SSMS使用使用T-SQL中的中的CREATE TRIGGER語句語句 CREATE TRIGGER schema_name.trigger_name ON table_

43、name|view_name WITH ENCRYPTION FOR|AFTER|INSTEAD OF INSERT,UPDATE,DELETE AS ;,.n本章小結(jié)查看觸發(fā)器查看觸發(fā)器修改觸發(fā)器修改觸發(fā)器刪除觸發(fā)器刪除觸發(fā)器習(xí)題九一、填空題一、填空題1使用使用T-SQL語句創(chuàng)建存儲(chǔ)過程時(shí)，使用語句創(chuàng)建存儲(chǔ)過程時(shí)，使用 來指示該參數(shù)為輸出參數(shù)，使用來指示該參數(shù)為輸出參數(shù)，使用 來指來指示示SQL Server對(duì)該存儲(chǔ)過程的文本進(jìn)行加密。對(duì)該存儲(chǔ)過程的文本進(jìn)行加密。2執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字是執(zhí)行存儲(chǔ)過程的命令關(guān)鍵字是EXECUTE，如果此語句是批處理中的第一條語句，可如果此語句是批處理中的第一條語句，可以以 。習(xí)題九一、填空