第6章存儲(chǔ)過(guò)程與觸發(fā)器

1、中南大學(xué)信息科學(xué)與工程學(xué)院數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用教材編寫(xiě)組數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用教材編寫(xiě)組數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用本章內(nèi)容重點(diǎn)難點(diǎn)第六章第六章 存儲(chǔ)過(guò)程與觸發(fā)器存儲(chǔ)過(guò)程與觸發(fā)器存儲(chǔ)過(guò)程的基本概念存儲(chǔ)過(guò)程的基本概念存儲(chǔ)過(guò)程的特點(diǎn)與作用存儲(chǔ)過(guò)程的特點(diǎn)與作用觸發(fā)器的基本概念觸發(fā)器的基本概念觸發(fā)器的特點(diǎn)與作用觸發(fā)器的特點(diǎn)與作用存儲(chǔ)過(guò)程創(chuàng)建、執(zhí)行以及參數(shù)應(yīng)用的方法存儲(chǔ)過(guò)程創(chuàng)建、執(zhí)行以及參數(shù)應(yīng)用的方法觸發(fā)器的創(chuàng)建及使用方法觸發(fā)器的創(chuàng)建及使用方法存儲(chǔ)過(guò)程的參數(shù)應(yīng)用方法存儲(chǔ)過(guò)程的參數(shù)應(yīng)用方法2數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用問(wèn)題提出問(wèn)題提出為什么需要存儲(chǔ)過(guò)程？存儲(chǔ)過(guò)程是什么？為什么需要存儲(chǔ)過(guò)程？存儲(chǔ)過(guò)程是什么？為

2、什么要觸發(fā)器？觸發(fā)器是什么？為什么要觸發(fā)器？觸發(fā)器是什么？3數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1 6.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的刪除存儲(chǔ)過(guò)程的刪除4數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.1 6.1.1 存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的特點(diǎn)存儲(chǔ)過(guò)程的特點(diǎn)存儲(chǔ)過(guò)程是在服務(wù)器端運(yùn)行，執(zhí)行速度快，方便用戶查詢(xún)存儲(chǔ)過(guò)程是在服務(wù)器端運(yùn)行，執(zhí)行速度快，方便用戶查詢(xún)，能有效提高數(shù)據(jù)使用效率。，能有效提高數(shù)據(jù)使用效率。封裝復(fù)雜操作封裝復(fù)雜操作加快系

3、統(tǒng)運(yùn)行速度加快系統(tǒng)運(yùn)行速度實(shí)現(xiàn)代碼重用實(shí)現(xiàn)代碼重用增強(qiáng)安全性增強(qiáng)安全性減少網(wǎng)絡(luò)流量減少網(wǎng)絡(luò)流量調(diào)用方便調(diào)用方便5數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.1 6.1.1 存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的特點(diǎn)和類(lèi)型存儲(chǔ)過(guò)程的類(lèi)型存儲(chǔ)過(guò)程的類(lèi)型 SQL Server 2008 SQL Server 2008中常用的存儲(chǔ)過(guò)程類(lèi)型有中常用的存儲(chǔ)過(guò)程類(lèi)型有3種：種：系統(tǒng)存儲(chǔ)過(guò)程（系統(tǒng)存儲(chǔ)過(guò)程（sp_sp_）: :由數(shù)據(jù)庫(kù)系統(tǒng)自身創(chuàng)建，存儲(chǔ)在由數(shù)據(jù)庫(kù)系統(tǒng)自身創(chuàng)建，存儲(chǔ)在mastermaster數(shù)據(jù)庫(kù)中，以數(shù)據(jù)庫(kù)中，以“sp_sp_” ” 前綴標(biāo)識(shí)前綴標(biāo)識(shí)用戶定義存儲(chǔ)過(guò)程（本地存儲(chǔ)過(guò)程）用戶定義存儲(chǔ)過(guò)程（本地存儲(chǔ)過(guò)程）: :

4、在用戶數(shù)據(jù)庫(kù)中由用戶創(chuàng)建。在用戶數(shù)據(jù)庫(kù)中由用戶創(chuàng)建。 臨時(shí)存儲(chǔ)過(guò)程：臨時(shí)存儲(chǔ)過(guò)程：可以是局部的，名稱(chēng)以可以是局部的，名稱(chēng)以“# #”開(kāi)頭；也可以是全局的，名開(kāi)頭；也可以是全局的，名稱(chēng)以稱(chēng)以“#”#”開(kāi)頭。開(kāi)頭。擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程: :以動(dòng)態(tài)鏈接庫(kù)（以動(dòng)態(tài)鏈接庫(kù)（DLLDLL）的形式實(shí)現(xiàn)。以）的形式實(shí)現(xiàn)。以“xp_xp_”為前綴，只能添加到為前綴，只能添加到mastermaster數(shù)據(jù)庫(kù)中，使用方法與系統(tǒng)存儲(chǔ)過(guò)程一樣。數(shù)據(jù)庫(kù)中，使用方法與系統(tǒng)存儲(chǔ)過(guò)程一樣。6數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行7數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)

5、建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行創(chuàng)建存儲(chǔ)過(guò)程實(shí)際是對(duì)存儲(chǔ)過(guò)程進(jìn)行定義的過(guò)程，創(chuàng)建存儲(chǔ)過(guò)程實(shí)際是對(duì)存儲(chǔ)過(guò)程進(jìn)行定義的過(guò)程，主要包含：主要包含：存儲(chǔ)過(guò)程名稱(chēng)及其參數(shù)的說(shuō)明和存儲(chǔ)過(guò)程的主體存儲(chǔ)過(guò)程名稱(chēng)及其參數(shù)的說(shuō)明和存儲(chǔ)過(guò)程的主體（包含執(zhí)行過(guò)程操作的（包含執(zhí)行過(guò)程操作的 T-SQL T-SQL 語(yǔ)句）兩部分。語(yǔ)句）兩部分?？梢允褂每梢允褂? 3種方法創(chuàng)建存儲(chǔ)過(guò)程：種方法創(chuàng)建存儲(chǔ)過(guò)程： 使用圖形工具使用圖形工具使用向?qū)褂孟驅(qū)褂檬褂肨ransact-SQLTransact-SQL語(yǔ)言中的語(yǔ)言中的CREATE PROCEDURECREATE PROCEDURE語(yǔ)句語(yǔ)句 8數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1

6、.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行使用圖形工具創(chuàng)建存儲(chǔ)過(guò)程使用圖形工具創(chuàng)建存儲(chǔ)過(guò)程9數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行使用使用CREATE PROCEDURECREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程 語(yǔ)法格式如下：語(yǔ)法格式如下： CREATE PROCEDURE schema_name. procedure_name ; number parameter schema_name.data_type VARYING=default OUTPUT WITH RECOMPILE | ENCRYPTION | RECOM

7、PILE, ENCRYPTION FOR REPLICATION AS sql_statement .n10 注意事項(xiàng)：注意事項(xiàng)：只能在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程只能在本地?cái)?shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程 ；可以引用在同一存儲(chǔ)過(guò)程中創(chuàng)建的對(duì)象，只要引用時(shí)已經(jīng)創(chuàng)建了；可以引用在同一存儲(chǔ)過(guò)程中創(chuàng)建的對(duì)象，只要引用時(shí)已經(jīng)創(chuàng)建了該對(duì)象即可該對(duì)象即可 可以在存儲(chǔ)過(guò)程內(nèi)引用臨時(shí)表，如果在存儲(chǔ)過(guò)程內(nèi)創(chuàng)建本地臨時(shí)表，則臨時(shí)表僅為該存儲(chǔ)過(guò)程而存在可以在存儲(chǔ)過(guò)程內(nèi)引用臨時(shí)表，如果在存儲(chǔ)過(guò)程內(nèi)創(chuàng)建本地臨時(shí)表，則臨時(shí)表僅為該存儲(chǔ)過(guò)程而存在；退出該存儲(chǔ)過(guò)程后，臨時(shí)表將消失；退出該存儲(chǔ)過(guò)程后，臨時(shí)表將消失 根據(jù)可用內(nèi)存的不同，存儲(chǔ)過(guò)程

8、最大可達(dá)根據(jù)可用內(nèi)存的不同，存儲(chǔ)過(guò)程最大可達(dá)128MB 數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行【例例6.1】在在Student_db數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為數(shù)據(jù)庫(kù)中創(chuàng)建一個(gè)名為p_Stu的存的存儲(chǔ)過(guò)程，它將從表中返回所有學(xué)生的姓名、性別、班級(jí)、電儲(chǔ)過(guò)程，它將從表中返回所有學(xué)生的姓名、性別、班級(jí)、電話。話。 存儲(chǔ)過(guò)程只能建立在當(dāng)前數(shù)據(jù)庫(kù)上，故需先用存儲(chǔ)過(guò)程只能建立在當(dāng)前數(shù)據(jù)庫(kù)上，故需先用USE語(yǔ)句來(lái)指定數(shù)據(jù)庫(kù)語(yǔ)句來(lái)指定數(shù)據(jù)庫(kù) USE Student_db Go 存儲(chǔ)過(guò)程的內(nèi)容如下：存儲(chǔ)過(guò)程的內(nèi)容如下： CREATE PROCEDURE p_Stu AS SE

9、LECT St_ID,St_Sex,Cl_Name,Telephone FROM St_Info 以存儲(chǔ)過(guò)程是從單個(gè)表中提取數(shù)據(jù)，最終返回了學(xué)生的簡(jiǎn)明信息。以存儲(chǔ)過(guò)程是從單個(gè)表中提取數(shù)據(jù)，最終返回了學(xué)生的簡(jiǎn)明信息。11數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行【例例6.2】創(chuàng)建一個(gè)帶創(chuàng)建一個(gè)帶SELECT查詢(xún)語(yǔ)句的名為查詢(xún)語(yǔ)句的名為“Average_Score”的存儲(chǔ)的存儲(chǔ)過(guò)程。從學(xué)生表、課程表、選課表返回每位修課學(xué)生的課程平均分。過(guò)程。從學(xué)生表、課程表、選課表返回每位修課學(xué)生的課程平均分。 分析：分析：學(xué)生表與選課表通過(guò)學(xué)生表與選課表通過(guò)“St_ID”關(guān)

10、聯(lián)，關(guān)聯(lián)，課程表與選課表通過(guò)課程表與選課表通過(guò)“C_No”關(guān)聯(lián)，關(guān)聯(lián)，要查到每個(gè)學(xué)生的修課平均分，需要通過(guò)聚集函數(shù)要查到每個(gè)學(xué)生的修課平均分，需要通過(guò)聚集函數(shù)AVG計(jì)算，計(jì)算，因?yàn)橐昧司奂瘮?shù)，因?yàn)橐昧司奂瘮?shù)，SELECT查詢(xún)中必須使用查詢(xún)中必須使用GROUP BY分組選項(xiàng)。分組選項(xiàng)。 12 USE Student_db GO CREATE PROCEDURE Average_Score AS SELECT St_Info.St_Name, AVG(S_C_Info.Score) AS AvgScore FROM St_Info, S_C_Info, C_Info WHERE St_In

11、fo.St_ID = S_C_Info.St_ID AND S_C_Info.C_No =C_Info.C_NoGROUP BY St_Info.St_Name GO數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程的創(chuàng)建和執(zhí)行使用使用EXECUTEEXECUTE（或（或EXECEXEC）命令執(zhí)行存儲(chǔ)過(guò)程）命令執(zhí)行存儲(chǔ)過(guò)程 語(yǔ)法格式如下：語(yǔ)法格式如下： EXECUTE return_status= procedure_name ;number|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH

12、 RECOMPILE 注意事項(xiàng)：注意事項(xiàng)：執(zhí)行存儲(chǔ)過(guò)程必須具有執(zhí)行該過(guò)程的權(quán)限許可執(zhí)行存儲(chǔ)過(guò)程必須具有執(zhí)行該過(guò)程的權(quán)限許可如果存儲(chǔ)過(guò)程是批處理中的第一條語(yǔ)句，如果存儲(chǔ)過(guò)程是批處理中的第一條語(yǔ)句，EXECUTE命令可以省略命令可以省略存儲(chǔ)過(guò)程的最大大小這存儲(chǔ)過(guò)程的最大大小這128MB13數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的執(zhí)行存儲(chǔ)過(guò)程的執(zhí)行例例6.3執(zhí)行例執(zhí)行例【例例6.2】所創(chuàng)建的存儲(chǔ)過(guò)程所創(chuàng)建的存儲(chǔ)過(guò)程Average_Score。 新建查詢(xún)，在新建查詢(xún)，在“查詢(xún)?cè)O(shè)計(jì)器查詢(xún)?cè)O(shè)計(jì)器”中輸入并運(yùn)行以下語(yǔ)句：中輸入并運(yùn)行以下語(yǔ)句： USE Student_db GO EXECUTE A

13、verage_Score 輸出結(jié)果如右圖所示。輸出結(jié)果如右圖所示。 14數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.2 6.1.2 存儲(chǔ)過(guò)程的執(zhí)行存儲(chǔ)過(guò)程的執(zhí)行使用對(duì)象資源管理器中執(zhí)行存儲(chǔ)過(guò)程，操作方法如下：使用對(duì)象資源管理器中執(zhí)行存儲(chǔ)過(guò)程，操作方法如下： 15數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)存儲(chǔ)過(guò)程參數(shù)類(lèi)型類(lèi)型有：有：“輸入輸入”和和“輸出輸出”參數(shù)參數(shù)（1 1）輸入?yún)?shù)）輸入?yún)?shù)定義存儲(chǔ)過(guò)程時(shí)，可指定輸入?yún)?shù)，以定義存儲(chǔ)過(guò)程時(shí)，可指定輸入?yún)?shù)，以 作為參數(shù)名稱(chēng)的作為參數(shù)名稱(chēng)的前置字符，聲明若干個(gè)參數(shù)變量及其數(shù)據(jù)類(lèi)型，一個(gè)存儲(chǔ)前置字符，聲明若干個(gè)參

14、數(shù)變量及其數(shù)據(jù)類(lèi)型，一個(gè)存儲(chǔ)過(guò)程最多指定過(guò)程最多指定10241024個(gè)參數(shù)。個(gè)參數(shù)。（2 2）輸出參數(shù)）輸出參數(shù)如果要在存儲(chǔ)過(guò)程中傳回值給調(diào)用者，可在參數(shù)名稱(chēng)后使如果要在存儲(chǔ)過(guò)程中傳回值給調(diào)用者，可在參數(shù)名稱(chēng)后使用用OUTPUTOUTPUT 關(guān)鍵詞。關(guān)鍵詞。同時(shí)，為了使用輸出參數(shù)，必須在創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程時(shí)同時(shí)，為了使用輸出參數(shù)，必須在創(chuàng)建和執(zhí)行存儲(chǔ)過(guò)程時(shí)都使用都使用OUTPUTOUTPUT關(guān)鍵詞。關(guān)鍵詞。16數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)【例例6.4】創(chuàng)建一個(gè)帶兩個(gè)參數(shù)的存儲(chǔ)過(guò)程，從創(chuàng)建一個(gè)帶兩個(gè)參數(shù)的存儲(chǔ)過(guò)程，從St_Info、C_In

15、fo、S_C_Info表的相關(guān)聯(lián)接中返回輸入?yún)?shù)的學(xué)生姓名和課程類(lèi)別、該學(xué)生表的相關(guān)聯(lián)接中返回輸入?yún)?shù)的學(xué)生姓名和課程類(lèi)別、該學(xué)生選課的課程名稱(chēng)和成績(jī)。選課的課程名稱(chēng)和成績(jī)。 CREATE PROCEDURE ScoreInfo stname varchar(20), ctype char(4) AS SELECT St_Info.St_Name, C_Info.C_Type, C_Info.C_Name, S_C_Info.Score FROM St_Info, S_C_Info, C_Info WHERE St_Info.St_ID = S_C_Info.St_ID AND S_C_Inf

16、o.C_No = C_Info.C_No AND St_Info.St_Name = stname AND C_Info.C_Type = ctype在在“新建查詢(xún)新建查詢(xún)”窗格中輸入并運(yùn)行如下命令：窗格中輸入并運(yùn)行如下命令： EXEC ScoreInfo 吳中華吳中華,必修必修 輸出結(jié)果如右圖所示。輸出結(jié)果如右圖所示。17數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)兩種傳遞參數(shù)的方式：兩種傳遞參數(shù)的方式：位置標(biāo)識(shí)位置標(biāo)識(shí)和和名字標(biāo)識(shí)名字標(biāo)識(shí)。位置標(biāo)識(shí)傳遞參數(shù)位置標(biāo)識(shí)傳遞參數(shù)只按順序提供值只按順序提供值參數(shù)值必須以參數(shù)的定義順序列出參數(shù)值必須以參數(shù)的定義

17、順序列出可以忽略有默認(rèn)值的參數(shù)，但不能中斷次序可以忽略有默認(rèn)值的參數(shù)，但不能中斷次序例：例：EXEC ScoreInfo 吳中華吳中華,必修必修 名字標(biāo)識(shí)傳遞值名字標(biāo)識(shí)傳遞值在調(diào)用語(yǔ)句中以在調(diào)用語(yǔ)句中以“參數(shù)名參數(shù)名=值值”的格式指定參數(shù)的格式指定參數(shù)當(dāng)通過(guò)參數(shù)名傳遞值時(shí)，可以以任何順序指定參數(shù)值，并且可省略當(dāng)通過(guò)參數(shù)名傳遞值時(shí)，可以以任何順序指定參數(shù)值，并且可省略允許空值或具有默認(rèn)值允許空值或具有默認(rèn)值 的參數(shù)的參數(shù)若在若在“新建查詢(xún)新建查詢(xún)”窗格中輸入并運(yùn)行如下命令：窗格中輸入并運(yùn)行如下命令： EXEC ScoreInfo ctype=必修必修, stname=吳中華吳中華 EXEC Sc

18、oreInfo ctype = 必修必修,stname = 楊平娟楊平娟18數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)- -舉例舉例【例例6.5】創(chuàng)建帶一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程，通過(guò)輸入?yún)?shù)創(chuàng)建帶一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)的存儲(chǔ)過(guò)程，通過(guò)輸入?yún)?shù)在在 St_Info表中查詢(xún)指定學(xué)號(hào)的學(xué)生，以輸出參數(shù)的形式返回學(xué)生所在的表中查詢(xún)指定學(xué)號(hào)的學(xué)生，以輸出參數(shù)的形式返回學(xué)生所在的班級(jí)名稱(chēng)（班級(jí)名稱(chēng)（Cl_Name字段）。字段）。 創(chuàng)建此存儲(chǔ)過(guò)程的語(yǔ)句如下：創(chuàng)建此存儲(chǔ)過(guò)程的語(yǔ)句如下： CREATE PROCEDURE StClass stid cha

19、r(10), class_name char(20) OUTPUT ASSELECT class_name = cl_name FROM St_Info WHERE St_Info.St_ID = stid 執(zhí)行該存儲(chǔ)過(guò)程的語(yǔ)句如下：執(zhí)行該存儲(chǔ)過(guò)程的語(yǔ)句如下： DECLARE get_clname char(20) EXEC StClass 0603060109, get_clname OUTPUT SELECT get_clname注意：變量被聲明，其值會(huì)先被設(shè)為注意：變量被聲明，其值會(huì)先被設(shè)為NULL。19數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)

20、返回存儲(chǔ)過(guò)程狀態(tài)返回存儲(chǔ)過(guò)程狀態(tài)為了增強(qiáng)存儲(chǔ)過(guò)程的效率，應(yīng)使用錯(cuò)誤信息向用戶傳達(dá)事為了增強(qiáng)存儲(chǔ)過(guò)程的效率，應(yīng)使用錯(cuò)誤信息向用戶傳達(dá)事務(wù)狀態(tài)（成功或失?。?。務(wù)狀態(tài)（成功或失?。?。RETURNRETURN語(yǔ)句語(yǔ)句從查詢(xún)或存儲(chǔ)過(guò)程無(wú)條件返回，同時(shí)可以返回一個(gè)整數(shù)狀從查詢(xún)或存儲(chǔ)過(guò)程無(wú)條件返回，同時(shí)可以返回一個(gè)整數(shù)狀態(tài)值（返回碼）態(tài)值（返回碼）返回碼為返回碼為0 0表示執(zhí)行成功，表示執(zhí)行成功，返回返回-1-1-99-99之間的整數(shù)，表示之間的整數(shù)，表示執(zhí)行失敗。執(zhí)行失敗。20數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)【例【例6.6】修改修改【例例6.5】示例中的

21、存儲(chǔ)過(guò)程，分示例中的存儲(chǔ)過(guò)程，分3種情況返回不同的執(zhí)種情況返回不同的執(zhí)行狀態(tài)：如果輸入空的學(xué)號(hào)參數(shù)值，則返回執(zhí)行狀態(tài)行狀態(tài)：如果輸入空的學(xué)號(hào)參數(shù)值，則返回執(zhí)行狀態(tài)“-1”；如果在；如果在St_Info表中不存在指定學(xué)號(hào)的學(xué)生，則返回執(zhí)行狀態(tài)表中不存在指定學(xué)號(hào)的學(xué)生，則返回執(zhí)行狀態(tài)“-2”；除前兩種；除前兩種情況之外（即找到了指定學(xué)號(hào)的學(xué)生），則返回執(zhí)行狀態(tài)情況之外（即找到了指定學(xué)號(hào)的學(xué)生），則返回執(zhí)行狀態(tài)“0”表示執(zhí)行表示執(zhí)行正常正常。修改此存儲(chǔ)過(guò)程的語(yǔ)句如下：修改此存儲(chǔ)過(guò)程的語(yǔ)句如下： CREATE CREATE PROCEDURE PROCEDURE StClass_newStClass_

22、new stidstid char(10) = NULL , char(10) = NULL , class_nameclass_name char(20) char(20) OUTPUTOUTPUT AS AS IF IF stidstid IS NULL IS NULL RETURN -1 RETURN -1 -IF-IF或或ELSEELSE條件只能影響一個(gè)條件只能影響一個(gè)SQLSQL語(yǔ)句（語(yǔ)句（p39p39） SELECT SELECT class_nameclass_name = = cl_namecl_name FROM FROM St_InfoSt_Info WHERE WHERE

23、 St_Info.St_IDSt_Info.St_ID = = stidstid IF IF class_nameclass_name IS IS NULLNULL RETURN -2RETURN -2 RETURN 0 RETURN 021數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.3 6.1.3 存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)存儲(chǔ)過(guò)程參數(shù)和執(zhí)行狀態(tài)正確接收返回的正確接收返回的狀態(tài)的存儲(chǔ)過(guò)程執(zhí)行語(yǔ)句狀態(tài)的存儲(chǔ)過(guò)程執(zhí)行語(yǔ)句形式：形式： EXEC EXEC status_varstatus_var = = 過(guò)程名稱(chēng)過(guò)程名稱(chēng)調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程，根據(jù)返回碼進(jìn)行輸出調(diào)用帶輸出參數(shù)的存儲(chǔ)過(guò)程，根據(jù)返回碼進(jìn)行輸出【例【例6.6

24、6.6】的執(zhí)行過(guò)程：的執(zhí)行過(guò)程： DECLARE DECLARE status_returnstatus_return intint DECLARE DECLARE get_clnameget_clname char(20 char(20) ) EXEC EXEC status_returnstatus_return = = StClass_newStClass_new 0603060109 0603060109, , get_clnameget_clname OUTPUTOUTPUT IF IF status_returnstatus_return = -1 = -1 PRINT PRINT

25、 沒(méi)有輸入學(xué)號(hào)沒(méi)有輸入學(xué)號(hào) ELSE ELSE IF IF status_returnstatus_return = -2 = -2PRINT PRINT 找不到這個(gè)學(xué)號(hào)的學(xué)生找不到這個(gè)學(xué)號(hào)的學(xué)生 ELSE ELSEPRINT PRINT get_clnameget_clname 22數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.4 6.1.4 存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的查看和修改使用對(duì)象資源管理器查看或修改存儲(chǔ)過(guò)程使用對(duì)象資源管理器查看或修改存儲(chǔ)過(guò)程23數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.4 6.1.4 存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的查看和修改使用系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程使用系統(tǒng)存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程24系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存

26、儲(chǔ)過(guò)程作作 用用使用語(yǔ)法使用語(yǔ)法sp_helptextsp_helptext查看存儲(chǔ)過(guò)程的文本信息查看存儲(chǔ)過(guò)程的文本信息sp_helptext objname= sp_helptext objname= 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名sp_dependssp_depends查看存儲(chǔ)過(guò)程的相關(guān)性查看存儲(chǔ)過(guò)程的相關(guān)性sp_depends objname= sp_depends objname= 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名sp_helpsp_help查看存儲(chǔ)過(guò)程的一般信息查看存儲(chǔ)過(guò)程的一般信息sp_helpsp_help objnameobjname= = 存儲(chǔ)過(guò)程名存儲(chǔ)過(guò)程名數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.4 6.1.4

27、 存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的查看和修改使用使用ALTER PROCEDUREALTER PROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程語(yǔ)句修改存儲(chǔ)過(guò)程語(yǔ)法格式：語(yǔ)法格式：ALTER PROCEDUREALTER PROCEDURE schema_nameschema_name. . procedure_nameprocedure_name;number;numberparameter parameter data_typedata_type VARYING=defaultOUTPUT,.nVARYING=defaultOUTPUT,.nWITH RECOMPILE|ENCRYPTION|RECOMPI

28、LE,ENCRYPTIONWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPLICATIONFOR REPLICATIONAS AS sql_statementsql_statement ,.n ,.n 參數(shù)參數(shù)和保留字的含義說(shuō)明與和保留字的含義說(shuō)明與CREATE PROCEDURE語(yǔ)句一致語(yǔ)句一致。25數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.4 6.1.4 存儲(chǔ)過(guò)程的查看和修改存儲(chǔ)過(guò)程的查看和修改重命名存儲(chǔ)過(guò)程重命名存儲(chǔ)過(guò)程可可使用系統(tǒng)存儲(chǔ)過(guò)程使用系統(tǒng)存儲(chǔ)過(guò)程sp_renamesp_rename，語(yǔ)法格式：，語(yǔ)法格式： sp_renamesp_rena

29、me stored procedure stored procedure object_nameobject_name, , stored procedure stored procedure new_namenew_name 【例例6.86.8】 將將【例例6.16.1】創(chuàng)建的存儲(chǔ)過(guò)程創(chuàng)建的存儲(chǔ)過(guò)程p_Stup_Stu更名為更名為Student_procStudent_proc。 完成操作的完成操作的語(yǔ)句：語(yǔ)句： sp_renamesp_rename p_Stup_Stu, , Student_procStudent_proc 注意：通過(guò)對(duì)象資源管理器也可以修改存儲(chǔ)過(guò)程的名稱(chēng)注意：通過(guò)對(duì)象資

30、源管理器也可以修改存儲(chǔ)過(guò)程的名稱(chēng)26存儲(chǔ)過(guò)程老名稱(chēng)存儲(chǔ)過(guò)程老名稱(chēng)存儲(chǔ)過(guò)程新名稱(chēng)存儲(chǔ)過(guò)程新名稱(chēng)數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.5 6.1.5 存儲(chǔ)過(guò)程的刪除存儲(chǔ)過(guò)程的刪除刪除刪除存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程使用使用DROP PROCEDUREDROP PROCEDURE語(yǔ)句從當(dāng)前數(shù)據(jù)庫(kù)中移除用戶定義語(yǔ)句從當(dāng)前數(shù)據(jù)庫(kù)中移除用戶定義存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程語(yǔ)法格式：語(yǔ)法格式：DROP DROP PROCEDURE PROCEDURE procedure_nameprocedure_name ,.n ,.n 刪除存儲(chǔ)過(guò)程的注意事項(xiàng)刪除存儲(chǔ)過(guò)程的注意事項(xiàng)在刪除存儲(chǔ)過(guò)程之前，執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程在刪除存儲(chǔ)過(guò)程之前，執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_

31、dependssp_depends檢查檢查是否有對(duì)象依賴(lài)此存儲(chǔ)過(guò)程。是否有對(duì)象依賴(lài)此存儲(chǔ)過(guò)程。27數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.1.5 6.1.5 存儲(chǔ)過(guò)程的刪除存儲(chǔ)過(guò)程的刪除【例例6.106.10】刪除例刪除例6.26.2所創(chuàng)建的存儲(chǔ)過(guò)程所創(chuàng)建的存儲(chǔ)過(guò)程Average_ScoreAverage_Score。完成操作的語(yǔ)句：完成操作的語(yǔ)句：USE student_dbUSE student_dbGO GO IF EXISTS ( IF EXISTS ( SELECT name FROM SELECT name FROM sysobjectssysobjects WHERE name=Average_Sc

32、ore WHERE name=Average_Score) )DROP DROP PROCEDURE PROCEDURE Average_ScoreAverage_Score注意：注意：不論是重命名存儲(chǔ)過(guò)程名稱(chēng)還是刪除了存儲(chǔ)過(guò)程，都會(huì)影響到引用該不論是重命名存儲(chǔ)過(guò)程名稱(chēng)還是刪除了存儲(chǔ)過(guò)程，都會(huì)影響到引用該存儲(chǔ)過(guò)程的其他數(shù)據(jù)庫(kù)對(duì)象。存儲(chǔ)過(guò)程的其他數(shù)據(jù)庫(kù)對(duì)象。28sysobjects sysobjects 系統(tǒng)對(duì)象表。系統(tǒng)對(duì)象表。 保保存當(dāng)前數(shù)據(jù)庫(kù)的對(duì)象，如約存當(dāng)前數(shù)據(jù)庫(kù)的對(duì)象，如約束、默認(rèn)值、日志、規(guī)則、束、默認(rèn)值、日志、規(guī)則、存儲(chǔ)過(guò)程等存儲(chǔ)過(guò)程等數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2 6.2 觸發(fā)器概述觸發(fā)器

33、概述觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建觸發(fā)器的查看和修改觸發(fā)器的查看和修改觸發(fā)器的刪除觸發(fā)器的刪除29數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.1 6.2.1 觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器（觸發(fā)器（triggertrigger）是是SQL ServerSQL Server數(shù)據(jù)庫(kù)中一種數(shù)據(jù)庫(kù)中一種特殊特殊類(lèi)型的類(lèi)型的存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程，不能不能由由用戶直接調(diào)用用戶直接調(diào)用，而且可以包含復(fù)雜的，而且可以包含復(fù)雜的T-SQLT-SQL語(yǔ)句。它是一語(yǔ)句。它是一個(gè)在修改指定表中的數(shù)據(jù)時(shí)執(zhí)行的存儲(chǔ)過(guò)程。用戶可以用個(gè)在修改指定表中的數(shù)據(jù)時(shí)執(zhí)行的存儲(chǔ)過(guò)程。用戶可以用它來(lái)強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)

34、則，以此確保數(shù)據(jù)的完整性。它來(lái)強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)則，以此確保數(shù)據(jù)的完整性。30數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.1 6.2.1 觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)觸發(fā)器的特點(diǎn)觸發(fā)器與觸發(fā)器與表緊密相連表緊密相連，可以看作表定義的一部分。，可以看作表定義的一部分。觸發(fā)器是基于一個(gè)表創(chuàng)建的，但是可以針對(duì)多個(gè)表進(jìn)行操作，實(shí)現(xiàn)觸發(fā)器是基于一個(gè)表創(chuàng)建的，但是可以針對(duì)多個(gè)表進(jìn)行操作，實(shí)現(xiàn)數(shù)據(jù)庫(kù)中相關(guān)表的級(jí)聯(lián)更改。數(shù)據(jù)庫(kù)中相關(guān)表的級(jí)聯(lián)更改。觸發(fā)器觸發(fā)器不能通過(guò)名稱(chēng)被直接調(diào)用不能通過(guò)名稱(chēng)被直接調(diào)用，更不允許帶參數(shù)，而是當(dāng)用戶對(duì)，更不允許帶參數(shù)，而是當(dāng)用戶對(duì)表中的數(shù)據(jù)進(jìn)行修改這樣的事件發(fā)生時(shí)，自動(dòng)執(zhí)行的行

35、為。表中的數(shù)據(jù)進(jìn)行修改這樣的事件發(fā)生時(shí)，自動(dòng)執(zhí)行的行為。觸發(fā)器可以觸發(fā)器可以用于用于SQL ServerSQL Server約束、默認(rèn)值和規(guī)則的完整性檢查約束、默認(rèn)值和規(guī)則的完整性檢查，實(shí)，實(shí)施更為復(fù)雜的數(shù)據(jù)完整性約束。施更為復(fù)雜的數(shù)據(jù)完整性約束。觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對(duì)策。觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對(duì)策。一個(gè)表中可以存在多個(gè)同類(lèi)觸發(fā)器一個(gè)表中可以存在多個(gè)同類(lèi)觸發(fā)器（INSERTINSERT、UPDATEUPDATE或或DELETEDELETE），對(duì)于同一個(gè)修改語(yǔ)句可以有多個(gè)不同的對(duì)策用以響應(yīng)。，對(duì)于同一個(gè)修改語(yǔ)句可以有多個(gè)不同的對(duì)策用以

36、響應(yīng)。31數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.1 6.2.1 觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的類(lèi)型觸發(fā)器的類(lèi)型按按觸發(fā)事件觸發(fā)事件不同分為不同分為2 2類(lèi)類(lèi)（1 1）DDLDDL（數(shù)據(jù)定義語(yǔ)言）觸發(fā)器（數(shù)據(jù)定義語(yǔ)言）觸發(fā)器是指當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生是指當(dāng)服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生DDLDDL事件時(shí)將啟用。事件時(shí)將啟用。DDLDDL事事件即指在表或索引中的件即指在表或索引中的createcreate、alteralter、dropdrop語(yǔ)句。語(yǔ)句。（2 2）DMLDML（ 數(shù)據(jù)操縱語(yǔ)言數(shù)據(jù)操縱語(yǔ)言 ）觸發(fā)器）觸發(fā)器是指觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生是指觸發(fā)器在數(shù)據(jù)庫(kù)中發(fā)生DMLDML事件時(shí)將啟用。事件時(shí)將啟用

37、。DMLDML事件事件即指在表或視圖中修改數(shù)據(jù)的即指在表或視圖中修改數(shù)據(jù)的insertinsert、updateupdate、deletedelete語(yǔ)句。語(yǔ)句。因此因此DMLDML觸發(fā)器也可分為觸發(fā)器也可分為3 3種類(lèi)型：種類(lèi)型：INSERT觸發(fā)器、觸發(fā)器、UPDATE觸發(fā)器、觸發(fā)器、DELETE觸發(fā)器。觸發(fā)器。32數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.1 6.2.1 觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的特點(diǎn)和類(lèi)型觸發(fā)器的類(lèi)型觸發(fā)器的類(lèi)型按觸發(fā)器被按觸發(fā)器被激活的時(shí)機(jī)激活的時(shí)機(jī)可以分為以下兩種可以分為以下兩種類(lèi)型：類(lèi)型：（1 1）AFTERAFTER觸發(fā)器（后觸發(fā)器）觸發(fā)器（后觸發(fā)器）是是在觸發(fā)動(dòng)作之后再觸動(dòng)，可視

38、為控制觸發(fā)器激活時(shí)間的機(jī)制。在引起在觸發(fā)動(dòng)作之后再觸動(dòng)，可視為控制觸發(fā)器激活時(shí)間的機(jī)制。在引起觸發(fā)器執(zhí)行的更新語(yǔ)句成功完成之后執(zhí)行。如果更新語(yǔ)句因錯(cuò)誤（如違觸發(fā)器執(zhí)行的更新語(yǔ)句成功完成之后執(zhí)行。如果更新語(yǔ)句因錯(cuò)誤（如違反約束或語(yǔ)法錯(cuò)誤）而失敗，觸發(fā)器將不會(huì)執(zhí)行。反約束或語(yǔ)法錯(cuò)誤）而失敗，觸發(fā)器將不會(huì)執(zhí)行。此類(lèi)觸發(fā)器只能定義在表上，不能創(chuàng)建在視圖上?？梢詾槊總€(gè)觸發(fā)操作此類(lèi)觸發(fā)器只能定義在表上，不能創(chuàng)建在視圖上?？梢詾槊總€(gè)觸發(fā)操作（如（如INSERTINSERT、UPDATEUPDATE或或DELETEDELETE）創(chuàng)建多個(gè)）創(chuàng)建多個(gè)AFTERAFTER觸發(fā)器觸發(fā)器。（2 2）INSTEAD OF

39、INSTEAD OF觸發(fā)器（替代觸發(fā)器）觸發(fā)器（替代觸發(fā)器）將將在數(shù)據(jù)變動(dòng)以前被觸發(fā)，該類(lèi)觸發(fā)器代替觸發(fā)操作被執(zhí)行。在數(shù)據(jù)變動(dòng)以前被觸發(fā)，該類(lèi)觸發(fā)器代替觸發(fā)操作被執(zhí)行。該類(lèi)觸發(fā)器既可在表上定義，也可在視圖上定義。對(duì)于每個(gè)觸發(fā)操作（該類(lèi)觸發(fā)器既可在表上定義，也可在視圖上定義。對(duì)于每個(gè)觸發(fā)操作（INSERTINSERT、UPDATEUPDATE和和DELETEDELETE）只能）只能定義定義1 1個(gè)個(gè)INSTEAD OFINSTEAD OF觸發(fā)器。觸發(fā)器。33數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.2 6.2.2 觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建與觸發(fā)器相關(guān)的虛擬表與觸發(fā)器相關(guān)的虛擬表在觸發(fā)器執(zhí)行的時(shí)候，在觸發(fā)器執(zhí)行的時(shí)

40、候，系統(tǒng)產(chǎn)生系統(tǒng)產(chǎn)生兩個(gè)臨時(shí)表：兩個(gè)臨時(shí)表：inserted inserted 表和表和deleted deleted 表。表。（1 1）insertedinserted表表存儲(chǔ)著被存儲(chǔ)著被INSERTINSERT和和UPDATEUPDATE語(yǔ)句語(yǔ)句影響的新的數(shù)據(jù)記錄影響的新的數(shù)據(jù)記錄。當(dāng)用戶執(zhí)行。當(dāng)用戶執(zhí)行INSERTINSERT和和UPDATEUPDATE語(yǔ)句時(shí)，新數(shù)據(jù)記錄的備份被復(fù)制到語(yǔ)句時(shí)，新數(shù)據(jù)記錄的備份被復(fù)制到insertedinserted臨時(shí)表中。臨時(shí)表中。（2 2）deleteddeleted表表存儲(chǔ)著被存儲(chǔ)著被DELETEDELETE和和UPDATEUPDATE語(yǔ)句語(yǔ)句影響

41、的舊數(shù)據(jù)記錄影響的舊數(shù)據(jù)記錄。在執(zhí)行。在執(zhí)行DELETEDELETE和和UPDATEUPDATE語(yǔ)句過(guò)程中，指定的舊數(shù)據(jù)記錄被用戶從基本表中刪除，然后轉(zhuǎn)語(yǔ)句過(guò)程中，指定的舊數(shù)據(jù)記錄被用戶從基本表中刪除，然后轉(zhuǎn)移到移到deletedelete表中。表中。34數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.2 6.2.2 觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建創(chuàng)建創(chuàng)建觸發(fā)器主要有觸發(fā)器主要有T-SQLT-SQL語(yǔ)句和對(duì)象資源管理器等方式。語(yǔ)句和對(duì)象資源管理器等方式。1 1使用使用CREATE TRIGGERCREATE TRIGGER語(yǔ)句創(chuàng)建語(yǔ)句創(chuàng)建觸發(fā)器觸發(fā)器 語(yǔ)法格式：語(yǔ)法格式： CREATE TRIGGER schema_nam

42、e. trigger_name ON table_name|view_name WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE,INSERT,UPDATE AS sql_statement ,.n 例：例：CREATE TRIGGET trig_stu ON Student AFTER INSERT, DELETE, UPDATE AS SELECT * FROM student35創(chuàng)建觸發(fā)器必須指定的選項(xiàng)：創(chuàng)建觸發(fā)器必須指定的選項(xiàng)：名稱(chēng)；名稱(chēng)；在其上定義觸發(fā)器的表；在其上定義觸發(fā)器的表；觸發(fā)器將何時(shí)激發(fā)；觸發(fā)器將何時(shí)激發(fā)；激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)

43、句；激活觸發(fā)器的數(shù)據(jù)修改語(yǔ)句；執(zhí)行觸發(fā)操作的編程語(yǔ)句；執(zhí)行觸發(fā)操作的編程語(yǔ)句；對(duì)對(duì)CREATE TRIGGER CREATE TRIGGER 語(yǔ)句的文語(yǔ)句的文本加密。本加密。數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.2 6.2.2 觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器注意事項(xiàng)：創(chuàng)建觸發(fā)器注意事項(xiàng)：（1 1）CREATE TRIGGERCREATE TRIGGER語(yǔ)句語(yǔ)句必須必須是批處理中的是批處理中的第一條第一條語(yǔ)句。語(yǔ)句。（2 2）只能在）只能在當(dāng)前數(shù)據(jù)庫(kù)當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器，一個(gè)觸發(fā)器只能中創(chuàng)建觸發(fā)器，一個(gè)觸發(fā)器只能對(duì)對(duì)應(yīng)一個(gè)表。應(yīng)一個(gè)表。（3 3）表的所有者具有創(chuàng)建觸發(fā)器的默認(rèn)權(quán)限，不能將該權(quán)）表的所有者

44、具有創(chuàng)建觸發(fā)器的默認(rèn)權(quán)限，不能將該權(quán)限轉(zhuǎn)給其他用戶。限轉(zhuǎn)給其他用戶。（4 4）不能在）不能在視圖、臨時(shí)表、系統(tǒng)表視圖、臨時(shí)表、系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用視圖、臨時(shí)表，但是不能引用系統(tǒng)表。發(fā)器可以引用視圖、臨時(shí)表，但是不能引用系統(tǒng)表。（5 5）盡管）盡管TRUNCATE TABLETRUNCATE TABLE語(yǔ)句類(lèi)似于沒(méi)有語(yǔ)句類(lèi)似于沒(méi)有WHEREWHERE子句的子句的DELETEDELETE語(yǔ)句，但由于該語(yǔ)句不被記入日志，所以它不會(huì)引語(yǔ)句，但由于該語(yǔ)句不被記入日志，所以它不會(huì)引發(fā)發(fā)DELETEDELETE觸發(fā)器。觸發(fā)器。36數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.2 6.2.2

45、觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建【例例6.126.12】創(chuàng)建創(chuàng)建DelCourseDelCourse觸發(fā)器的語(yǔ)句如下：觸發(fā)器的語(yǔ)句如下：CREATE TRIGGER CREATE TRIGGER DelCourseDelCourse ON ON C_InfoC_InfoFOR FOR DELETE DELETE ASASDELETE DELETE S_C_InfoS_C_Info WHERE WHERE C_NoC_No IN ( IN (SELECT SELECT C_NoC_No FROM FROM deleted)deleted)GOGO在在“新建查詢(xún)新建查詢(xún)”窗格中輸入以下語(yǔ)句并執(zhí)行：窗格中輸

46、入以下語(yǔ)句并執(zhí)行：DELETE DELETE FROM FROM C_InfoC_Info WHERE WHERE C_NoC_No=29000011=29000011注意注意：該語(yǔ)句從：該語(yǔ)句從C_Info表中刪除課程編號(hào)為表中刪除課程編號(hào)為“29000011”的數(shù)據(jù)行的數(shù)據(jù)行，觸發(fā)，觸發(fā)DelCourse觸發(fā)器，觸發(fā)器，產(chǎn)生信息產(chǎn)生信息：37數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.2 6.2.2 觸發(fā)器的創(chuàng)建觸發(fā)器的創(chuàng)建2 2使用圖形界面方式創(chuàng)建觸發(fā)器使用圖形界面方式創(chuàng)建觸發(fā)器 在表在表St_InfoSt_Info上創(chuàng)建觸發(fā)器，操作步驟：上創(chuàng)建觸發(fā)器，操作步驟：38在學(xué)生表在學(xué)生表St_InfoSt_In

47、fo中的中的INSERTINSERT操作上創(chuàng)建操作上創(chuàng)建了一個(gè)名稱(chēng)為了一個(gè)名稱(chēng)為“st_Insertst_Insert”的觸發(fā)器。的觸發(fā)器。當(dāng)在該表上執(zhí)行任何有效的插入操作（當(dāng)在該表上執(zhí)行任何有效的插入操作（不論是否實(shí)際插入了記錄）時(shí)，都會(huì)激不論是否實(shí)際插入了記錄）時(shí)，都會(huì)激活該觸發(fā)器，將變量活該觸發(fā)器，將變量 strstr的值設(shè)為的值設(shè)為“TRIGGER IS WORKING”TRIGGER IS WORKING”。PRINTPRINT命令的作用命令的作用是向客戶端返回用是向客戶端返回用戶定義的消息。戶定義的消息。數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.3 6.2.3 觸發(fā)器的查看和修改觸發(fā)器的查看和修改1

48、 1使用對(duì)象資源管理器查看觸發(fā)器信息使用對(duì)象資源管理器查看觸發(fā)器信息39數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.3 6.2.3 觸發(fā)器的查看和修改觸發(fā)器的查看和修改2 2使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器信息使用系統(tǒng)存儲(chǔ)過(guò)程查看觸發(fā)器信息在在SQL ServerSQL Server中，根據(jù)不同需要，可以使用中，根據(jù)不同需要，可以使用sp_helptextsp_helptext、sp_dependssp_depends、sp_helpsp_help等系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看觸發(fā)器的不同等系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看觸發(fā)器的不同信息。信息。例：例：40數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.3 6.2.3 觸發(fā)器的查看和修改觸發(fā)器的查看和修改專(zhuān)門(mén)查看觸

49、發(fā)器屬性信息的系統(tǒng)存儲(chǔ)過(guò)程：專(zhuān)門(mén)查看觸發(fā)器屬性信息的系統(tǒng)存儲(chǔ)過(guò)程：sp_helptriggersp_helptrigger，語(yǔ)法格式：，語(yǔ)法格式：sp_helptrigger tabname = table sp_helptrigger tabname = table , triggertype = type , triggertype = type 【例例6.146.14】查看查看S_C_InfoS_C_Info表上存在的觸發(fā)器的屬性信息。表上存在的觸發(fā)器的屬性信息。操作的語(yǔ)句：操作的語(yǔ)句： EXEC EXEC sp_helptrigger sp_helptrigger S_C_Info S_C_Info 41數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.3 6.2.3 觸發(fā)器的查看和修改觸發(fā)器的查看和修改3.3.使用對(duì)象資源管理器修改觸發(fā)器的正文使用對(duì)象資源管理器修改觸發(fā)器的正文 注意：被設(shè)置成注意：被設(shè)置成“WITH ENCRYPTION”WITH ENCRYPTION”觸發(fā)器是不能被修改的。觸發(fā)器是不能被修改的。42數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用6.2.3 6.2.3 觸發(fā)器的查看和修改觸發(fā)器的查看和修改4