存儲(chǔ)過(guò)程觸發(fā)器及自定義函數(shù)

存儲(chǔ)過(guò)程觸發(fā)器及自定義函數(shù)第一頁(yè)，共五十五頁(yè)，2022年，8月28日第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)存儲(chǔ)過(guò)程1觸發(fā)器2用戶自定義函數(shù)3SQLServer2008實(shí)訓(xùn)：T-SQL編程的高級(jí)應(yīng)用4第二頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程概述T-SQL語(yǔ)句進(jìn)行編程有兩種方法：一種是把T-SQL語(yǔ)句全部寫(xiě)在應(yīng)用程序中，并存儲(chǔ)在本地；另一種是把部分T-SQL語(yǔ)句編寫(xiě)的程序作為存儲(chǔ)過(guò)程存儲(chǔ)在SQLServer中，只在本地的應(yīng)用程序調(diào)用存儲(chǔ)過(guò)程。大多數(shù)程序員偏向使用后者。存儲(chǔ)過(guò)程的概念：存儲(chǔ)過(guò)程（StoredProcedure）是一組編譯好的、存儲(chǔ)在服務(wù)器上的、能完成特定功能的T-SQL語(yǔ)句集合，是數(shù)據(jù)庫(kù)的一種對(duì)象。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程概述使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：存儲(chǔ)過(guò)程只需編譯一次，以后即可多次執(zhí)行，因此可以提高應(yīng)用程序的性能。存儲(chǔ)過(guò)程一經(jīng)被創(chuàng)建，可以在程序中被多次調(diào)用；并且修改存儲(chǔ)過(guò)程不會(huì)影響應(yīng)用程序源代碼，因此極大地提高了程序的重用性、可維護(hù)性、共享性和可移植性。存儲(chǔ)過(guò)程存儲(chǔ)在服務(wù)中，能夠減少網(wǎng)絡(luò)流量。存儲(chǔ)過(guò)程可被作為一種安全機(jī)制來(lái)充分利用。存儲(chǔ)過(guò)程的分類(lèi)：系統(tǒng)存儲(chǔ)過(guò)程。擴(kuò)展存儲(chǔ)過(guò)程。用戶定義的存儲(chǔ)過(guò)程。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：⑴依次選擇“數(shù)據(jù)庫(kù)”→“BillingSys”→“可編程性”→“存儲(chǔ)過(guò)程”節(jié)點(diǎn)，右擊選擇“新建存儲(chǔ)過(guò)程”命令，打開(kāi)“查詢(xún)編輯器”，在“查詢(xún)編輯器”中出現(xiàn)存儲(chǔ)過(guò)程的編程模板。如左圖所示。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第五頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：⑵修改存儲(chǔ)過(guò)程的編程模板，插入需要的T-SQL代碼。①存儲(chǔ)過(guò)程編程模板。……CREATEPROCEDURE<Procedure_Name,sysname,ProcedureName> --Addtheparametersforthestoredprocedurehere <@Param1,sysname,@p1><Datatype_For_Param1,,int>=<Default_Value_For_Param1,,0>, <@Param2,sysname,@p2><Datatype_For_Param2,,int>=<Default_Value_For_Param2,,0>ASBEGIN --SETNOCOUNTONaddedtopreventextraresultsetsfrom --interferingwithSELECTstatements. SETNOCOUNTON;--Insertstatementsforprocedurehere SELECT<@Param1,sysname,@p1>,<@Param2,sysname,@p2>ENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第六頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：⑵修改存儲(chǔ)過(guò)程的編程模板，插入需要的T-SQL代碼。①存儲(chǔ)過(guò)程編程模板。說(shuō)明：參數(shù)按以下格式包括在尖括號(hào)（<>）中：<parameter_name,data_type,default_value>。其中尖括號(hào)內(nèi)是參數(shù)的三個(gè)元素：參數(shù)的名稱(chēng)、該參數(shù)的數(shù)據(jù)類(lèi)型以及該參數(shù)的默認(rèn)值。parameter_name：列出存儲(chǔ)過(guò)程或函數(shù)中的參數(shù)。此字段是只讀的。data_type：模板中參數(shù)的數(shù)據(jù)類(lèi)型。此字段是只讀的。若要更改數(shù)據(jù)類(lèi)型，請(qǐng)更改模板中的參數(shù)。default_value：為所選參數(shù)指定值。默認(rèn)值。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第七頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：⑵修改存儲(chǔ)過(guò)程的編程模板，插入需要的T-SQL代碼。②在存儲(chǔ)過(guò)程中編寫(xiě)代碼。在上述模板代碼中：在”CREATEPROCEDURE…”行命令中，用戶必須自己定義一個(gè)存儲(chǔ)過(guò)程名稱(chēng)，來(lái)替代參數(shù)部分，即“<>”部分?！?lt;@Param1,…”、“<@Param2,…”行命令用來(lái)指定參數(shù)項(xiàng)，如果用戶需要為該存儲(chǔ)過(guò)程指定參數(shù)，則按照提示指定參數(shù)，例如：@Cust_namevarchar(20)；如果不需要參數(shù)，則刪除這兩條命令?！癝ELECT<@Param1,…”行命令是為參數(shù)賦值。如果沒(méi)有參數(shù)，則刪除此條命令。用戶從模板的第33行之后（即“--Insertstatementsforprocedurehere”之后），插入所要編寫(xiě)的存儲(chǔ)過(guò)程代碼。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第八頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：【例10-1】為計(jì)費(fèi)系統(tǒng)創(chuàng)建一個(gè)實(shí)現(xiàn)查詢(xún)單位客戶及其聯(lián)系人的存儲(chǔ)過(guò)程?！瑿REATEPROCEDUREmypro1ASBEGIN --SETNOCOUNTONaddedtopreventextraresultsetsfrom --interferingwithSELECTstatements. SETNOCOUNTON;selectcid,cname,rname,rtelephonefromCustomerinnerjoinRelationeronCustomer.RID=Relationer.RIDwhereCType='商業(yè)'orCType='公眾'orCType='大戶'ENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第九頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程使用存儲(chǔ)過(guò)程模板創(chuàng)建存儲(chǔ)過(guò)程：⑶單擊工具欄上的“執(zhí)行”按鈕，即可創(chuàng)建該存儲(chǔ)過(guò)程。此時(shí)，刷新對(duì)象資源管理器，重新展開(kāi)“存儲(chǔ)過(guò)程”節(jié)點(diǎn)，可以看到剛建立的存儲(chǔ)過(guò)程。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程利用對(duì)象資源管理器修改存儲(chǔ)過(guò)程：⑴在對(duì)象資源管理器窗口中，找到需要修改的存儲(chǔ)過(guò)程節(jié)點(diǎn)，在其上右擊選擇“修改”命令，打開(kāi)“查詢(xún)編輯器”，其中出現(xiàn)要修改的存儲(chǔ)過(guò)程代碼。⑵對(duì)現(xiàn)有的存儲(chǔ)過(guò)程進(jìn)行修改。⑶修改完成后，單擊工具欄上的“執(zhí)行”按鈕，即可完成存儲(chǔ)過(guò)程的修改。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十一頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用SSMS創(chuàng)建與管理存儲(chǔ)過(guò)程利用對(duì)象資源管理器刪除存儲(chǔ)過(guò)程：在對(duì)象資源管理器窗口中，找到需要?jiǎng)h除的存儲(chǔ)過(guò)程節(jié)點(diǎn)，右擊選擇“刪除”命令。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十二頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程：CREATEPROCEDUR語(yǔ)句的語(yǔ)法格式如下：CREATEPROCEDURE|PROC<存儲(chǔ)過(guò)程名>[;n][@形參名數(shù)據(jù)類(lèi)型,…][,][@變參名數(shù)據(jù)類(lèi)型OUTPUT,…][WITHENCRYPTION|RECOMPILE][FORREPLICATION]AS<T-SQL語(yǔ)句或語(yǔ)句塊>第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十三頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程：CREATEPROCEDUR語(yǔ)句的參數(shù)說(shuō)明：n：是可選整數(shù)，用于對(duì)同名的過(guò)程分組。@形參名…：指定過(guò)程中的參數(shù)。每個(gè)參數(shù)僅作用于該過(guò)程本身，是局部的。@變參名…：指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變。僅適用于CURSOR參數(shù)。WITHENCRYPTION：指示SQLServer將CREATEPROCEDURE語(yǔ)句的原始文本轉(zhuǎn)換為模糊格式。WITHRECOMPILE：指示數(shù)據(jù)庫(kù)引擎不緩存該過(guò)程的計(jì)劃，該過(guò)程在運(yùn)行時(shí)編譯。如果指定了FORREPLICATION，則不能使用此選項(xiàng)。FORREPLICATION：指定不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過(guò)程。使用FORREPLICATION選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程可用作存儲(chǔ)過(guò)程篩選器，且只能在復(fù)制過(guò)程中執(zhí)行。如果指定了FORREPLICATION，則無(wú)法聲明參數(shù)。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十四頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程查看存儲(chǔ)過(guò)程的信息：sp_help'存儲(chǔ)過(guò)程名稱(chēng)'：用于查看存儲(chǔ)過(guò)程的一般信息，如存儲(chǔ)過(guò)程的名稱(chēng)、屬性、類(lèi)型和創(chuàng)建時(shí)間。sp_helptext'存儲(chǔ)過(guò)程名稱(chēng)'：用于查看存儲(chǔ)過(guò)程的正文信息。sp_depends'存儲(chǔ)過(guò)程名稱(chēng)'|'表名'：用于查看指定存儲(chǔ)過(guò)程所引用的表或者指定的表涉及到的所有存儲(chǔ)過(guò)程。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十五頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程：ALTERPROCEDURE語(yǔ)句的語(yǔ)法格式如下：ALTERPROCEDURE|PROC<存儲(chǔ)過(guò)程名>[@形參名數(shù)據(jù)類(lèi)型,…][,][@變參名數(shù)據(jù)類(lèi)型OUTPUT,…][WITHENCRYPTION|RECOMPILE][FORREPLICATION]AS<T-SQL語(yǔ)句或語(yǔ)句塊>第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十六頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程：DROPPROCEDURE語(yǔ)句的語(yǔ)法格式如下：DROPPROCEDURE|PROC<存儲(chǔ)過(guò)程名>[,…,n]例如，刪除數(shù)據(jù)庫(kù)BillingSys中的存儲(chǔ)過(guò)程“mypro2”,其代碼為：USEBillingSysDROPPROCEDUREmypro2第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十七頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程：EXECUTE語(yǔ)句的語(yǔ)法格式如下：Exec|Execute{[@整型變量=]存儲(chǔ)過(guò)程名[,n]|@存儲(chǔ)過(guò)程變量名[[@過(guò)程參數(shù)=]參數(shù)值|@可變參數(shù)名[OUTPUT]|[DEFAULT]][,…,n][WITHRECOMPILE]}[;]第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十八頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程：EXECUTE語(yǔ)句的參數(shù)說(shuō)明：@整型變量：是可選項(xiàng)，代表存儲(chǔ)過(guò)程的返回狀態(tài)，該變量在用于EXECUTE語(yǔ)句前，必須已經(jīng)聲明過(guò)。n：可選整數(shù)，用于對(duì)同名的過(guò)程分組。@過(guò)程參數(shù)…：為存儲(chǔ)過(guò)程的參數(shù)賦值。其中參數(shù)名必須和存儲(chǔ)過(guò)程定義中的相同。OUTPUT：指定該參數(shù)為輸出參數(shù)，該參數(shù)在存儲(chǔ)過(guò)程中定義時(shí)也必須使用OUTPUT選項(xiàng)聲明。DEFAULT：指明該參數(shù)使用默認(rèn)值。如果該參數(shù)定義時(shí)沒(méi)有指定默認(rèn)值，則不能使用DEFAULT選項(xiàng)。WITHRECOMPILE：使用WITHRECOMPILE，強(qiáng)制在執(zhí)行存儲(chǔ)過(guò)程時(shí)重新對(duì)其進(jìn)行編譯。而一般情況下，存儲(chǔ)過(guò)程只有在第一次執(zhí)行時(shí)，系統(tǒng)對(duì)其進(jìn)行編譯，并將存儲(chǔ)起來(lái)，以后執(zhí)行時(shí)直接取出執(zhí)行計(jì)劃執(zhí)行，不再編譯。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第十九頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程帶參數(shù)傳遞的存儲(chǔ)過(guò)程舉例：【例10-2】為計(jì)費(fèi)系統(tǒng)創(chuàng)建一個(gè)實(shí)現(xiàn)輸入一個(gè)有效客戶ID，查詢(xún)其聯(lián)系人的存儲(chǔ)過(guò)程。創(chuàng)建該存儲(chǔ)過(guò)程Query_Relationer代碼如下：……CREATEPROCEDUREQuery_Relationer@QueryCIDint, --輸入的形參

@QueryRNamevarchar(20)OUTPUT --輸出的形參ASBEGINSELECT@QueryRName=RNameFROMRelationerWHERERID=(SELECTRIDFROMCustomerWHERECID=@QueryCIDANDCStatus=1)ENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程帶參數(shù)傳遞的存儲(chǔ)過(guò)程舉例：【例10-2】為計(jì)費(fèi)系統(tǒng)創(chuàng)建一個(gè)實(shí)現(xiàn)輸入一個(gè)有效客戶ID，查詢(xún)其聯(lián)系人的存儲(chǔ)過(guò)程。如果要查詢(xún)客戶ID為20103530的聯(lián)系人，調(diào)用該存儲(chǔ)過(guò)程Query_Relationer，代碼如下：DECLARE@Relationer_namevarchar(20),@Cust_IDintSET@Cust_ID=20103530EXECUTEQuery_Relationer@Cust_ID,@Relationer_nameOUTPUTPRINT'客戶ID為'+convert(char(8),@Cust_ID)+'的聯(lián)系人是：'+@Relationer_name如果要查詢(xún)客戶ID為20103531的聯(lián)系人。只須在下面代碼的第二行，將變量@Cust_ID的值改為20103531。調(diào)用該存儲(chǔ)過(guò)程，結(jié)果沒(méi)有任何信息返回。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十一頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程帶參數(shù)傳遞的存儲(chǔ)過(guò)程舉例：【例10-2】為計(jì)費(fèi)系統(tǒng)創(chuàng)建一個(gè)實(shí)現(xiàn)輸入一個(gè)有效客戶ID，查詢(xún)其聯(lián)系人的存儲(chǔ)過(guò)程。改進(jìn)：前面設(shè)計(jì)的存儲(chǔ)過(guò)程Query_Relationer還有待改進(jìn)。對(duì)不存在的客戶，提示“不存在”信息。因此，需要對(duì)給定的客戶ID是否在表中存在給出判斷。代碼如下：ALTERPROCEDUREQuery_Relationer@QueryCIDint, --輸入的形參

@QueryRNamevarchar(20)OUTPUT --輸出的形參ASBEGIN--判斷用戶輸入的CID值是否存在，如果不存在，返回“不存在”

IFEXISTS(SELECTCIDFROMCustomerWHERECID=@QueryCID)SELECT@QueryRName=RNameFROMRelationerWHERERID=(SELECTRIDFROMCustomerWHERECID=@QueryCIDANDCStatus=1)ELSESET@QueryRName='不存在'ENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十二頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程帶參數(shù)傳遞的存儲(chǔ)過(guò)程舉例：【例10-3】利用存儲(chǔ)過(guò)程計(jì)算階乘。代碼如下：CREATEPROCEDUREfactorial--創(chuàng)建存儲(chǔ)過(guò)程factorial@in_numint,@out_numfloatOUTPUTASBEGINDECLARE@iint,@ffloatSET@i=1SET@f=1WHILE@i<=@in_numBEGINSET@f=@f*@iSET@i=@i+1ENDSET@out_num=@fENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十三頁(yè)，共五十五頁(yè)，2022年，8月28日存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句創(chuàng)建與管理存儲(chǔ)過(guò)程帶參數(shù)傳遞的存儲(chǔ)過(guò)程舉例：【例10-3】利用存儲(chǔ)過(guò)程計(jì)算階乘。求9的階乘，調(diào)用存儲(chǔ)過(guò)程factorial，代碼如下：DECLARE@factorfloatEXECUTEfactorial@out_num=@factorOUTPUT,@in_num=9PRINT@factor第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十四頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器觸發(fā)器概念觸發(fā)器（Trigger）是一種特殊的存儲(chǔ)過(guò)程，它不允許帶參數(shù)，不能由用戶直接通過(guò)名稱(chēng)調(diào)用，而是由用戶的某一動(dòng)作自動(dòng)觸發(fā)。SQLServer2008提供三類(lèi)觸發(fā)器：DML觸發(fā)器：在數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)操作（如：INSERT、UPDATE、DELETE）事件時(shí)自動(dòng)執(zhí)行。DDL觸發(fā)器：在服務(wù)器或數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義（如：CREATE、ALTER、DROP）事件時(shí)自動(dòng)執(zhí)行。登錄觸發(fā)器：在與SQLServer實(shí)例建立用戶會(huì)話時(shí)自動(dòng)執(zhí)行，主要用來(lái)審核和控制服務(wù)器會(huì)話。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十五頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器DML觸發(fā)器在用戶對(duì)表中的數(shù)據(jù)進(jìn)行插入（INSERT）、修改（UPDATE）和刪除（DELETE）時(shí)自動(dòng)運(yùn)行。使用觸發(fā)器模板創(chuàng)建觸發(fā)器：在展開(kāi)SQLServer實(shí)例中，依次展開(kāi)“數(shù)據(jù)庫(kù)”→“BillingSys”→“表”，繼續(xù)展開(kāi)要?jiǎng)?chuàng)建觸發(fā)器的具體表節(jié)點(diǎn)，選擇“觸發(fā)器”，右擊選擇“新建觸發(fā)器”命令，打開(kāi)“查詢(xún)編輯器”，在“查詢(xún)編輯器”中出現(xiàn)觸發(fā)器的編程模板。當(dāng)模板修改完成后，單擊窗口工具欄中的“執(zhí)行”按鈕，創(chuàng)建該觸發(fā)器。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十六頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器DML觸發(fā)器在用戶對(duì)表中的數(shù)據(jù)進(jìn)行插入（INSERT）、修改（UPDATE）和刪除（DELETE）時(shí)自動(dòng)運(yùn)行。使用觸發(fā)器模板創(chuàng)建觸發(fā)器：比如選擇Customer表，打開(kāi)觸發(fā)器模板，其模板代碼如下：……CREATETRIGGER<Schema_Name,sysname,Schema_Name>.<Trigger_Name,sysname,Trigger_Name>ON<Schema_Name,sysname,Schema_Name><Table_Name,sysname,Table_Name>AFTER<Data_Modification_Statements,,INSERT,DELETE,UPDATE>ASBEGIN --SETNOCOUNTONaddedtopreventextraresultsetsfrom --interferingwithSELECTstatements. SETNOCOUNTON;--InsertstatementsfortriggerhereENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十七頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器使用CREATETRIGGER創(chuàng)建DML觸發(fā)器：語(yǔ)法格式如下：CREATETRIGGER<觸發(fā)器名>ON<表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF[INSERT][,][UPDATE][,][DELETE]AST-SQL語(yǔ)句或語(yǔ)句塊[;]第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十八頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器使用CREATETRIGGER創(chuàng)建DML觸發(fā)器：參數(shù)說(shuō)明如下：觸發(fā)器名：觸發(fā)器名稱(chēng)，必須遵守標(biāo)識(shí)符命名規(guī)則，不能以#或##開(kāi)頭。WITHENCRYPTION：指定對(duì)觸發(fā)器進(jìn)行加密處理。FOR|AFTER：指定觸發(fā)器中在相應(yīng)的DML操作（INSERT、UPDATE、DELETE）成功執(zhí)行后才觸發(fā)。視圖上不能定義FOR和AFTER觸發(fā)器，只能定義INSTEADOF觸發(fā)器。INSTEADOF：指定執(zhí)行DML觸發(fā)器用于“代替”引發(fā)觸發(fā)器執(zhí)行的INSERT、UPDATE或DELETE語(yǔ)句。在表或視圖上，每個(gè)INSERT、UPDATE和DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。[INSERT][,][UPDATE][,][DELETE］：指定能夠激活觸發(fā)器的操作，必須至少指定一個(gè)操作。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第二十九頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器【例10-4】在聯(lián)系人表Relationer中修改聯(lián)系人ID（RID）后，同步修改客戶表Customer中的聯(lián)系人ID（RID），保證修改記錄滿足參照完整性。這是一個(gè)UPDATE觸發(fā)器，其代碼如下：CREATETRIGGERUpdate_RIDONRelationerAFTERUPDATEASBEGINSETNOCOUNTONUPDATECustomerSETRID=(SELECTRIDFROMINSERTED)ENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DML觸發(fā)器【例10-4】在聯(lián)系人表Relationer中修改聯(lián)系人ID（RID）后，同步修改客戶表Customer中的聯(lián)系人ID（RID），保證修改記錄滿足參照完整性。在對(duì)Relationer表中RID值進(jìn)行修改時(shí)，需要執(zhí)行UPDATE命令。當(dāng)執(zhí)行UPDATE命令時(shí)，會(huì)激發(fā)Relationer表中的觸發(fā)器Update_RID，同步對(duì)Customer表中的RID列值進(jìn)行修改。由于Customer表中的RID列存在外鍵約束fk_Customer，因此，在執(zhí)行UPDATE命令前，需要對(duì)外鍵約束進(jìn)行禁用設(shè)置，執(zhí)行完UPDATE命令后，再啟用外鍵約束。具體代碼如下：--禁用外鍵約束ALTERTABLECustomerNOCHECKCONSTRAINTfk_Customer--更新紀(jì)錄

UPDATERelationerSETRID=20106609whereRID=20106605--啟用約束ALTERTABLECustomerCHECKCONSTRAINTfk_Customer第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十一頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DDL觸發(fā)器使用CREATETRIGGER創(chuàng)建DDL觸發(fā)器：語(yǔ)法格式如下：CREATETRIGGER<觸發(fā)器名>ONALLSERVER|DATABASE[WITHENCRYPTION]FOR|AFTER<事件類(lèi)型或事件組>[,…,n]AS<T-SQL語(yǔ)句或語(yǔ)句塊>[;]第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十二頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DDL觸發(fā)器使用CREATETRIGGER創(chuàng)建DDL觸發(fā)器：參數(shù)說(shuō)明如下：ALLSERVER：指定DDL觸發(fā)器的作用域?yàn)楫?dāng)前服務(wù)器。如果指定了此參數(shù)，則只要當(dāng)前服務(wù)器中的任何位置上出現(xiàn)命令中指定的事件類(lèi)型或事件組，就會(huì)激發(fā)該觸發(fā)器。DATABASE：指定DDL觸發(fā)器的作用域?yàn)楫?dāng)前數(shù)據(jù)庫(kù)。如果指定了此參數(shù)，則只要當(dāng)前數(shù)據(jù)庫(kù)中出現(xiàn)命令中指定的事件類(lèi)型或事件組，就會(huì)激發(fā)該觸發(fā)器。WITHENCRYPTION：對(duì)CREATETAIGGER語(yǔ)句的文本進(jìn)行加密處理。FOR|AFTER：指定DDL觸發(fā)器僅在命令中指定事件類(lèi)型或事件組的所有操作都已成功執(zhí)行時(shí)才被觸發(fā)。事件類(lèi)型：將激活DDL觸發(fā)器的T-SQL語(yǔ)言事件的名稱(chēng)。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE等操作。事件組：預(yù)定義的T-SQL語(yǔ)句事件分組的名稱(chēng)。執(zhí)行任何屬于事件組的T-SQL語(yǔ)句事件之后，都將激發(fā)DDL觸發(fā)器。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十三頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器創(chuàng)建觸發(fā)器DDL觸發(fā)器【例10-5】設(shè)計(jì)DDL觸發(fā)器，禁止修改和刪除當(dāng)前數(shù)據(jù)庫(kù)中的任何表。代碼如下：CREATETRIGGERsafe_databaseONDATABASEFORALTER_TABLE,DROP_TABLEASBEGINPRINT'不能修改或刪除表！如果必須要完成此操作，請(qǐng)先禁用觸發(fā)器safe_database。'ROLLBACKENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十四頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器管理觸發(fā)器查看觸發(fā)器信息因?yàn)橛|發(fā)器是特殊的存儲(chǔ)過(guò)程，所以查看觸發(fā)器也是使用系統(tǒng)存儲(chǔ)過(guò)程實(shí)現(xiàn)。如：sp_help'觸發(fā)器名稱(chēng)'。sp_helptext'觸發(fā)器名稱(chēng)'。sp_depends'觸發(fā)器名稱(chēng)'|'表名'。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十五頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器管理觸發(fā)器修改觸發(fā)器修改DML觸發(fā)器語(yǔ)法格式如下：ALTERTRIGGER<觸發(fā)器名>ON<表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF[INSERT][,][UPDATE][,][DELETE]AS<T-SQL語(yǔ)句或語(yǔ)句塊>[;]第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十六頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器管理觸發(fā)器修改觸發(fā)器修改DML觸發(fā)器，其語(yǔ)法格式如下：ALTERTRIGGER<觸發(fā)器名>ON<表名|視圖名>[WITHENCRYPTION]FOR|AFTER|INSTEADOF[INSERT][,][UPDATE][,][DELETE]AS<T-SQL語(yǔ)句或語(yǔ)句塊>[;]修改DDL觸發(fā)器，其語(yǔ)法格式如下：ALTERTRIGGER<觸發(fā)器名>ONALLSERVER|DATABASE[WITHENCRYPTION]FOR|AFTER<事件類(lèi)型或事件組>[,…,n]AS<T-SQL語(yǔ)句或語(yǔ)句塊>[;]第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十七頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器管理觸發(fā)器刪除觸發(fā)器如果確認(rèn)觸發(fā)器已經(jīng)不再需要，可以使用DROPTRIGGER命令將其刪除。其語(yǔ)法格式如下：DROPTRIGGER<觸發(fā)器名>可以使用對(duì)象資源管理器來(lái)完成觸發(fā)器的管理。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十八頁(yè)，共五十五頁(yè)，2022年，8月28日觸發(fā)器管理觸發(fā)器禁用與啟用觸發(fā)器當(dāng)暫時(shí)不需要某個(gè)觸發(fā)器時(shí)，可將其禁用。禁用觸發(fā)器的語(yǔ)法格式如下：DISABLETRIGGER<觸發(fā)器名>ON對(duì)象名|DATABASE|ALLSERVER已禁用的觸發(fā)器可以被重新啟用。啟用觸發(fā)器的語(yǔ)法格式如下：ENABLETRIGGER<觸發(fā)器名>ON對(duì)象名|DATABASE|ALLSERVER第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第三十九頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)自定義函數(shù)的基本概念用戶自定義函數(shù)是為了實(shí)現(xiàn)某些功能，用戶對(duì)多個(gè)T-SQL語(yǔ)句進(jìn)行封裝，并返回結(jié)果。用戶自定義函數(shù)與存儲(chǔ)過(guò)程的比較：第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008比較項(xiàng)用戶自定義函數(shù)存儲(chǔ)過(guò)程參數(shù)允許有0到多個(gè)輸入?yún)?shù)，不允許有輸出參數(shù)允許有多個(gè)輸入/輸出參數(shù)返回值有且只有一個(gè)返回值可以沒(méi)有返回值調(diào)用在表達(dá)式或賦值語(yǔ)句中引用使用EXECUTE調(diào)用第四十頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)自定義函數(shù)的基本概念使用用戶定義函數(shù)的優(yōu)點(diǎn)如下：模塊化程序設(shè)計(jì)：將特定的功能封閉在一個(gè)用戶定義函數(shù)中，并存儲(chǔ)在數(shù)據(jù)庫(kù)中。這個(gè)函數(shù)只需創(chuàng)建一次，以后便可以在程序中多次調(diào)用。并且用戶定義函數(shù)可以獨(dú)立于程序源代碼進(jìn)行修改。執(zhí)行速度快：與存儲(chǔ)過(guò)程相似，用戶定義函數(shù)實(shí)施緩存計(jì)劃。即用戶定義函數(shù)只需編譯一次，以后可以多次重用，從而降低了T-SQL代碼的編譯開(kāi)銷(xiāo)。這意味著每次使用用戶定義函數(shù)時(shí)均無(wú)需重新解析和重新優(yōu)化，從而縮短了執(zhí)行時(shí)間。減少網(wǎng)絡(luò)流量：和存儲(chǔ)過(guò)程一樣可以減少網(wǎng)絡(luò)通信的流量。此外，用戶定義函數(shù)還可以用在WHERE子句中,在服務(wù)器端過(guò)濾數(shù)據(jù)，以減少發(fā)送至客戶端的數(shù)字或行數(shù)。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十一頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)自定義函數(shù)的基本概念定義函數(shù)的分類(lèi)：標(biāo)量值函數(shù)：標(biāo)量值函數(shù)的返回值是返回子句（RETURNS子句）中定義的類(lèi)型的單個(gè)數(shù)據(jù)值，不能返回多個(gè)值。內(nèi)嵌表值函數(shù)：內(nèi)嵌表值函數(shù)返回的是在RETURNS子句中指定的“table”類(lèi)型的數(shù)據(jù)行集（表）。在內(nèi)嵌表值函數(shù)中，RETUAN子句在括號(hào)中含有一條單獨(dú)的SELECT查詢(xún)語(yǔ)句，該語(yǔ)句的結(jié)果構(gòu)成了內(nèi)嵌表值函數(shù)所返回的表。多語(yǔ)句表值函數(shù)：與內(nèi)嵌表值函數(shù)一樣，多語(yǔ)句表值函數(shù)返回的是由選擇結(jié)果構(gòu)成的數(shù)據(jù)行集（表）。與內(nèi)聯(lián)表值函數(shù)不同的是，多語(yǔ)句表值函數(shù)在返回語(yǔ)句之前還有其他的T－SQL語(yǔ)句，并且RETURNS子句指定的表帶有列及其數(shù)據(jù)類(lèi)型。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十二頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建標(biāo)量值函數(shù)：標(biāo)量值函數(shù)的函數(shù)體由一條或多條T－SQL語(yǔ)句組成，寫(xiě)在BEGIN與END之間。其語(yǔ)法格式如下：CREATEFUNCTION<函數(shù)名>([@形參名數(shù)據(jù)類(lèi)型[,…,n]])RETURNS返回值數(shù)據(jù)類(lèi)型[WITHENCRYPTION][AS]BEGIN<T-SQL語(yǔ)句或語(yǔ)句塊>RETURN返回表達(dá)式END參數(shù)說(shuō)明如下：返回值的數(shù)據(jù)類(lèi)型不能是text、ntext、image和timestamp類(lèi)型。在BEGIN…END之間，必須有一條RETURN語(yǔ)句，用于指定返回表達(dá)式，即函數(shù)的值。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十三頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建標(biāo)量值函數(shù)：【例10-6】定義函數(shù)EProduct_Number，當(dāng)給出一個(gè)客戶ID，返回該客戶所擁有的產(chǎn)品數(shù)量?！痉治觥吭撟远x函數(shù)接收的參數(shù)是客戶ID，數(shù)據(jù)類(lèi)型應(yīng)與EProduct中的CID一致；返回的值是產(chǎn)品數(shù)，該數(shù)據(jù)量一般不大，可以設(shè)為smallint類(lèi)型。代碼如下：CREATEFUNCTIONEProduct_Number(@EP_CIDint)RETURNSsmallintASBEGINDECLARE@epnumsmallintSELECT@epnum=count(*)FROMEProductWHERECID=@EP_CIDRETURN@epnumENDGO第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十四頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建內(nèi)聯(lián)表值函數(shù)：語(yǔ)法格式如下：CREATEFUNCTION<函數(shù)名>([@形參名數(shù)據(jù)類(lèi)型[,…,n]])RETURNSTable[WITHENCRYPTION][AS]RETURN(SELECT查詢(xún)語(yǔ)句)參數(shù)說(shuō)明如下：內(nèi)聯(lián)表值函數(shù)沒(méi)有函數(shù)體。RETURNSTable子句說(shuō)明返回值是一個(gè)表。RETURN子句中的SELECT語(yǔ)句是返回表中的數(shù)據(jù)。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十五頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建內(nèi)聯(lián)表值函數(shù)：【例10-7】定義函數(shù)EProduct_Table，當(dāng)給出一個(gè)客戶ID，返回該客戶所擁有的產(chǎn)品號(hào)碼。代碼如下：CREATEFUNCTIONEProduct_Table(@EP_CIDint)RETURNSTableASRETURN(SELECTENO,ENameFROMEProductWHEREcid=@EP_CID第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十六頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建多語(yǔ)句表值函數(shù)：語(yǔ)法格式如下：CREATEFUNCTION<函數(shù)名>([@形參名數(shù)據(jù)類(lèi)型[,…,n]])RETURNS@返回變量Table(表結(jié)構(gòu)定義)[WITHENCRYPTION][AS]BEGIN<T-SQL語(yǔ)句或語(yǔ)句塊>RETURNEND參數(shù)說(shuō)明如下：RETURNS@返回變量子句指明該函數(shù)的返回局部變量，該變量的數(shù)據(jù)類(lèi)型是Table，而且在該子句中還需要對(duì)返回的表進(jìn)行表結(jié)構(gòu)的定義。在BEGIN…END之間的語(yǔ)句是函數(shù)體，函數(shù)體中必須包括一條不帶參數(shù)的RETURN語(yǔ)句用于返回表。第十章存儲(chǔ)過(guò)程、觸發(fā)器、自定義函數(shù)SQLServer2008第四十七頁(yè)，共五十五頁(yè)，2022年，8月28日用戶自定義函數(shù)創(chuàng)建自定義函數(shù)創(chuàng)建多語(yǔ)句表值函數(shù)：【例10-8】定義多語(yǔ)句表值函數(shù)EProduct_Table_1，完成上例功能。代碼如下：CREATEFUNCTIONEProduct_Table_1(@EP_CIDint)RETURNS@tbTable--定義@tb表變量的結(jié)構(gòu)，其中的列名可以和原數(shù)據(jù)表名不一樣，但數(shù)據(jù)類(lèi)型要一致。(tb_Enochar(11),tb_ENamevarchar(50),tb_EJoinDataDateTime)ASBEGIN