存儲過程與觸發(fā)器(超詳細)

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