存儲(chǔ)過程與觸發(fā)器(超詳細(xì))

第八章存儲(chǔ)過程與觸發(fā)器在SQLServer2023中存儲(chǔ)過程和觸發(fā)器是兩個(gè)重要的數(shù)據(jù)庫對(duì)象。使用存儲(chǔ)過程，可以將Transact-SQL語句和控制流語句預(yù)編譯到集合并保存到效勞器端，它使得管理數(shù)據(jù)庫、顯示關(guān)于數(shù)據(jù)庫及其用戶信息的工作更為容易。而觸發(fā)器是一種特殊類型的存儲(chǔ)過程，在用戶使用一種或多種數(shù)據(jù)修改操作來修改指定表中的數(shù)據(jù)時(shí)被觸發(fā)并自動(dòng)執(zhí)行，通常用于實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)那么，更有效地實(shí)施數(shù)據(jù)完整性。本章學(xué)習(xí)目標(biāo)了解存儲(chǔ)過程的作用及類型掌握存儲(chǔ)過程的創(chuàng)立及應(yīng)用熟悉存儲(chǔ)過程的管理了解觸發(fā)器的作用及分類熟悉各種類型觸發(fā)器的創(chuàng)立了解嵌套、遞歸觸發(fā)器熟悉觸發(fā)器的管理8.1認(rèn)識(shí)存儲(chǔ)過程Transact-SQL語句是應(yīng)用程序與SQLServer數(shù)據(jù)庫之間的主要編程接口，大量的時(shí)間將花費(fèi)在Transact-SQL語句和應(yīng)用程序代碼上。在很多情況下，許多代碼被重復(fù)使用屢次，每次都輸入相同的代碼不但繁瑣，更由于在客戶機(jī)上的大量命令語句逐條向SQLServer發(fā)送將降低系統(tǒng)運(yùn)行效率。因此，SQLServer提供了一種方法，它將一些固定的操作集中起來由SQLServer數(shù)據(jù)庫效勞器來完成，應(yīng)用程序只需調(diào)用它的名稱，將可實(shí)現(xiàn)某個(gè)特定的任務(wù)，這種方法就是存儲(chǔ)過程。下面將詳細(xì)介紹存儲(chǔ)過程的概念、特點(diǎn)、創(chuàng)立、執(zhí)行等內(nèi)容。8.1.1存儲(chǔ)過程概述SQLServer中T-SQL語言為了實(shí)現(xiàn)特定任務(wù)而將一些需要屢次調(diào)用的固定的操作編寫成子程序并集中以一個(gè)存儲(chǔ)單元的形式存儲(chǔ)在效勞器上，由SQLServer數(shù)據(jù)庫效勞器通過子程序名來調(diào)用它們，這些子程序就是存儲(chǔ)過程。存儲(chǔ)過程是一種數(shù)據(jù)庫對(duì)象，存儲(chǔ)在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個(gè)調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行，具有很強(qiáng)的編程功能。存儲(chǔ)過程可以使用EXECUTE語句來運(yùn)行。在SQLServer中使用存儲(chǔ)過程而不使用存儲(chǔ)在客戶端計(jì)算機(jī)本地的T-SQL程序有以下幾個(gè)方面的好處。加快系統(tǒng)運(yùn)行速度存儲(chǔ)程序只在創(chuàng)立時(shí)進(jìn)行編譯，以后每次執(zhí)行存儲(chǔ)過程都不需再重新編譯，而一般SQL語句每執(zhí)行一次就編譯一次，所以使用存儲(chǔ)過程可提高數(shù)據(jù)庫執(zhí)行速度。封裝復(fù)雜操作當(dāng)對(duì)數(shù)據(jù)庫進(jìn)行復(fù)雜操作時(shí)〔如對(duì)多個(gè)表進(jìn)行更新，刪除時(shí)〕，可用存儲(chǔ)過程將此復(fù)雜操作封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。實(shí)現(xiàn)代碼重用可以實(shí)現(xiàn)模塊化程序設(shè)計(jì)，存儲(chǔ)過程一旦創(chuàng)立，以后即可在程序中調(diào)用任意屢次，這可以改良應(yīng)用程序的可維護(hù)性，并允許應(yīng)用程序統(tǒng)一訪問數(shù)據(jù)庫。增強(qiáng)平安性可設(shè)定特定用戶具有對(duì)指定存儲(chǔ)過程的執(zhí)行權(quán)限而不具備直接對(duì)存儲(chǔ)過程中引用的對(duì)象具有權(quán)限?？梢詮?qiáng)制應(yīng)用程序的平安性，參數(shù)化存儲(chǔ)過程有助于保護(hù)應(yīng)用程序不受SQL注入式攻擊。減少網(wǎng)絡(luò)流量因?yàn)榇鎯?chǔ)過程存儲(chǔ)在效勞器上，并在效勞器上運(yùn)行。一個(gè)需要數(shù)百行T-SQL代碼的操作可以通過一條執(zhí)行過程代碼的語句來執(zhí)行，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼，這樣就可以減少網(wǎng)絡(luò)流量。8.1.2存儲(chǔ)過程的分類存儲(chǔ)過程是一個(gè)被命名的存儲(chǔ)在效勞器上的Transact-SQL語句的集合，是封裝重復(fù)性工作的一種方法，它支持用戶聲明的變量、條件執(zhí)行和其他強(qiáng)大的編程功能。在SQLServer2023中存儲(chǔ)過程可以分為兩類：系統(tǒng)存儲(chǔ)過程、用戶存儲(chǔ)過程和擴(kuò)展性存儲(chǔ)過程。1．系統(tǒng)存儲(chǔ)過程系統(tǒng)存儲(chǔ)過程是由SQLServer系統(tǒng)提供的存儲(chǔ)過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲(chǔ)過程主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQLServer提供幫助，為用戶查看數(shù)據(jù)庫對(duì)象提供方便。例如，執(zhí)行SP_HELPTEXT系統(tǒng)存儲(chǔ)過程可以顯示規(guī)那么、默認(rèn)值、未加密的存儲(chǔ)過程、用戶函數(shù)、觸發(fā)器或視圖的文本信息；執(zhí)行sp_depends系統(tǒng)存儲(chǔ)過程可以顯示有關(guān)數(shù)據(jù)庫對(duì)象相關(guān)性的信息；執(zhí)行sp_rename系統(tǒng)存儲(chǔ)過程可以更改當(dāng)前數(shù)據(jù)庫中用戶創(chuàng)立對(duì)象的名稱。SQLServer中許多管理工作是通過執(zhí)行系統(tǒng)存儲(chǔ)過程來完成的，許多系統(tǒng)信息也可以通過執(zhí)行系統(tǒng)存儲(chǔ)過程而獲得。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是sp_。在調(diào)用時(shí)不必在存儲(chǔ)過程前加上數(shù)據(jù)庫名。2．用戶存儲(chǔ)過程用戶存儲(chǔ)過程是指用戶根據(jù)自身需要，為完成某一特定功能，在用戶數(shù)據(jù)庫中創(chuàng)立的存儲(chǔ)過程。用戶創(chuàng)立存儲(chǔ)過程時(shí)，存儲(chǔ)過程名的前面加上“##〞，是表示創(chuàng)立全局臨時(shí)存儲(chǔ)過程。在存儲(chǔ)過程名前面加上“#〞，是表示創(chuàng)立局部臨時(shí)存儲(chǔ)過程。局部臨時(shí)存儲(chǔ)過程只能在創(chuàng)立它的會(huì)話中可用，當(dāng)前會(huì)話結(jié)束時(shí)除去。全局臨時(shí)存儲(chǔ)過程可以在所有會(huì)話中使用，即所有用戶均可以訪問該過程。它們都在tempdb數(shù)據(jù)庫上。存儲(chǔ)過程可以接受輸入?yún)?shù)、向客戶端返回表格或者標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言〔DDL〕和數(shù)據(jù)操作語言〔DML〕，然后返回輸出參數(shù)。在SQLServer2023中，用戶定義的存儲(chǔ)過程有兩種類型：Transact-SQL或者CLR，如表8-1所示。表8-1用戶定義存儲(chǔ)過程的兩種類型存儲(chǔ)過程類型說明Transact-SQLTransact-SQL存儲(chǔ)過程是指保存的Transact-SQL語句集合，可以接受和返回用戶提供的參數(shù)。存儲(chǔ)過程也可能從數(shù)據(jù)庫向客戶端應(yīng)用程序返回?cái)?shù)據(jù)。CLRCLR存儲(chǔ)過程是指對(duì)Microsoft.NETFramework公共語言運(yùn)行時(shí)方法的引用，可以接受和返回用戶提供的參數(shù)。他們?cè)?NETFramework程序集中是作為類的公共靜態(tài)方法實(shí)現(xiàn)的3．?dāng)U展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程以在SQLServer環(huán)境外執(zhí)行的動(dòng)態(tài)鏈接庫〔DLL，Dynamic-LinkLibrar-ies〕來實(shí)現(xiàn)。擴(kuò)展存儲(chǔ)過程通過前綴“xp_〞來標(biāo)識(shí)，它們以與存儲(chǔ)過程相似的方式來執(zhí)行。8.2使用存儲(chǔ)過程在使用存儲(chǔ)過程之前，首先需要?jiǎng)?chuàng)立一個(gè)存儲(chǔ)過程，這可以通過T-SQL語句CREATEPROCEDURE來完成。在使用的過程中，包括對(duì)存儲(chǔ)過程的執(zhí)行、查看和修改以及刪除操作。8.2.1創(chuàng)立存儲(chǔ)過程在SQLServer2023kh,可以使用T-SQL語句CREATEPROCEDURE來創(chuàng)立存儲(chǔ)過程。在創(chuàng)立存儲(chǔ)過程時(shí)，應(yīng)該指定所有的輸入?yún)?shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句、返回至調(diào)用過程或批處理時(shí)以示成功或失敗的狀態(tài)值、捕獲和處理潛在錯(cuò)誤時(shí)的錯(cuò)誤處理語句等。需要強(qiáng)調(diào)的是，必須具有CREATEPROCEDURE權(quán)限才能創(chuàng)立存儲(chǔ)過程，存儲(chǔ)過程是架構(gòu)作用域中的對(duì)象，只能在本地?cái)?shù)據(jù)庫中創(chuàng)立存儲(chǔ)過程。1．創(chuàng)立存儲(chǔ)過程的規(guī)那么在設(shè)計(jì)和創(chuàng)立存儲(chǔ)過程時(shí)，應(yīng)該滿足一定的約束和規(guī)那么。只有滿足了這些約束和規(guī)那么才能創(chuàng)立有效的存儲(chǔ)過程。CREATEPROCEDURE定義自身可以包括任意數(shù)量和類型的SQL語句，但表8-2中的語句除外。因?yàn)椴荒茉诖鎯?chǔ)過程的任何位置使用這些語句。表8-2CREATEPROCEDURE定義中不能出現(xiàn)的語句CREATEAGGREGATECREATERULECREATEDEFAULTCREATESCHEMACREATE或ALTERFUNCTIONCREATE或ALTERTRIGGERCREATE或ALTERPROCEDURECREATE.或ALTERVIEWSETPARSEONLYSETSHOWPLAN_ALLSETSHOWPLAN_TEXTSETSHOWPLAN_XMLUSEDatabase_name可以引用在同一存儲(chǔ)過程中創(chuàng)立的對(duì)象，只要引用時(shí)已經(jīng)創(chuàng)立了該對(duì)象即可?？梢栽诖鎯?chǔ)過程內(nèi)引用臨時(shí)表。如果在存儲(chǔ)過程內(nèi)創(chuàng)立本地臨時(shí)表，那么臨時(shí)表僅為該存儲(chǔ)過程而存在；退出該存儲(chǔ)過程后，臨時(shí)表將消失。如果執(zhí)行的存儲(chǔ)過程將調(diào)用另一個(gè)存儲(chǔ)過程，那么被調(diào)用的存儲(chǔ)過程可以訪問由第一個(gè)存儲(chǔ)過程創(chuàng)立的所有對(duì)象，包括臨時(shí)表在內(nèi)。如果執(zhí)行對(duì)遠(yuǎn)程SQLServer2023實(shí)例進(jìn)行更改的遠(yuǎn)程存儲(chǔ)過程，那么不能回滾這些更改，而且遠(yuǎn)程存儲(chǔ)過程不參與事務(wù)處理。存儲(chǔ)過程中的參數(shù)的最大數(shù)目為2100。存儲(chǔ)過程中的局部變量的最大數(shù)目僅受可用內(nèi)存的限制。根據(jù)可用內(nèi)存的不同，存儲(chǔ)過程最大可達(dá)128MB。2．存儲(chǔ)過程的語法使用CREATEPROCEDURE語句創(chuàng)立存儲(chǔ)過程的語法如下。CREATEPROCDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]其主要參數(shù)含義含義如下：Procedure_name新存儲(chǔ)過程的名稱。過程名稱在架構(gòu)中必須唯一，可在procedure_name前面使用一個(gè)數(shù)字符號(hào)“#〞來創(chuàng)立局部臨時(shí)過程，使用兩個(gè)數(shù)字符號(hào)“#〞來創(chuàng)立全局臨時(shí)過程。對(duì)于CLR存儲(chǔ)過程，不能指定臨時(shí)名稱。;number是可選的整數(shù)，用來對(duì)同名的過程分組。使用一個(gè)DROPPROCEDURE語句可將這些分組過程一起刪除。如果名稱中包含分隔標(biāo)識(shí)符，那么數(shù)字不應(yīng)該包含在標(biāo)識(shí)符中;只應(yīng)在procedure_name前使用分隔符。@parameter過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個(gè)或多個(gè)參數(shù)。除非定義了參數(shù)的默認(rèn)值或者將參數(shù)設(shè)置為等于另一個(gè)參數(shù)，否那么用戶必須在調(diào)用過程時(shí)為每個(gè)聲明的參數(shù)提供值，如果指定了FORREPLICATION，那么無法聲明參數(shù)。Data_type參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型均可以用作存儲(chǔ)過程的參數(shù)。不過cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor，那么還必須指定VARYING和OUTPUT關(guān)鍵字。對(duì)于CLR存儲(chǔ)過程，不能指定char,varchar,text,next,image,cursor和table作為參數(shù)。如果參數(shù)的數(shù)據(jù)類型為CLR用戶定義類型，那么必須對(duì)此類型有EXECUTE權(quán)限。Default參數(shù)的默認(rèn)值。如果定義了dafault值，那么無須指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或NULL。如果過程使用帶like關(guān)鍵字的參數(shù)，那么可包含以下通配符：%、_、[]、[^]。Output指示參數(shù)是輸出參數(shù)。此選項(xiàng)的值可以返回給調(diào)用EXECUTE的語句。使用OUTPUT參數(shù)將值返回給過程的調(diào)用方。除非是CLR過程，否那么text,ntext和image參數(shù)不能用作OUTPUT參數(shù)。OUTPUT關(guān)鍵字的輸出參數(shù)可以為游標(biāo)占位符，CLR過程除外，<sql_statement>要包含在過程中的一個(gè)或多個(gè)T-SQL語句中。3．使用圖形工具創(chuàng)立除了直接編寫T-SQL創(chuàng)立外，SQLServer2023還提供了一種簡便的方法，使用SQLServerManagementStudio工具。操作步驟如下：〔1〕翻開SQLServerManagementStudio窗口，連接到【BookDateBase】數(shù)據(jù)庫。〔2〕依次展開【效勞器】｜【數(shù)據(jù)庫】｜【BookDateBase】|【可編程性】節(jié)點(diǎn)?！?〕從列表中右擊【存儲(chǔ)過程】節(jié)點(diǎn)選擇【新建存儲(chǔ)過程】命令，然后將出現(xiàn)如圖8-1所示的顯示CREATEPROCEDURE語句的模板，可以修改要?jiǎng)?chuàng)立的存儲(chǔ)過程的名稱，然后參加存儲(chǔ)過程所包含的SQL語句。圖8-1創(chuàng)立存儲(chǔ)過程〔4〕修改完后，單擊【執(zhí)行】按鈕即可創(chuàng)立一個(gè)存儲(chǔ)過程。4．創(chuàng)立存儲(chǔ)過程的例如例如，在SQLServer2023的例如數(shù)據(jù)庫【BookDatebase】中創(chuàng)立一個(gè)名為Reader_proc的存儲(chǔ)過程，它將從表中返回所有讀者的姓名、姓別、、等級(jí)。使用CREATEPROCEDURE語句如下：UseBookDatebaseGoCREATEPROCEDUREReader_procAsSELECTRname,Rsex,Rphone,rleveFROMReader下面的存儲(chǔ)過程proc_GetCountsBook獲取了【BookDatebase】數(shù)據(jù)庫中圖書的總數(shù)量，具體語句如下所示：UseBookDatebaseGoCREATEPROCEDUREproc_GetCountsBookAsSELECTcount(ID)AS總數(shù)FROMBooks以上兩個(gè)存儲(chǔ)過程例如都是從單個(gè)表中提取數(shù)據(jù)，在第二個(gè)例如中使用了簡單的表達(dá)式。下面使用SELECT語句鏈接多個(gè)表，最終返回了借書人的簡明信息。存儲(chǔ)過程名稱是proc_BorRreader,創(chuàng)立語句如下：UseBookDatebaseGoCREATEPROCEDUREproc_BorR_readerAsSELECTB.Bnum,B.Bname,B.writer,R.Rcert,R.Rname,BR.botimeFromBooksB,ReaderR,BorrowORreturnBRWHEREB.Bnum=BR.BnumandR.Rcert=BR.RcertandBR.botime<>''8.2.2執(zhí)行存儲(chǔ)過程在需要執(zhí)行存儲(chǔ)過程時(shí)，可以使用T-SQL語句EXECUTE。如果存儲(chǔ)過程是批處理中的第一條語句，那么不使用EXECUTE關(guān)鍵字也可以執(zhí)行該存儲(chǔ)過程，EXECUTE語法格式如下：[{EXEC|EXECUTE}]{[@return_status=]{procedure_name[;number]|@procedure_name_var}@parameter=[{value|@variable[OUTPUT]|[DEFAULT]}][,…n][WITHRECOMPILE]其中主要參數(shù)的含義如下：@return_status是一個(gè)可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。這個(gè)變量在用于EXECUTE語句前，必須在批處理、存儲(chǔ)過程或函數(shù)中聲明過。Procedure_name要調(diào)用的存儲(chǔ)過程名稱。;number是可選的整數(shù)，用于將相同名稱的過程進(jìn)行組合，使得它們可以用一句DROPPROCEDURE語句刪除。在【BookDatebase】中使用的過程可以Reader_proc;1、proc_GetCountsBook；２等來命名。DROPPROCEDUREReader_proc語句將除去整個(gè)組。在對(duì)過程分組后，不能刪除組中的單個(gè)過程。例如，DROPPROCEDUREproc_GetCountsBook;２是不允許的。@procedure_name_var是局部定義變量名，代表存儲(chǔ)過程名稱。@parameter是過程參數(shù)，在CREATEPROCEDURE語句中定義。參數(shù)名稱前必須加上符號(hào)“@〞。Value是過程中參數(shù)的值。如果參數(shù)名稱沒有指定，參數(shù)值必須以CREATEPROCEDURE語句中定義的順序給出。如果參數(shù)值是一個(gè)對(duì)象名稱、字符串或通過數(shù)據(jù)庫名稱或所有者名稱進(jìn)行限制，那么整個(gè)名稱必須用單引號(hào)括起來。如果參數(shù)值是一個(gè)關(guān)鍵字，那么該關(guān)鍵字必須用雙引號(hào)括起來。@variable是用來保存參數(shù)或者返回參數(shù)的變量。OUTPUT指定存儲(chǔ)過程必須返回一個(gè)參數(shù)。該存儲(chǔ)過程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)立。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。DEFAULT根據(jù)過程的定義，提供參數(shù)的默認(rèn)值。當(dāng)過程需要的參數(shù)值是沒有事先定義好的默認(rèn)值，或缺少參數(shù)，或指定了DEFAULT關(guān)鍵字，就會(huì)出錯(cuò)。下面，我們通過EXECUTE語句來依次執(zhí)行節(jié)創(chuàng)立的3個(gè)存儲(chǔ)過程。首先是Reader_proc存儲(chǔ)過程，它位于【BookDatebase】數(shù)據(jù)庫中，使用語句如下：UseBookDatebaseGoEXECUTEReader_proc執(zhí)行上述語句后，結(jié)果如圖8-2所示。圖8-2執(zhí)行存儲(chǔ)過程Reader_proc然后再使用同樣的方法，執(zhí)行【BookDatebase】數(shù)據(jù)庫中的兩個(gè)存儲(chǔ)過程，結(jié)果分別如圖8-3和圖8-4所示。圖8-3執(zhí)行存儲(chǔ)過程proc_GetCountsBook圖8-4執(zhí)行存儲(chǔ)過程proc_BorR_reader運(yùn)行EXECUTE語句無須權(quán)限，但是需要對(duì)EXECUTE字符串內(nèi)引用的對(duì)象的權(quán)限。例如，如果字符串包含INSERT語句，那么EXECUTE語句的調(diào)用方對(duì)目標(biāo)表必須具有INSERT權(quán)限。除使用EXECUTE直接執(zhí)行外，還可以將存儲(chǔ)過程嵌入到INSERT語句中執(zhí)行。這樣操作時(shí)，INSERT語句將把本地或遠(yuǎn)程存儲(chǔ)過程返回的結(jié)果集參加到一個(gè)本地表中。SQLServer2023會(huì)將存儲(chǔ)過程中的SELECT語句返回的數(shù)據(jù)載入表中，前提是表必須存在并且數(shù)據(jù)類型必須匹配。8.2.3存儲(chǔ)過程參數(shù)存儲(chǔ)過程的優(yōu)勢(shì)不僅在于存儲(chǔ)在效勞器端、運(yùn)行速度快、還有重要的一點(diǎn)就是存儲(chǔ)過程可完成的功能非常強(qiáng)大，特別是在SQLServer2023中。本節(jié)將學(xué)習(xí)如何在存儲(chǔ)過程使用參數(shù)，包括輸入?yún)?shù)和輸出參數(shù)，以及參數(shù)的默認(rèn)值等。1．參數(shù)的定義ＳQLServer2023的存儲(chǔ)過程可以使用兩種類型的參數(shù)：輸入?yún)?shù)和輸出參數(shù)。參數(shù)用于在存儲(chǔ)過程以及應(yīng)用程序之間交換數(shù)據(jù)，其中：輸入?yún)?shù)允許用戶將數(shù)據(jù)值傳遞到存儲(chǔ)過程或函數(shù)。輸出參數(shù)允許存儲(chǔ)過程將數(shù)據(jù)值或游標(biāo)變量傳遞給用戶。每個(gè)存儲(chǔ)過程向用戶返回一個(gè)整數(shù)代碼，如果存儲(chǔ)過程沒有顯式設(shè)置返回代碼的值，那么返回代碼為0。存儲(chǔ)過程的參數(shù)在創(chuàng)立時(shí)應(yīng)在CREATEPROCEDURE和AS關(guān)鍵字之間定義，每個(gè)參數(shù)都要指定參數(shù)名和數(shù)據(jù)類型，參數(shù)名必須以@符號(hào)為前綴，可以為參數(shù)指定默認(rèn)值；如果是輸出參數(shù)，那么應(yīng)用OUTPUT關(guān)鍵描述。各個(gè)參數(shù)定義之間用逗號(hào)隔開，具體語法如下：@parameter_namedata_type[=default][OUTPUT]2．輸入?yún)?shù)輸入?yún)?shù)，即指在存儲(chǔ)過程中有一個(gè)條件，在執(zhí)行存儲(chǔ)過程時(shí)為這個(gè)條件指定值，通過存儲(chǔ)過程返回相應(yīng)的信息。使用輸入?yún)?shù)可以向同一存儲(chǔ)過程屢次查找數(shù)據(jù)庫。例如，可以創(chuàng)立一個(gè)存儲(chǔ)過程用于返回【BookDatebase】數(shù)據(jù)庫上某條借閱信息中包括的圖書名稱。通過為同一存儲(chǔ)過程指定不同的借閱者，來返回不同的圖書名稱。在節(jié)最后創(chuàng)立的存儲(chǔ)過程proc_BorR_reader只能對(duì)表進(jìn)行特定的查詢。假設(shè)要使這個(gè)存儲(chǔ)過程更加通用化、靈活且能夠查詢某個(gè)類別中相應(yīng)的圖書信息，那么讀者信息中的讀者卡號(hào)就應(yīng)該是可變的，這樣的存儲(chǔ)過程才能返回某個(gè)類別的圖書信息。在這個(gè)存儲(chǔ)過程上將一個(gè)讀者的卡號(hào)作為參數(shù)來實(shí)現(xiàn)，名稱為proc_GetReaderBooks,其代碼如下：USE[BookDateBase]GOCREATEPROCEDURE[dbo].[proc_GetReaderBooks]@RcertintAsSELECTB.Bnum,B.Bname,B.writer,R.Rname,BR.botime,R.RcertFromBooksB,ReaderR,BorrowORreturnBRWHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert以上代碼，創(chuàng)立一個(gè)名為proc_GetReaderBooks的存儲(chǔ)過程，使用一個(gè)字符串型的參數(shù)@Rcert來執(zhí)行。執(zhí)行帶有輸入?yún)?shù)的存儲(chǔ)過程時(shí)，SQLServer2023提供了如下兩種傳遞參數(shù)的方式。按位置傳遞這種方式是在執(zhí)行存儲(chǔ)過程的語句中，直接給出參數(shù)的值。當(dāng)有多個(gè)參數(shù)時(shí)，給出的參數(shù)的順序與創(chuàng)立存儲(chǔ)過程的語句中的參數(shù)的順序一致，即參數(shù)傳遞的順序就是參數(shù)定義的順序。使用這種方式執(zhí)行proc_GetReaderBooks存儲(chǔ)過程的代碼為：EXECproc_GetReaderBooks‘10010’這種方式是在執(zhí)行存儲(chǔ)過程的語句中，使用“參數(shù)名=參數(shù)值〞的形式給出參數(shù)值。通過參數(shù)名傳遞參數(shù)的好處是，參數(shù)可以以任意順序給出。用這種方式執(zhí)行proc_GetReaderBooks存儲(chǔ)過程的代碼如下，執(zhí)行結(jié)果如圖8-5所示。EXECproc_GetReaderBooks@Rcert=’10010’圖8-5執(zhí)行結(jié)果3．使用默認(rèn)參數(shù)值執(zhí)行存儲(chǔ)過程proc_GetReaderBooks時(shí)，如果沒有指定參數(shù)，那么系統(tǒng)運(yùn)行就會(huì)出錯(cuò)；如果希望不給出參數(shù)時(shí)也能夠正確運(yùn)行，那么可以給參數(shù)設(shè)置默認(rèn)值來實(shí)現(xiàn)。因此，如果要將proc_GetReaderBooks存儲(chǔ)過程修改為默認(rèn)值使用類別編號(hào)為10010的proc_GetReaderBooks，那么可以運(yùn)行以下代碼：USE[BookDateBase]GOCREATEPROCEDURE[dbo].[proc_GetReaderBooks]@Rcertint=10010AsSELECTB.Bnum,B.Bname,B.writer,R.Rname,BR.botime,R.RcertFromBooksB,ReaderR,BorrowORreturnBRWHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert4．輸出參數(shù)通過定義輸出參數(shù)，可以從存儲(chǔ)過程中返回一個(gè)或多個(gè)值。為了使用輸出參數(shù)，必須在CREATEPROCEDURE語句和EXECUTE語句中指定關(guān)鍵字OUTPUT。在執(zhí)行存儲(chǔ)過程時(shí)，如果忽略O(shè)UTPUT關(guān)鍵字，存儲(chǔ)過程仍會(huì)執(zhí)行但不返回值。USE[BookDateBase]GOCREATEPROCEDURE[dbo].[proc_GetReaderBookscount]@Rcertint=10010@bookcountsintOUTPUTAsSELECT@bookcount=COUNT(B.Bnum)FromBooksB,ReaderR,BorrowORreturnBRWHEREB.Bnum=BR.BnumANDR.Rcert=BR.RcertANDBR.botime<>''ANDBR.Rcert=@Rcert以上代碼創(chuàng)立一個(gè)名為proc_GetReaderBooks1的存儲(chǔ)過程，它使用兩個(gè)參數(shù)：@Rcert為輸出參數(shù)，用于指定要查詢的讀者編號(hào)，默認(rèn)參數(shù)值為10010；@bookcounts為輸出參數(shù)，用來返回讀者借閱的圖書數(shù)量。為了接收某一存儲(chǔ)過程的返回值，需要一個(gè)變量來存放返回參數(shù)的值，在該存儲(chǔ)過程的調(diào)用語句中，必須為這個(gè)變量加上OUTPUT關(guān)鍵字來聲明。下面的代碼顯示了如何調(diào)用proc_GetReaderBooks1，并將得到的結(jié)果返回到@bookcounts中，其運(yùn)行結(jié)果如圖8-6所示。USE[BookDateBase]GODECLARE@bookcountintEXECproc_GetReaderBookscount10001,@bookcountOUTPUTSELECT'讀者共借閱圖書：'+STR(@bookcount)+'本'GO圖8-6帶輸出參數(shù)的存儲(chǔ)過程5．存儲(chǔ)過程的返回值存儲(chǔ)過程在執(zhí)行后都會(huì)返回一個(gè)整形值。如果執(zhí)行成功，那么返回0；否那么返回-1到-99之間的隨機(jī)數(shù)，也可以使用RETURN語句來指定一個(gè)存儲(chǔ)過程的返回值。例如，下面創(chuàng)立一個(gè)名為aAndb的存儲(chǔ)過程，用以計(jì)算出兩個(gè)參數(shù)的和。本例使用SET語句，但是也可以使用SELECT語句來組織一個(gè)字符串，語句如下：CREATEPROCaANDb@aint=0,@bint=0,@cint=0OUTPUTASSet@c=@a+@bReturn@c@c參數(shù)由OUTPUT關(guān)鍵字指定。在執(zhí)行這個(gè)存儲(chǔ)過程時(shí)，需要指定一個(gè)變量存放返回值，然后再顯示出來。如下所示為一個(gè)調(diào)用這個(gè)存儲(chǔ)過程的例如：DECLARE@intcintEXECaANDb6,2,@intcOUTPUTSELECT‘兩個(gè)之和為：’+STR(@INTC)執(zhí)行如果如圖8-7所示。圖8-7執(zhí)行aANDb結(jié)果8.2.4刪除存儲(chǔ)過程使用DROPPROCEDURE語句來從當(dāng)前的數(shù)據(jù)庫中刪除用戶定義的存儲(chǔ)過程。刪除存儲(chǔ)過程的根本語法如下所示。DROPPROCEDURE{procedure}[,…n]下面的語句將刪除aANDb存儲(chǔ)過程：DROPPROCaANDb如果另一個(gè)存儲(chǔ)過程調(diào)用某個(gè)已被刪除的存儲(chǔ)過程，SQLServer2023將在執(zhí)行調(diào)用進(jìn)程時(shí)顯示一條錯(cuò)誤消息。但是，如果定義了具有相同名稱和參數(shù)的新存儲(chǔ)過程來替換已被刪除的存儲(chǔ)過程，那么引用該過程的其他過程仍能成功執(zhí)行。8.2.5管理存儲(chǔ)過程在SQLServer2023系統(tǒng)中，可以使用OBJECT_DEFINITION系統(tǒng)函數(shù)查看存儲(chǔ)過程的內(nèi)容：使用ALTERPROCEDURE語句修改已經(jīng)存儲(chǔ)過程。1．查看存儲(chǔ)過程信息在SQLServer2023系統(tǒng)中，可以使用系統(tǒng)存儲(chǔ)過程和目錄視圖查看有關(guān)存儲(chǔ)過程的信息。如果希望查看存儲(chǔ)過程的定義信息，可以使用sys.sql_modules目錄視圖、OBJECT_DEFINITION系統(tǒng)函數(shù)、sp_helptext系統(tǒng)存儲(chǔ)過程等。例如，下面代碼使用OBJECT_DEFINITION系統(tǒng)函數(shù)查看proc_GetReaderBookscount存儲(chǔ)過程的定義內(nèi)容。SELECTOBJECT_DEFINITION(OBJECT_ID(N’proc_GetReaderBookscount’))在創(chuàng)立存儲(chǔ)過程時(shí)使用了WITHENCRYPTION子句，那么將隱藏存儲(chǔ)過程定義文本的信息，上面將不能查看到具體的文本信息。還可以使用sys.sql_dependencies對(duì)象目錄視圖、sp_depends系統(tǒng)存儲(chǔ)過程等可以查看存儲(chǔ)過程的依賴信息。使用sys.objects、cedure、sys.parameters、sys.numbered_procedures等目錄視圖可以查看有關(guān)存儲(chǔ)過程的名稱、參數(shù)等信息。2．修改存儲(chǔ)過程使用ALTERPROCEDURE語句來修改現(xiàn)有的存儲(chǔ)過程與刪除和重建存儲(chǔ)過程不同，因?yàn)樗员３执鎯?chǔ)過程的權(quán)限不發(fā)生變化。在使用ALTERPROCEDURE語句修改存儲(chǔ)過程時(shí)，SQLServer2023會(huì)覆蓋以前定義的存儲(chǔ)過程。修改存儲(chǔ)過程的根本語句如下：ALTERPROCEDUREprocedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]修改存儲(chǔ)過程的語法中的各參數(shù)與創(chuàng)立存儲(chǔ)過程語法中的各參數(shù)相同，這里就不在重復(fù)介紹。在使用ALTERPROCEDURE語句時(shí)，考慮以下方面的事項(xiàng)：如果要修改具有任何選項(xiàng)的存儲(chǔ)過程，例如WITHENCRYPTION選項(xiàng)，必須在ALTERPROCEDURE語句中包括該選項(xiàng)以保存該選項(xiàng)提供的功能。ALTERPROCEDURE語句只能修改一個(gè)單一的過程，如果過程調(diào)用了其他存儲(chǔ)過程，嵌套的存儲(chǔ)過程不受影響。在默認(rèn)狀態(tài)下，允許該語句的執(zhí)行者是存儲(chǔ)過程最初的創(chuàng)立者、sysadmin效勞器角色成員和db_owner與db_ddladmin固定的數(shù)據(jù)庫角色成員，用戶不能授權(quán)執(zhí)行ALTERPROCEDURE語句。建議不要直接修改系統(tǒng)存儲(chǔ)過程，相反，可以通過從現(xiàn)有的存儲(chǔ)過程中復(fù)制語句來創(chuàng)立用戶定義的系統(tǒng)存儲(chǔ)過程，然后修改它以滿足要求。8.3其他存儲(chǔ)過程在SQLServer2023中內(nèi)置了許多存儲(chǔ)過程，它們有時(shí)也被稱為系統(tǒng)存儲(chǔ)過程。同時(shí)，SQLServer2023還支持?jǐn)U展存儲(chǔ)過程，即調(diào)用第三方DLL文件的能力，通常它們與系統(tǒng)存儲(chǔ)過程一塊使用。8.3.1系統(tǒng)存儲(chǔ)過程在SQLServer2023中，許多管理活動(dòng)和信息活動(dòng)都可以使用系統(tǒng)存儲(chǔ)過程來執(zhí)行，這些系統(tǒng)存儲(chǔ)過程可分為表8-3所示的幾類。表8-3系統(tǒng)存儲(chǔ)過程分類類型描述活動(dòng)目錄存儲(chǔ)過程用于在Windows的活動(dòng)目錄中注冊(cè)SQLServer實(shí)例和SQLServer數(shù)據(jù)庫目錄訪問存儲(chǔ)過程用于實(shí)現(xiàn)ODBC數(shù)據(jù)字典功能，并且隔離ODBC應(yīng)用程序，使之不受根底系統(tǒng)表更改的影響游標(biāo)過程存儲(chǔ)用于實(shí)現(xiàn)游標(biāo)變量功能數(shù)據(jù)庫引擎存儲(chǔ)過程用于SQLServer數(shù)據(jù)庫引擎的常規(guī)維護(hù)數(shù)據(jù)庫郵件和SQLMail存儲(chǔ)過程用于從SQLServer實(shí)例內(nèi)執(zhí)行電子郵件操作數(shù)據(jù)庫維護(hù)方案存儲(chǔ)過程用于設(shè)置管理數(shù)據(jù)庫性能所需的核心維護(hù)任務(wù)分布式查詢存儲(chǔ)過程用于實(shí)現(xiàn)和管理分布式查詢?nèi)乃阉鞔鎯?chǔ)過程用于實(shí)現(xiàn)和查詢?nèi)乃饕罩緜魉痛鎯?chǔ)過程用于配置、修改和監(jiān)視日志傳送配置自動(dòng)化存儲(chǔ)過程用于在Transact-SQL批處理中使用OLE自動(dòng)化對(duì)象通知效勞存儲(chǔ)過程用于管理MicrosoftSQLServer2023系統(tǒng)的通知效勞復(fù)制存儲(chǔ)過程用于管理復(fù)制操作平安性存儲(chǔ)過程用于管理平安性Porfile存儲(chǔ)過程在SQLServer代理用于管理方案的活動(dòng)和事件驅(qū)動(dòng)活動(dòng)Web任務(wù)存儲(chǔ)過程用于創(chuàng)立網(wǎng)頁XML存儲(chǔ)過程用于XML文本管理雖然SQLServer2023中的系統(tǒng)存儲(chǔ)過程被放在master數(shù)據(jù)庫中，但是仍可以在其他數(shù)據(jù)庫中對(duì)其進(jìn)行調(diào)用，而且在調(diào)用時(shí)不必在存儲(chǔ)過程名前加上數(shù)據(jù)庫名。甚至當(dāng)創(chuàng)立一個(gè)新數(shù)據(jù)庫時(shí)，一些系統(tǒng)存儲(chǔ)過程會(huì)在新數(shù)據(jù)庫中被自動(dòng)創(chuàng)立。SQLServer2023支持表8-4所示的系統(tǒng)存儲(chǔ)過程，這些存儲(chǔ)過程用于對(duì)SQLServer2023實(shí)例進(jìn)行常規(guī)維護(hù)。表8-4系統(tǒng)存儲(chǔ)過程sp_add_data_file_recover_suspect_dbsp_helpsp_recompilesp_addextendedprocsp_helpconstraintsp_refreshviewsp_addextendedpropertysp_helpdbsp_releaseapplocksp_add_log_file_recover_suspect_dbsp_helpdevicesp_renamesp_addmessagesp_helpextendedprocsp_renamedbsp_addtypesp_helpfilesp_resetstatussp_addumpdevicesp_helpfilegroupsp_serveroptionsp_altermessagesp_helpindexsp_setnetnamesp_autostatssp_helplanguagesp_settriggerordersp_attach_dbsp_helpserversp_spaceusedsp_attach_single_file_dbsp_helpsortsp_tableoptionsp_bindefaultsp_helpstatssp_unbindefaultsp_bindrulesp_helptextsp_unbindrulesp_updateextendedpropertysp_helptriggersp_bindsessionsp_certify_removablesp_indexoptionsp_updatestatssp_configuresp_invalidate_textptrsp_validnamesp_control_plan_guidesp_locksp_whosp_create_plan_guidesp_monitorsp_createstatssp_create_removablesp_procoptionsp_cycle_errorlogsp_datatype_infosp_detach_dbsp_executesqlsp_dbcmptlevelsp_dropdevicesp_getapplocksp_dboptionsp_dropextendedprocsp_getbindtokensp_dropextendedpropertysp_dbremovesp_droptypesp_delete_backuphistorysp_dropmessagesp_depends8.3.2擴(kuò)展存儲(chǔ)過程擴(kuò)展存儲(chǔ)過程就是保存在動(dòng)態(tài)鏈接庫〔DLL〕中從動(dòng)態(tài)鏈接中執(zhí)行的C++代碼。在多數(shù)擴(kuò)展存儲(chǔ)過程與其他系統(tǒng)存儲(chǔ)過程一起執(zhí)行，因此它們很少單獨(dú)使用，下面列出了２個(gè)可以單獨(dú)使用的擴(kuò)展存儲(chǔ)過程：Xp_cmdshell用于執(zhí)行命令提示符下的DOS程序。例如，dir命令和md命令〔更改目錄〕。在需要SQLServer2023創(chuàng)立一個(gè)用來自動(dòng)存檔BulkCopyProgram〔BCP〕文件或此類文件的目錄時(shí)，可以使用該存儲(chǔ)過程。Xp_fileexist用于測(cè)試文件是否存在，可以使用該存儲(chǔ)過程。例如，下面代碼演示了如何使用xp_fileexist測(cè)試Ｃ盤下的boot.ini文件是否面存在。如果@Result等于1，那么文件存在；如果等于0，那么文件不存在。具體語句如下所示：USEMasterGODECLARE@ResultintEXECxp_fileexist‘c:\boot.ini’,@ResultOUTPUTSELECT@ResultAS是否存在這里對(duì)上述語句簡單說明一下：第３行聲明一個(gè)保存輸出參數(shù)的變量，第４行用一個(gè)輸出參數(shù)調(diào)用該過程，第５行顯示輸出結(jié)果，這里要注意的是必須在主數(shù)據(jù)庫Master中進(jìn)行。執(zhí)行上述語句，運(yùn)行結(jié)果如圖8-8所示。圖8-8測(cè)試文件是否存在例如，要獲取當(dāng)前SQLServer2023效勞器的計(jì)算機(jī)名稱，可以使用擴(kuò)展存儲(chǔ)過程完成，語句如下：EXECUTEMASTER..XP_GETNETNAME運(yùn)行上面的執(zhí)行語句結(jié)果如圖8-9所示。圖8-9查看計(jì)算機(jī)名稱其他的擴(kuò)展存儲(chǔ)過程包括如下常規(guī)幾個(gè)：Xp_enumgroups提供Windows本地組列表或在指定WINDOWS域中定義的全局組列表。Xp_findnextmsg接受輸入的郵件ID并返回輸出的郵件ID，需要與xp_processmail配合使用。Xp_grantlogin授予WINDOWS組或用戶對(duì)SQLSERVER的訪問權(quán)限。Xp_logevent將用戶定義消息記入SQLSERVER日志文件和WINDOWS事件查看器。Xp_loginconfig報(bào)告SQLSERVER2023實(shí)例在WINDOWS上運(yùn)行時(shí)的登錄平安配置。Xp_logininfo報(bào)告賬戶、賬戶類型、賬戶的特權(quán)級(jí)別、賬戶的映射登錄名和賬戶訪問SQLSERVER的權(quán)限路徑。Xp_msver返回有關(guān)SQLSERVER2023的版本信息Xp_revokelogin撤銷WINDOWS組或用戶對(duì)SQLSERVER的訪問權(quán)限。Xp_sprintf設(shè)置一系列字符和值的格式并將其存儲(chǔ)到字符串輸出參數(shù)中。每個(gè)格式參數(shù)都用相應(yīng)的參數(shù)替換。Xp_sqlmaint用包含SQLMAINT開關(guān)的字符串調(diào)用SQLMAINT實(shí)用工具，在一個(gè)或多個(gè)數(shù)據(jù)庫上執(zhí)行一系列維護(hù)操作。Xp_sscanf將數(shù)據(jù)從字符串讀入每個(gè)格式參數(shù)所指定的參數(shù)位置。8.4異常處理與調(diào)試當(dāng)代碼產(chǎn)生錯(cuò)誤時(shí)，在該場合下代碼將不可能繼續(xù)運(yùn)行該代碼，因?yàn)樗玫降慕Y(jié)果是錯(cuò)誤結(jié)果。這時(shí)候就用到了異常處理和調(diào)試。8.4.1異常處理在SQLSERVER中進(jìn)行異常處理時(shí)，要理解的第一件事情是系統(tǒng)中沒有可用的“異常處理器〞機(jī)制。如果錯(cuò)誤發(fā)生，那么在該場合下將不可能繼續(xù)運(yùn)行該代碼，因?yàn)樗媒Y(jié)果將是錯(cuò)誤結(jié)果。足以產(chǎn)生運(yùn)行時(shí)錯(cuò)誤的嚴(yán)重錯(cuò)誤給SQLSERVER帶來了兩方面的問題：一方面，所有當(dāng)前的數(shù)據(jù)訪問的對(duì)象模型都傳遞了錯(cuò)誤消息，別一方面，在客戶端應(yīng)用程序中存在這樣的錯(cuò)誤都可以進(jìn)行適當(dāng)處理。1．處理內(nèi)嵌錯(cuò)誤內(nèi)嵌錯(cuò)誤是一種令人討厭的錯(cuò)誤，而且一直會(huì)讓SQLSERVER繼續(xù)運(yùn)行，卻不能得到我們期望的成功結(jié)果。在內(nèi)嵌錯(cuò)誤產(chǎn)生的執(zhí)行結(jié)果中，一般來說，錯(cuò)誤號(hào)是可以利用的一點(diǎn)。利用@@ERROR@@ERROR包含了最后一條T-SQL語句執(zhí)行的錯(cuò)誤號(hào)。如果值為0，那么表示沒有錯(cuò)誤發(fā)生。每次都用新的語句對(duì)@@ERROR這種警告復(fù)位，這意味著如果想要延遲分析值，或者想屢次再使用該值，就需要將該值移入到其他地方存儲(chǔ)起來，為此定義了一個(gè)局部變量。實(shí)際上，過程訪問信息的唯一局部就是錯(cuò)誤號(hào)。該錯(cuò)誤號(hào)駐留在@@ERROR中，用于下一條T-SQL語句，在下一條語句中，該錯(cuò)誤號(hào)就會(huì)消失。@ERROR和@@ERROR是兩個(gè)完全不同的變量，而且可以獨(dú)立引用。這不是因?yàn)榇笮懙膮^(qū)別〔取決于效勞器是怎么配置的，區(qū)分大小寫會(huì)影響變量名〕，而是因?yàn)榉秶膮^(qū)別。@和@@是變量名的一局部，所以前面的@符號(hào)數(shù)就將彼此區(qū)分開了。在過程中使用@@ERROR可以利用IF…ELSE語句和@@ERROR〔如果能立即測(cè)試該值并只需要測(cè)試一次〕或者局部變量〔在該變量中，已經(jīng)移入了以前的@@ERROR的值〕來完成。2．在錯(cuò)誤發(fā)生之前處理錯(cuò)誤有時(shí)SQLSERVER并沒有真正有效的方式確定發(fā)生的錯(cuò)誤到底是什么，這時(shí)，如果想在錯(cuò)誤發(fā)生之前阻止錯(cuò)誤的發(fā)生，就需要檢查程序并提前加以處理。3．手工提示錯(cuò)誤有時(shí)會(huì)遇到SQLSERVER實(shí)際并不知道的一些錯(cuò)誤，但我們希望它知道。例如，我們不希望返回-100。相反，希望能在客戶端產(chǎn)生運(yùn)行錯(cuò)誤，而客戶端使用的時(shí)候能夠喚醒異常處理并進(jìn)行相應(yīng)的處理。要完成這一點(diǎn)，就需要在T-SQL中使用RAISERROR命令。語法非常簡單：RAISERROR(<messageID|messagestring>,<severity>,<state>[,<argument>[,<…n>]])[WITHoption[,…n]]消息ID/消息串消息ID或者消息串決定了發(fā)送到客戶端的消息。使用消息ID創(chuàng)立一個(gè)手工提示錯(cuò)誤，該錯(cuò)誤有指定的ID與在master數(shù)據(jù)庫中的sysmessages表中找到的ID相關(guān)的消息。也可以不用特定的文本形式提供消息串，這樣可以不用在sysmessages中產(chǎn)生永久的消息。錯(cuò)誤處理等級(jí)錯(cuò)誤等級(jí)是對(duì)該錯(cuò)誤有多嚴(yán)重的指示，本質(zhì)上它們可以在信息級(jí)別〔錯(cuò)誤嚴(yán)重等級(jí)1-18〕到系統(tǒng)級(jí)〔19-25〕之間變動(dòng)。如果提供一個(gè)錯(cuò)誤嚴(yán)重等級(jí)為19的錯(cuò)誤或更高〔系統(tǒng)層〕級(jí)的錯(cuò)誤，那么也必須指定WITHLOG選項(xiàng)。20或者更高的號(hào)會(huì)自動(dòng)終止用戶的連接。SQLSERVER實(shí)際上的變化范圍比WINDOWSNT的還要大，它們主要分成6組。如表8-5所示。表8-5錯(cuò)誤嚴(yán)重等級(jí)錯(cuò)誤嚴(yán)重等級(jí)解釋1-9純粹只是信息，但返回消息信息中的特定錯(cuò)誤代碼。不管在RAISERROR中設(shè)置了什么，都將提供相同值作為代碼〔不要問為什么，變是這樣處理的〕10也是信息，但不會(huì)在客戶端產(chǎn)生錯(cuò)誤，而且除了提供錯(cuò)誤文本以外，不會(huì)提供特定錯(cuò)誤信息。11-16這些值會(huì)終止存儲(chǔ)過程的執(zhí)行，而且在客戶端產(chǎn)生錯(cuò)誤。從這一點(diǎn)向前看，該狀態(tài)顯示的值就是所設(shè)置的值。17通常，只有SQLSERVER使用該錯(cuò)誤嚴(yán)重等級(jí)。根本上，它指示SQLERVER已經(jīng)用盡了所有資源且不能滿足需要18-19這些都是嚴(yán)重錯(cuò)誤，而且暗示著需要系統(tǒng)管理員注意的潛在原因。對(duì)于19，需要使用WITHLOG選項(xiàng)，如果使用了OS系列，那么事件將顯示在WINDOWSNT或WINDOWS2000的事件日志中。20-25本質(zhì)上，這是一個(gè)致使錯(cuò)誤，連接被終止。對(duì)于19，我們必須使用WITHLOG選項(xiàng)，如果可以使用的話，消息將顯示在事件日志中狀態(tài)狀態(tài)是一個(gè)特定值，它能識(shí)別在代碼中的多個(gè)位置處發(fā)生的錯(cuò)誤。該概念是使我們有時(shí)機(jī)為確實(shí)發(fā)生的錯(cuò)誤發(fā)送位置標(biāo)志。狀態(tài)值可以是1-127之間的任意值。錯(cuò)誤參數(shù)一些預(yù)先定義的錯(cuò)誤可以接受參數(shù)。通過改變錯(cuò)誤的指定屬性允許錯(cuò)誤做動(dòng)態(tài)修改。也可以指定錯(cuò)誤消息的格式以接受參數(shù)。當(dāng)希望在某種靜態(tài)錯(cuò)誤信息中利用動(dòng)態(tài)信息的時(shí)候，需要規(guī)定信息的固定局部的格式，以便可以在參數(shù)化的局部留有足夠空間?？梢杂谜嘉环幚恚嘉环幚?。WITH<option>在枚舉一個(gè)錯(cuò)誤時(shí)，可以混合使用三個(gè)選項(xiàng)：LOG,SETERROR,NOWAIT。WITHLOG(采用日志)：告訴SQLSERVER將錯(cuò)誤記錄到SQLSERVER的錯(cuò)誤日志和WINDOWSNT應(yīng)用程序日志中。這種選項(xiàng)用于錯(cuò)誤嚴(yán)重等級(jí)是19或者更高的錯(cuò)誤。WITHSETERROR〔采用SETERROR〕：在默認(rèn)情況下，RAISERROR命令不用產(chǎn)生的錯(cuò)誤值設(shè)置@@ERROR,相反，@@ERROR將影響RAISERROR命令的成功與失敗。SETERROR克服了這一點(diǎn)并設(shè)置@@ERROR的值等于錯(cuò)誤ID。WITHNOWAIT(不等待)：立即向客戶端通報(bào)錯(cuò)誤。4．添加自己定制的錯(cuò)誤消息可以使用特定的系統(tǒng)存儲(chǔ)過程將消息添加到系統(tǒng)中。該過程叫叫sp_addmessage,語法如下所示：Sp_addmessage[@msgnum=]<msgid>,[@severity=]<severity>,[@msgtext=]<’msg’>[,[@lang=]<’language’>][,[@with_log=][TRUE|FALSE]][,[@replace=]’replace’]@lang說明該消息所應(yīng)用的語言。優(yōu)點(diǎn)是可以為syslanguages中支持的任何語言提供消息的不同版本。@with_log這與它在RAISERROR中的工作方式相同，如果將其設(shè)置為TRUE，那么在錯(cuò)誤產(chǎn)生時(shí)自動(dòng)把錯(cuò)誤消息記錄到SQLSERVER的錯(cuò)誤日志和WINDOWSNT應(yīng)用程序日志中。這里的技巧是要通過將該參數(shù)值設(shè)置為TRUE而不是使用WITHLOG選項(xiàng)設(shè)置來記錄該消息。@REPLACE如果在編輯一條現(xiàn)有的消息而不是創(chuàng)立一條新的消息，那么就必須將@replace參數(shù)設(shè)置為“REPLACE〞。如果省略了這一點(diǎn)，那么一旦消息已經(jīng)存在就會(huì)出錯(cuò)。使用sp_admessage使用sp_addmessage創(chuàng)立消息的方式與使用RAISERROR創(chuàng)立特定消息的方式相同。刪除現(xiàn)有的定制消息：要?jiǎng)h除定制消息，可以使用:sp_dropmessage<msgnum>8.4.2調(diào)試SQLSERVER2023刪除了所有高度功能〔把調(diào)試功能放到了產(chǎn)品中，但是要獲得高度功能，必須使用作為BusinessIntelligenceDevelopmentStudio一局部的VisualStudio安裝程序〕。不過快樂的是，調(diào)試工具仍在ManagementStudio中，甚至比以前更好了。1．啟動(dòng)調(diào)試器SQLSERVER2023中的調(diào)試器很容易找到。使用調(diào)試器的方法與VB或C#中是一樣的，就此而言，可能像大多數(shù)現(xiàn)代調(diào)試器。只需選擇“調(diào)試〞菜單〔當(dāng)“查詢〞窗口活動(dòng)時(shí)可用〕。然后從選項(xiàng)中選擇啟動(dòng)方式：StartDebugging(Alt+F5)或StepInto(F11)。2．調(diào)試器的組成當(dāng)首次彈出“調(diào)試〞窗口時(shí)，需要注意左邊的黃色箭頭指示了當(dāng)前執(zhí)行行，如果選擇“運(yùn)行〞或是開始單步執(zhí)行代碼，那么這就是下一行將要執(zhí)行的代碼。如圖8-10所示。圖8-10當(dāng)前執(zhí)行行-黃色箭頭頂部有一些圖標(biāo)來指示不同的選項(xiàng)，圖8-11所示。圖8-11調(diào)試器“繼續(xù)〞，這將運(yùn)行至存儲(chǔ)過程的末尾或下一個(gè)斷點(diǎn)?！爸鹫Z句〞，這行將運(yùn)行下一行代碼并且在運(yùn)行接下來的代碼行前停止，而不管代碼位于哪個(gè)過程或函數(shù)中。如果執(zhí)行的當(dāng)前代碼行調(diào)用一個(gè)存儲(chǔ)過程或函數(shù)，那么“逐語句〞選項(xiàng)會(huì)去調(diào)用該存儲(chǔ)過程或函數(shù)，把它添加到調(diào)用堆棧中，使本地窗口顯示新嵌套的存儲(chǔ)過程而不是父存儲(chǔ)過程，并且在嵌套的存儲(chǔ)過程的第一行代碼處停止。“逐過程〞，這會(huì)執(zhí)行轉(zhuǎn)到調(diào)用堆棧中同一層的上一條語句必須的每一行代碼。如果沒有調(diào)用另外一個(gè)存儲(chǔ)過程和UDF，那么這個(gè)命令和“逐語句〞選項(xiàng)一樣。如果調(diào)用了另一個(gè)存儲(chǔ)過程或UDF，那么“逐過程〞選項(xiàng)會(huì)轉(zhuǎn)到緊接著那個(gè)存儲(chǔ)過程或UDF返回它的值的位置的語句?！疤雳?，這會(huì)執(zhí)行到調(diào)用堆棧中下一個(gè)最高點(diǎn)為止的每一行代碼。也就是說，會(huì)一直運(yùn)行下去，直到到達(dá)了與當(dāng)前所處的代碼調(diào)用層次相同的那一層次?！巴Ｖ拐{(diào)試〞，它的功能是立即停止執(zhí)行。但是調(diào)試窗口仍然是翻開的?！皵帱c(diǎn)〞，可以通過單擊代碼窗口的左邊空白區(qū)域來設(shè)置斷點(diǎn)。設(shè)置斷點(diǎn)是用來告訴SQLSERVER當(dāng)在調(diào)試模式下運(yùn)行代碼時(shí)在此處停止。如果對(duì)于不想處理每一行代碼大型存儲(chǔ)過程或函數(shù)，這就很有用，只是希望它運(yùn)行到某一點(diǎn)并且每次到達(dá)該處時(shí)停止。3．使用調(diào)試器翻開了調(diào)試器窗口，下面就開始高度代碼。如果你已經(jīng)開始了一局部調(diào)試，那么可以選擇關(guān)閉調(diào)試器并重啟它。這里的存儲(chǔ)過程的第一個(gè)執(zhí)行的代碼行具有一些欺騙性，它是@WorkingIn的聲明語句。通常，變量聲明不是可執(zhí)行的，但這里，將初始化變量作為聲明的一局部，因此調(diào)試器看到了初始化代碼。8.5認(rèn)識(shí)觸發(fā)器觸發(fā)器與存儲(chǔ)過程非常相似，觸發(fā)器也是SQL語句集，兩者惟一的區(qū)別是觸發(fā)器不能用EXECUTE語句調(diào)用，而是在用戶執(zhí)行Transact-SQL語句時(shí)自動(dòng)觸發(fā)〔激活〕執(zhí)行。下面將對(duì)觸發(fā)器的概念以及類型進(jìn)行詳細(xì)介紹。8.5.1觸發(fā)器概述觸發(fā)器是一個(gè)在修改指定表中的數(shù)據(jù)時(shí)執(zhí)行的存儲(chǔ)過程。經(jīng)常通過創(chuàng)立觸發(fā)器來強(qiáng)制實(shí)現(xiàn)不同表中的邏輯相關(guān)數(shù)據(jù)的引用完整性或者一致性。由于用戶不能繞過觸發(fā)器，所以可以用它來強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)那么，以此確保數(shù)據(jù)的完整性。觸發(fā)器不同于前面介紹的存儲(chǔ)過程。觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過程可以通過存儲(chǔ)過程名字而被直接調(diào)用。當(dāng)對(duì)某一表進(jìn)行諸如UPDATE、INSERT、DELETE這些操作時(shí)，SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)那么。1．觸發(fā)器的作用觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。它能夠?qū)?shù)據(jù)庫中的相關(guān)表進(jìn)行級(jí)聯(lián)修改，強(qiáng)制比CHECK約束更復(fù)雜的數(shù)據(jù)完整性，并自定義錯(cuò)誤消息，維護(hù)非標(biāo)準(zhǔn)化數(shù)據(jù)以及比擬數(shù)據(jù)修改前后的狀態(tài)。與CHECK約束不同，觸發(fā)器可以引用其他表中的列。在以下情況下，使用觸發(fā)器將強(qiáng)制實(shí)現(xiàn)復(fù)雜的引用完整性：強(qiáng)制數(shù)據(jù)庫間的引用完整性。創(chuàng)立多行觸發(fā)器，當(dāng)插入、更新或者刪除多行數(shù)據(jù)時(shí)，必須編寫一個(gè)處理多行數(shù)據(jù)的觸發(fā)器。執(zhí)行級(jí)聯(lián)更新或級(jí)聯(lián)刪除這樣的動(dòng)作。級(jí)聯(lián)修改數(shù)據(jù)庫中所有相關(guān)表。撤銷或者回滾違反引用完整性的操作，防止非法修改數(shù)據(jù)。2．與存儲(chǔ)過程的區(qū)別觸發(fā)器與存儲(chǔ)過程主要的區(qū)別在于觸發(fā)器的運(yùn)行方式。存儲(chǔ)過程必須由用戶、應(yīng)用程序或者觸發(fā)器來顯示式地調(diào)用并執(zhí)行，而觸發(fā)器是當(dāng)特定事件出現(xiàn)的時(shí)候，自動(dòng)執(zhí)行或者激活的，與連接到數(shù)據(jù)庫中的用戶或者應(yīng)用程序無關(guān)。當(dāng)一行被插入、更新或者從表中刪除時(shí)觸發(fā)器才運(yùn)行，同時(shí)這還取決于觸發(fā)器是怎樣創(chuàng)立的。在數(shù)據(jù)修改時(shí)，觸發(fā)器是強(qiáng)制業(yè)務(wù)規(guī)那么的一種很有效的方法。一個(gè)表最多有三種不同類型的觸發(fā)器，當(dāng)UPDATE發(fā)生時(shí)使用一個(gè)觸發(fā)器；DELETE發(fā)生時(shí)使用一個(gè)觸發(fā)器；INSERT發(fā)生時(shí)使用一個(gè)觸發(fā)器。盡管觸發(fā)器的功能強(qiáng)大，但是它們也可能對(duì)效勞器的性能很有害。因此，要注意不要在觸發(fā)器中放置太多的功能，因?yàn)樗鼘⒔档晚憫?yīng)速度，使用戶等待的時(shí)間增加。8.5.2觸發(fā)器的分類在SQLServer2023系統(tǒng)中，按照觸發(fā)事件的不同可以把提供的觸發(fā)器分成兩大類型：DML觸發(fā)器和DDL觸發(fā)器。1．DDL觸發(fā)器DDL觸發(fā)器當(dāng)效勞器或者數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言〔DDL〕事件時(shí)將被調(diào)用。如果要執(zhí)行以下操作，可以使用DDL觸發(fā)器：要防止對(duì)數(shù)據(jù)庫架構(gòu)進(jìn)行某些更改希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改要記錄數(shù)據(jù)庫架構(gòu)中的更改或者事件2．DML觸發(fā)器DML觸發(fā)器是當(dāng)數(shù)據(jù)庫效勞器中發(fā)生數(shù)據(jù)操作語言〔DML〕事件時(shí)要執(zhí)行的操作。通常所說的DML觸發(fā)器主要包括三種：INSERT觸發(fā)器、UPDATE觸發(fā)器、DELETE觸發(fā)器。DML觸發(fā)器可以查詢其他表，還可以包含復(fù)雜的Transact-SQL語句。將觸發(fā)器和觸發(fā)他的語句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待。如果檢測(cè)到錯(cuò)誤，那么整個(gè)事務(wù)自動(dòng)回滾。DML觸發(fā)器在以下方面非常有用：DML觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)更改。不過，通過級(jí)聯(lián)引用完整性約束可以更有效地進(jìn)行這些更改DML觸發(fā)器可以防止惡意或者錯(cuò)誤的INSERT、UPDATE以及DELETE操作，并強(qiáng)制執(zhí)行比CHECK約束定義的限制更為復(fù)雜的其他限制。DML觸發(fā)器能夠引用其他表中的列DML觸發(fā)器可以評(píng)估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施一個(gè)表中的多個(gè)同類DML觸發(fā)器〔INSERT、UPDATE和DELETE〕允許采取多個(gè)不同的操作來響應(yīng)同一個(gè)修改語句SQLServer2023為每個(gè)觸發(fā)器語句都創(chuàng)立了兩種特殊的表：DELETED表和INSERTED表。這是兩個(gè)邏輯表，由系統(tǒng)來自創(chuàng)立和維護(hù)，用戶不能對(duì)他們進(jìn)行修改。他們存放在內(nèi)存而不是數(shù)據(jù)庫中。這兩個(gè)表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表的結(jié)構(gòu)相同。觸發(fā)器執(zhí)行完成后，與該觸發(fā)器相關(guān)的這兩個(gè)表也會(huì)被刪除。DELETE表存放由執(zhí)行DELETE或者UPDATE語句而要從表中刪除的所有行。在執(zhí)行DELETE或者UPDATE操作時(shí)，被刪除的行從觸發(fā)觸發(fā)器的表中被移動(dòng)到DELETE表，這兩個(gè)表不會(huì)有共同的行。INSERT表存放由執(zhí)行INSERET或者UPDATE語句而要向表中插入的所有行。在執(zhí)行INSERT或者UPDATE事務(wù)中，新的行同時(shí)添加到觸發(fā)觸發(fā)器的表和INSERT表中，INSERT表的內(nèi)容是觸發(fā)觸發(fā)器的表中新行的副本。一個(gè)UPDATE事務(wù)可以看作先執(zhí)行一個(gè)DELETE操作，再執(zhí)行一個(gè)INSERT操作，舊的行首先被移動(dòng)到DELETE表，然后新行同時(shí)插入觸發(fā)觸發(fā)器的表和INSERT表。8.5.3觸發(fā)器完整性規(guī)那么在SQLSERVER2023中，維護(hù)數(shù)據(jù)的完整性和一致性叫完整性規(guī)那么。而完整性規(guī)那么分為引用完整性規(guī)那么和數(shù)據(jù)完整性規(guī)那么。1．引用完整性規(guī)那么到目前為止，提供執(zhí)行完整性檢查的唯一方法是使用DRI〔聲明的引用完整性〕，但這不是唯一的選項(xiàng)。實(shí)際上，直到6.5版本為止，DRI在以前的版本中甚至還不是一個(gè)選項(xiàng)，那里執(zhí)行完整性檢查都是用觸發(fā)器來完成的。觸發(fā)器仍然是維護(hù)引用完整性的一種最好選擇。雖然它們的速度有點(diǎn)慢，但人們認(rèn)為它們?cè)诰S護(hù)數(shù)據(jù)完整性上更靈活。正因如此，所以有幾種關(guān)系〔處理方法〕只能通過觸發(fā)器來執(zhí)行。使用觸發(fā)器的關(guān)系的例子包括：一對(duì)一的關(guān)系、排斥關(guān)系、需要跨越數(shù)據(jù)庫或效勞器邊界的情況。可能不有很多類似的例子，具體有多少取決于用戶的特定需要。這就是觸發(fā)器的最大特點(diǎn)，它們具有最大的靈活性。使用觸發(fā)器維護(hù)簡單的引用完整性：除了前面列出的所有技巧以外，觸發(fā)器還可以用于完成DRI能完成的相同的、簡單的引用完整性。一般而言，這不是我們所希望采取的方法，但有時(shí)又無法防止。其實(shí)很簡單。唯一的技巧在于：只有是一對(duì)０或多對(duì)多的而不是一對(duì)多的關(guān)系，才能發(fā)生這一現(xiàn)象。注意，就像以前介紹的一樣，我們可以用觸發(fā)器為DELETE語句產(chǎn)生的錯(cuò)誤創(chuàng)立一條定制信息。使用觸發(fā)器得到更靈活的引用完整性：以前的DRI只執(zhí)行兩種關(guān)系：一對(duì)一關(guān)系，或者一對(duì)零、一或多。另一個(gè)不能滿足DRI常規(guī)要求的例子是排它子類關(guān)系。在這種關(guān)系中，父表擁有的消息可能與許多子表相似，但是，父表中的每行只有唯一的一條記錄與子表的一條記錄相匹配。對(duì)于這種關(guān)系，我們只能使用觸發(fā)器作為唯一解決方案。雖然會(huì)聽說使用DRI是為了獲得更好的性能，但是DRI不能處理如子類這樣的復(fù)雜成員。此時(shí)采用觸發(fā)器可以出色地完成任務(wù)。２．?dāng)?shù)據(jù)完整性規(guī)那么觸發(fā)器既能實(shí)現(xiàn)外部鍵約束的關(guān)系，也能實(shí)現(xiàn)如CHECK約束甚至DEFAULT約束的相同功能。像觸發(fā)器與DRI一樣，需要根據(jù)實(shí)現(xiàn)情況何時(shí)使用觸發(fā)器以及何時(shí)使用使用CHECK約束。如果CHECK約束能完成該工作，或者在檢查處理中繼承的一些內(nèi)容使得效果更不理想，就采用觸發(fā)器。利用觸發(fā)器替代CHECK約束的例子有：業(yè)務(wù)規(guī)那么需要引用另一張的參考數(shù)據(jù)、業(yè)務(wù)規(guī)那么需要檢查更新的中間數(shù)據(jù)和需要一個(gè)定制的錯(cuò)誤消息。處理其他表的請(qǐng)求。雖然CHECK約束不僅快而且效率高，但它們不會(huì)按照所期望的方式完成所有工作。大概CHECK約束的最大缺點(diǎn)就是它要顯示何時(shí)需要驗(yàn)證看得到表中的數(shù)據(jù)。記住，如果愿意，也可以創(chuàng)立一個(gè)定制錯(cuò)誤信息，代替使用RAISERROR命令的特殊信息。使用觸發(fā)器檢查被更新的中間數(shù)據(jù)。有時(shí)，我們對(duì)過去或現(xiàn)在的值不感興趣，只希望知道變化值是多少。雖然沒有列或表提供這些變化信息，但我們可以利用觸發(fā)器中的INSERTED表和DELETED表進(jìn)行計(jì)算。使用觸發(fā)器定制錯(cuò)誤信息。在其他一些例子中，我們已經(jīng)提到了這一點(diǎn)，但是要記住，當(dāng)希望控制錯(cuò)誤信息或者給用戶或客戶應(yīng)用程序傳遞錯(cuò)誤號(hào)時(shí)，使用觸發(fā)器非常方便。例如利用CHECK約束，我們將得到標(biāo)準(zhǔn)547號(hào)錯(cuò)，而不是不可名狀的解釋。通常，從用戶實(shí)際指出發(fā)生什么錯(cuò)誤的角度出發(fā)，這樣的錯(cuò)誤提示不會(huì)給他們提供太大的幫助，實(shí)際上，客戶應(yīng)用程序通常沒有足夠信息為用戶的行為做出智能化、有幫助的響應(yīng)。簡而言之，雖然有時(shí)創(chuàng)立觸發(fā)器會(huì)提供預(yù)期的數(shù)據(jù)完整性，但是它不會(huì)提供足夠的處理。8.5.4創(chuàng)立觸發(fā)器對(duì)于不同的觸發(fā)器，其創(chuàng)立的語法多數(shù)相似，其區(qū)別與定義表示觸發(fā)器的特性有關(guān)。創(chuàng)立一個(gè)觸發(fā)器定義的根本語法如下：CREATETRIGGERtrigger_nameON{table|view}{{{FOR|AFTER|INSTEADOF}{[delete][,][insert][,][update]}ASSql_statement}}在DELETETRIGGER的語法中，各主要參數(shù)含義如下：Trigger_name是要?jiǎng)?chuàng)立的觸發(fā)器的名稱。Ｔable|view是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖。可以選擇是否指定表或視圖的所有者名稱。FOR,AFTER,INSTEADOF指定觸發(fā)器觸發(fā)的時(shí)機(jī)，其中FOR也創(chuàng)立AFTER觸發(fā)器。DELETE,INSERT,UPDATE是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時(shí)將觸發(fā)觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。Sql_statement指定觸發(fā)器所執(zhí)行的T-SQL語句。例如，下面的語句演示了在【BookDatebase】數(shù)據(jù)庫的【Books】表上創(chuàng)立了一個(gè)名為BooksBcountUpdate的觸發(fā)器，在用戶向表中執(zhí)行UPDATE操作時(shí)觸發(fā)。ALTERTRIGGER[dbo].[BooksBcountUpdate]ON[dbo].[Books]AFTERUPDATEASifUPDATE(Bcount)BEGINupdateBookManagesetBookManage.Bcount=i.BcountfromBookManageB,insertedi,deleteddwhereB.Bnum=d.BnumEND8.6使用觸發(fā)器在SQLSERVER2023中，通常使用的觸發(fā)器分為兩類：DML觸發(fā)器和DDL觸發(fā)器。同時(shí)觸發(fā)器也具有了可遞歸和可嵌套性。8.6.1DML觸發(fā)器本節(jié)主要介紹如何創(chuàng)立不同DML類型的觸發(fā)器。在SQLSERVER2023中DML觸發(fā)器可以使用3種類型：AFTER觸發(fā)器在執(zhí)行了INSERT,UPDATE或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。指定AFTER與指定FOR相同，是SQLSERVER早期版本中唯一可用的選項(xiàng)。AFTER觸發(fā)器只能在表上指定。INSTEADOF觸發(fā)器執(zhí)行INSTEADOF觸發(fā)器代替通常的觸發(fā)動(dòng)作。還可為帶有一個(gè)或多個(gè)基表的視圖定義INSTEADOF觸發(fā)器，而這些觸發(fā)器能夠擴(kuò)展視圖可支持的更新類型。CLR觸發(fā)器CLR觸發(fā)器將執(zhí)行在托管代碼〔在.NETFramework中創(chuàng)立并在SQLSERVER中加載的程序集的成員〕中編寫的方法，而不用執(zhí)行T-SQL存儲(chǔ)過程。１.AFTER觸發(fā)器創(chuàng)立DML觸發(fā)器前應(yīng)考慮以下問題，如表8-5所示。表8-5DML觸發(fā)器考慮的問題編號(hào)問題1CREATETRIGGER語句必須是批處理中的第一個(gè)語句，該語句后面的所有其他語句被解釋為CREATETRIGGER語句定義的一局部。2創(chuàng)立DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所在者，且不能將該權(quán)限轉(zhuǎn)給其他用戶。3DML觸發(fā)器為數(shù)據(jù)庫對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)那么。4雖然DML觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)立DML觸發(fā)器。5雖然DML觸發(fā)器可以引用臨時(shí)表，但不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)立DML觸發(fā)器。不應(yīng)引用系統(tǒng)表，而應(yīng)使用信息架構(gòu)視圖。6對(duì)于含有用DELETE或UPDATE操作定義的外鍵表，不能定義INSTEADOFDELETE和INSTEADOFUPDATE觸發(fā)器。7雖然TRUNCATETABLE語句類似于不帶WHERE子句的DELETE語句〔用于刪除所有行〕，但它并不會(huì)觸發(fā)DELETE觸發(fā)器，因?yàn)門RUNCATETABLE語句沒有記錄8WRITETEXT語句不會(huì)觸發(fā)INSERT或UPDATE觸發(fā)器。9在DML觸發(fā)器中不能出現(xiàn)以下T-SQL語句：CREATEDATABASE,ALTERDATABASE,DROPDATABASE,RESTOREDATABASE,RESTORELOG,CREATEINDEX,ALTERINDEX,DROPINDEX,RECONFIGURE等語句。１.INSERT觸發(fā)器INSERT觸發(fā)器就是當(dāng)對(duì)目標(biāo)表〔觸發(fā)器的基表〕執(zhí)行INSERT語句時(shí)，就會(huì)調(diào)用的觸發(fā)器。例如，當(dāng)管理員每次向數(shù)據(jù)庫中添加新的圖書的時(shí)候輸出當(dāng)前類別中的圖書總量，這個(gè)觸發(fā)器名稱為BookClassCounts，定義語句如下：createTRIGGERBookClassCountsON[dbo].[Books]AFTERinsertASselectcount(B.bigClass)as'類別'fromBooksB,insertediwhereB.bigClass=i.bigClass接下來，使用INSERT語句插入一個(gè)新的訂單，以驗(yàn)證觸發(fā)器是否會(huì)自動(dòng)執(zhí)行。測(cè)試語句如下：insertintoBooksvalues('100102','再見艷陽天',’惜月’,'上海出版社','1987-1-5',20,'文學(xué)','現(xiàn)代文學(xué)',5,'童年故事')執(zhí)行上述語句后，運(yùn)行結(jié)果如圖8-12所示。圖8-12INSERT觸發(fā)器２．UPDATE觸發(fā)器更新觸發(fā)器是當(dāng)一個(gè)UPDATE語句在目標(biāo)表上運(yùn)行的時(shí)候，就調(diào)用更新觸發(fā)器。就像任何其他觸發(fā)器一樣，當(dāng)調(diào)用觸發(fā)器，就運(yùn)行被觸發(fā)的SQL語句并且發(fā)生動(dòng)作。例如，數(shù)據(jù)庫【BookDateBase】中【Books】表中圖書編號(hào)發(fā)生改變時(shí)，【BorrowORreturn】表中圖書編號(hào)也發(fā)生改變。這個(gè)存儲(chǔ)過程為BooksBnumUpdate，語句如下所示：createTRIGGER[dbo].[BooksBnumUpdate]/*當(dāng)書號(hào)改變時(shí)*/ON[dbo].[Books]AFTERUPDATEASifUPDATE(Bnum)BEGINupdateBorrowORreturnsetBorrowORreturn.Bnum=i.BnumfromBorrowORreturnB,insertedi,deleteddwhereB.Bnum=d.BnumEND使用UPDATE更新圖書編號(hào)，驗(yàn)證觸發(fā)器是否會(huì)自動(dòng)執(zhí)行。測(cè)試語句如下,執(zhí)行語句后結(jié)果如圖8-13所示。updateBookssetBnum='9787532743513'whereBnum='123'8-13UPDATE觸發(fā)器例如3．DELETE觸發(fā)器當(dāng)觸發(fā)DELETE觸發(fā)器時(shí)，從受影響的表中刪除的行將被放置到一個(gè)特殊的DELETE表中。DELETE表跟INSERTED表一樣也是一個(gè)臨時(shí)表，它保存已被刪除數(shù)據(jù)行的一個(gè)副本。DELETED表還允許引用由初始化DELETE語句產(chǎn)生的日志數(shù)據(jù)。使用DELETE觸發(fā)器時(shí)，需要考慮以下的事項(xiàng)和原那么：當(dāng)某行被添加到DELETED表中時(shí)，它就不再存在于數(shù)據(jù)庫中，因此，DELETE表和數(shù)據(jù)庫表沒有相同的行；創(chuàng)立DELETE表時(shí)，空間是從內(nèi)存中分配的。DELETED表總是被存儲(chǔ)在調(diào)整緩存中。為DELETE動(dòng)作定義的觸發(fā)器并不執(zhí)行TRUNCATETABLE語句，原因在于日志不記錄TRUNCATETABLE語句。例如在刪除數(shù)據(jù)庫【BookDateBase】中【Reader】表的讀書信息時(shí)，相應(yīng)的借閱表中的借閱信息也應(yīng)該被刪除掉。這個(gè)存儲(chǔ)過程是ReaderDelete，語句如下所示。ALTERTRIGGER[dbo].[ReaderDelete]ON[dbo].[Reader]AFTERDELETEASdeleteBorrowORreturnfromBorrowORreturnb,deleteddwhereb.Rcert=d.Rcert2．INSTEADOF觸發(fā)器INSTEADOF觸發(fā)器用于代替通常的觸發(fā)操作〔AFTER觸發(fā)器〕，SQLSERVER2023中支持帶有一個(gè)或多個(gè)基表的視圖定義INSTEADOF觸發(fā)器，這些觸發(fā)器可以擴(kuò)展視圖可支持的更新類型。對(duì)于每一種觸發(fā)動(dòng)作〔INSERT,UPDATE或DELETE〕，每一個(gè)表或視圖只能有一個(gè)INSTEADOF觸發(fā)器?？梢栽诒砘蛘咭晥D上指定INSTEADOF觸發(fā)器，用INSTEADOF觸發(fā)器可以指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語句，從而屏蔽原來的SQL語句，而轉(zhuǎn)向執(zhí)行觸發(fā)器內(nèi)部的SQL語句。對(duì)于每一種觸發(fā)動(dòng)作〔INSERT、UPDATE或者DELETE〕，每一個(gè)表或者視圖只能有一個(gè)INSTEADOF觸發(fā)器。INSTEADOF觸發(fā)器的主要優(yōu)點(diǎn)是可以使不能更新的視圖支持更新。基于多個(gè)基表的視圖必須使用INSTEADOF觸發(fā)器來支持引用多個(gè)表中數(shù)據(jù)的插入、更新和刪除操作。INSTEADOF觸發(fā)器的另一個(gè)優(yōu)點(diǎn)是使用戶可以編寫這樣的邏輯代碼：在允許批處理的其他局部成功的同時(shí)拒絕批處理中的某些局部。例如，通常不能在一個(gè)基于聯(lián)接的視圖上進(jìn)行DELETE操作。然而，可以編寫一個(gè)INSTEADOFDELETE觸發(fā)器來實(shí)現(xiàn)刪除。8.6.2DDL觸發(fā)器SQLSERVER2023中，可以對(duì)整個(gè)效勞器或數(shù)據(jù)庫的某個(gè)范圍為DDL的事件整個(gè)定義觸發(fā)器。像常規(guī)觸發(fā)器一樣，DDL觸發(fā)器將激發(fā)存儲(chǔ)過程以響應(yīng)事件。但與DML不同的是，它們不會(huì)為響應(yīng)針對(duì)表或視圖的UPDATE,INSERT或DELETE語句而激發(fā)。相反，它們會(huì)為響應(yīng)多種數(shù)據(jù)定義語言〔DDL〕語句而激發(fā)。這些語句主要是以CREATE,ALTER和DROP開頭的語句。DDL觸發(fā)器可用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫操作。如果要執(zhí)行以下操作，可以使用DDL觸發(fā)器：要防止對(duì)數(shù)據(jù)庫架構(gòu)進(jìn)行某些更改；希望數(shù)據(jù)庫中發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫架構(gòu)中的更改；要記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。例如，如何使用DDL觸發(fā)器來防止在【BookDateBase】數(shù)據(jù)庫中表被修改或刪除的操作。首先在【BookDateBase】數(shù)據(jù)庫中定義一個(gè)數(shù)據(jù)庫級(jí)的DDL觸發(fā)器，如下所示。USE[BookDateBase]GOcreateTRIGGER[TRIG_DDL]ONDATABASEFORDROP_TABLE，DROP_TABLEASBEGINPRINT'無法修改或者刪除表，請(qǐng)?jiān)诓僮髦敖没騽h除DDL觸發(fā)器TRIG_DDL!'ROLLBACKTRANSACTIONEND接下來，在數(shù)據(jù)庫中執(zhí)行刪除manage表的操作：droptableManage執(zhí)行上述語句，會(huì)出現(xiàn)錯(cuò)誤信息，如圖8-14所示。同樣，如果執(zhí)行了ALTER操作，仍會(huì)出現(xiàn)下面的錯(cuò)誤信息。圖8-14執(zhí)行DDL觸發(fā)器8.6.3嵌套觸發(fā)器如果一個(gè)觸發(fā)器在執(zhí)行操作時(shí)引發(fā)了另一個(gè)觸發(fā)器，而這個(gè)觸發(fā)器又接著引發(fā)下一個(gè)觸發(fā)器，那么就形成了觸發(fā)器的嵌套。任何觸發(fā)器都可以包含影響另一個(gè)表的UPDATE、INSERT或者DELETE語句。嵌套觸發(fā)器在安裝時(shí)就被啟用，但是可以使用系統(tǒng)存儲(chǔ)過程sp_configure禁用和重新啟用嵌套。觸發(fā)器最多可以嵌套32層，如果嵌套鏈中的任何觸發(fā)器建立了無窮循環(huán)，那么這將超過最大嵌套層數(shù)。該觸發(fā)器將被終止，并回滾整個(gè)事務(wù)。嵌套觸發(fā)器具有多種用途，比方：保存由前一觸發(fā)器所影響的行的備份副本。使用嵌套觸發(fā)器時(shí)，需要考慮以下的事項(xiàng)和原那么：默認(rèn)情況下，嵌套觸發(fā)器配置選項(xiàng)是開啟的。在同一個(gè)觸發(fā)器事務(wù)中，一個(gè)嵌套觸發(fā)器不能被觸發(fā)兩次