8提高數據庫性句和流程控制語句的集合。就本質而言，觸發器也是一種過程。過程在運算時生成執行方式，所以，之后對其再運算時其執行速度變快。SQLServer2000中提供了許多可作為工具使用的系統過程。本章將學習SQLServer2000中過觸發器的使用。重點講解過觸發器的創建、過程的參數使用。通過本章學習，讀者可掌握過觸發器的創建，掌握過觸發器的的管理，并能熟練的使用它們。為Student_info表創建過程Student_info_pro，該過程中包Student_id、Name、Address l段為Student_info表創建 過程Student_info_pro1，該過程根據傳為Student_info表創建一帶默認值的過程Student_info_pro2，用來查詢是20歲的同學信息。為Student_info表創建過程Student_info_pro3，用來返回學生的為Student_info表創建過程Student_info_pro4，該過程根據查1個應用系統似乎并不難，但是要想使系統達到最優化的性能并不是一件容易的程度上發揮出SQL的潛能。它支持用戶提供的變量，可以返回、修改值，也可以將用戶8.1過程概Sybase公司80年代末期在它的SQLServer產品中首創了過程(StoredProcedures)使用過程還可以提高性能。過程開始執行后，其中包含的SQL語句在數據庫服。而且過程實際上只是在第一次使用時被編譯，編譯后的映像和過程在，安全性高。通過對執行某一過程的權限進行限制，從而實現對相應的數據訪問權限的限制，避免非用戶對數據的，保證數據的安全。 。當使用的過程的數量非常多時，每個 在 8.1.2過程的類系統過程就是系統創建的過程，目的在于能夠方便地從系統表中查詢信息或綴，在master數據庫中，為檢索系統表的信息提供快捷方式。盡管這些過程前加上數據庫名。而且在創建一個新的數據庫時，一些系統過程會在新數據庫中自動通過程，沒有前綴“sp_”。種過程稱為局部臨時過程，只能在一個用戶會話中使用；如果在本地過程前過程指服務器上的過程，也就是非本地服務器上的過程。8.2過程的創在一個過程中，可以使用任何SQL語句，但是不包括下面的語句：CREATE使用Transact-SQLCREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE|ENCRYPTION| PILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...nprocedure_name表示新過程的名稱。過程名必須符合標識符規則，且對于數(##)。完整的名稱（包括#或##）過128個字符。指定過程所有者的名稱orders的應用程序使用的過程可@parameter是過程中的參數。可以一個或多個參數。用戶必須在執行過程時data_type是參數的數據類型。所有數據類型均可以用作過程的參數。不過，cursorOUTPUTcursor，也必須同時指定VARYING和OUTPUT關鍵字。VARYING認值必須是常量或NULL。OUTPUT表明參數是返回參數。該選項的值可以返回給EXEC[UTE]。使用sql_statementSQL語句。n表示此過程可以包含任意數目和類型的Transact-SQL語句。 l這段【任務分析】使用CREATEPROC語句創建此過程，其兩個參數的值分別為 lUSEStudentCREATEPROCEDUREStudent_info_proSELECTstudent_id,name,Address, FROMStudent_info圖8-1使用Transact-SQL語句創建過程后在企業管理器中的顯在企業管理器其中用鼠標右鍵單擊要創建過程的數據庫下的“過程”選項，彈出快捷菜單，如圖8-2所示。8-2彈出快在彈出的快捷菜單中單擊“新建過程”命令，打開“過程屬性”如果要檢查用于創建過程的語法，可以單擊“檢查語法”按鈕。若腳本中沒有語法錯誤，將會彈出提示“語法檢查成功”的框，單擊“確定”按鈕，保存\administrator，PROCEDURENAME=Student_info_pro。過程的執行語句為select lfromStudent_info。在企業管理器其中用鼠標右鍵單擊Student數據庫下的“過程”選項，在彈出快捷菜單，單擊“新建過程”命令，打開“過程屬性”框。在“文本”列的語句，如圖8-3。圖8-3“過程屬性”查成功”的框，如圖8-4所示。單擊“確定”按鈕，保存過程定義。圖8-4“語法檢查成功”的框，在該框中選中“數據庫”下的“創建過程向導”選項。單擊“確定”按單擊“下一步”按鈕，打開“正在完成創建過程向導”框，該框中用于確認過程信息。在該框中選中其中一個過程，單擊“編輯”按鈕，打開“編輯過程屬性”框，在該框中的“名稱”文本框中修改該過程的名單擊“編輯SQL”按鈕，彈出“編輯過程SQL”框，用戶可以在已Student_info，操作可何一項或全部選擇，在“編輯過程SQL”框中將PROCEDURENAME改為Student_info_pro，將操作改為查詢 l和Address段的值。框，在該框中選中“數據庫”下的“創建過程向導”選項，如圖8-5所示。圖8-5“選擇向導” 圖8-6“歡迎使用創建過程向導”稱”下拉列表中選擇Student數據庫，如圖8-7所示。”以及該表后的插入、刪除和更新復選框，如圖8-8所示。”圖8-7“選擇數據庫” 圖8-8“創建過程向導”框中選中insert_Student_info_1過程，如圖8-9所示。單擊“編輯”按鈕，打開“編輯過程屬性”框，在該框中的“名 圖8-9“正在完成創建過程向導” 圖8-10“編輯過程屬性”如圖8-11：8-完成對過程的創建。如圖8-11。8.3執行過過程可以由應用程序通過一個調用來執行，而且允許用戶變量和有條件執可用EETE句來執這個過程。EXECUTE語句的語法格式如下：[[[EXEC[UTE]{[[@return_status={procedure_name[;number]|}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][ PILEEXECUTE語句中各參數的含義如下：@return_status是一個可選的整型變量，保存 于EXECUTE語句前，必須在批處理、 加上符號(@)。在以“@parameter_namevalue”格式使用時，參數名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現。value是過程中參數的值。如果參數名稱沒有指定，參數值必須以CREATEPROCEDURE語句中定義的順序給出。OUTPUT指定過程必須返回一個參數。該過程的匹配參數也必須由關鍵字OUTPUT創建。 PILE強制編譯新的計劃。該選項不能用于擴展過程。建議盡【任務8-4】通過執行名為Student_info_pro的過程，查看Student_info表中【任務分析】使用EXECUTE語句來執行這個過程，其參數的值USEUSEStudentEXECUTE圖8-12執行過8.4過程的參從過程中返回。輸入參數必須以@提示符開始，而且這些參數必須是Transcat-SQL的合法數據類型。輸出參數也必須以@提示符開始。另外，OUTPUT關鍵字必須緊跟著輸出【任務8-5】前面創建的過程是顯示表中的所有記錄，下面創建另一個過【任務分析】使用CREATEPROC語句創建此過程，其各參數的值分別為：SELECTStudent_id,Name,Address,lFROMStudent_infoWHERESex=@Sex_pro。USEUSEStudentCREATEPROCEDUREStudent_info_pro1@Sex_prochar(2)SELECTStudent_id,Name,Address, FROMStudent_infoWHERESexEXECUTE'男8-13使用參數傳有默認參數的過程，并使用一個默認值來執行過程。【任務8-6】創建一個帶有默認值參數的過程Student_info_pro2，用來查詢Student_info表中字段為20的記錄。【任務分析】使用CREATEPROC語句創建此過程，其各參數的值分別為=CREATECREATEPROCEDUREStudent_info_pro2(@ageInt=20)WHEREAge=@ageexec8-量。下面將創建帶OUTPUT的返回參數的過程。【任務分析】任務8-7涉及到CREATEPRODE語句的應用，過程（procedure_name）為Student_info_pro2；過程參數@id用來傳入學生編號；過程參數@addressOUTPUT類型，用以返回學生地址信息；sql語句為根據輸入的參數查詢學生USEUSEStudentCREATEPROCEDUREStudent_info_pro3@idInt,@addressVARCHAR(50)OUTPUTSELECT@address=addressFROMStudent_infoWHEREDECLAREDECLARE@address1EXECUTE1002,@address=@address1OUTPUTPRINT@address18-15使用OUTPUT的返回參RETURN0~-14OUTPUT參數面將創建帶RETURN的過程。【任務8-8】創建過程Student_info_pro4，該過程為帶有RETURN語句和參且這一字段值所對應記錄的Address字段值為“唐山”，該過程的返回值為error。Student_info_pro4，過程參數（@parameter）為@Student_id，用來傳入學生編號信息；RETURN語句根據查詢情況返回執行狀態。USEUSEStudentCREATEPROCEDUREStudent_info_pro4@Student_idint(4)(SELECTAddress(SELECTAddressWHEREStudent_id@Student_id)=’唐山RETURN@@ERROR8-16RETURN語8.5過程的查看、修改和刪在數據庫建立一個過程后，可以查看該過程的定義和相關性等。如果對已存在的過程不滿意，可以對該過程進行修改。而對于不再需要的過程，可以將8.5.1過程的查創建過程后，可以用企業管理器或有關的系統過程來查看該過程的信在企業管理器中，選擇相應數據庫下的“過程”選項，在詳細信息列表框中右鍵單擊要查看的過程，在彈出的快捷菜單中單擊“屬性”命令，打開“過程屬性”框，在該框中列出了過程的定義信息。這里可以通過單擊“權限”按在企業管理器中，選擇相應數據庫下的“過程”選項，在詳細信息列表框Student_infoStudent_info_pro過程，在彈出的快捷菜單中單擊“屬性”命令，打開“過程屬性”框，在該框中列出了過程的定義信息，如圖8-17所示。在企業管理器中，用鼠標右鍵單擊Student數據庫下Student_info過程，在彈出的快捷菜單中依次選擇“所有任務”|“顯示相關性”選項，打開“相關性”框，在該框可以查看依附于該過程的對象和該過程依附的對象，如圖8-18所示。圖8-17“過程屬性” 圖8-18“相關性”[EXECUTE]sp_help[EXECUTE]sp_help【任務分析】要查看的過程維Student_info_pro，使用sp_helptext查看其定義，使用sp_help查看其參數，使用sp_depends查看其相關性，這三個系統過程的參數都是USEUSEsp_helptextStudent_info_prosp_helpStudent_info_pro圖8-19查看過程的定義、參數和相關8.5.2過程的修在創建過程后，用戶可能需要對過程進行修改。修改過程通常指編輯它的參數和SQL語句。在企業管理器中，選擇相應數據庫下的“過程”選項，在詳細信息列表框中右鍵單擊要修改的過程，然后選擇“屬性”選項，打開“過程屬性”框。在“文本”編輯框中編輯過程的參數和SQL語句但是不能修改CREATEPROCEDURE如果要設置過程的權限，可通過單擊“權限”按鈕，打開“對象屬性”對話框，如圖8-20所示。在此可指定過程的權限。圖8-20“對象屬性”l l字段去掉。在企業管理器中，選擇Student數據庫下的“過程”選項，在詳細信息列表框中右鍵單擊Student_info_pro過程，然后選擇“屬性”選項，打開“過程屬性”框。在“文本”編輯框中的SELECT后的 l字段去掉，如圖8-21所示。（與圖8-17進行對比）。單擊“確定”按鈕，Student_info_pro過程修改成功。圖8-21修改過除了使用企業管理器外，還可以使用ALTERPROCEDURE語句修改過程，但該語句不會更改權限，也不影響相關的過程。其語法格式與創建過程的語法格式相ALTERALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT][,...n][WITH PILE| PILE,}][FORREPLICATION]sql_statement[...nVARYING為(procedure_name)Student_info_pro，過程要執行的sql語句為查詢Student_info表中的Student_id，Name和Address三個字段。USEUSEStudentALTERPROCEDUREStudent_info_proFROMStudent_info8.5.3過程的刪 DROPPROCEDURE{procedure}[,...nDROPPROCEDURE{procedure}[,...nDROPPROCEDURE語句中參數的procedure是要刪除的過程或過程組的名稱。過程名稱必須符合標識符規觸發器的CHECK約束更為復雜的觸發器可以通過數據庫中的相關表進行級聯修改。例如，可以在pubs表的id列上作為唯一鍵，在title、sales及emp表中對各匹配行進行定位。CHECKDROPLOADDISKDISKLOAD觸發器的CREATETRIGGER語句來完成。觸發器定義語句的INSERT、DELETE、UPDATE語Student，數據表為Student_infoStudent_info_tri，執行語句為輸出字符串“操作已執行”。Student_info表，在彈出的快捷菜單中選擇“所有任務”|“管理觸發器”命令，TRIGGERNAMEStudent_info_triAS后輸入“PRINT'數據插入'”，如圖8-23所示。圖8-23“觸發器屬性”CREATECREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION{{{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION][{IFUPDATE(column[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask{comparison_operator}column_bitmask[...n}sql_statement[...n}}AFTERSQL語句中指定的所有操作都已成功執行后才激UPDATE指明是UPDATE觸發器，每UPDATE語句修改OF子句指定的列值DBMS都將激發觸發器。WITHAPPEND指定應該添加現有類型的其它觸發器。WITHAPPEND不能與NOTFORREPLICATION表示當進程更改觸發器所涉及的表時，不應執行該sql_statement是觸發器的條件和操作。觸發器條件指定其它準則，以確定DELETE、INSERT或UPDATE語句是否導致執行觸發器操作。于DELETE操作。可以指定多列。【任務分析】任務8-14涉及到CREATETRIGGER的應用，其參數reae=efriae=osql_statement=PRINT‘操作已執行’。。USEUSEStudentCREATETRIGGERStudent_info_triONStudent_infoAFTERINSERT,UPDATE,DELETE只能在當前的數據庫中創建，不過觸發器可以當前數據庫的外部對象。而CREATETRIGGER語句中，可以為多種用戶操作(INSERT和觸發器的inserteddeleted每個觸發器有兩個特殊的插入表（inserted表）和刪除表（deleted表，這兩個表是邏輯表，由系統管理，在內存中，因此不允許用戶直接對其進行修改，當觸發器工作完inserted表和deleted表只能被創建它們的觸發器即它們的范圍僅限于該觸發器，6-1說明了在使用何種觸發器時，觸發6-1觸發器創建的INSERT是否DELETE觸發否是是是inserted表，該表映射了與該觸發器相連接的表的列的結構。例如當用戶在A表中插入一行時，A表的觸發器使用A表的insertedAinserted表中也包含該行。刪除的每一行都包含在刪除觸發器內的deleted表中。inserted表和deleted表，這兩個表和它們相連接的表有相同的列結構。inserteddeleted表分別包含相連接表中數據的前后映射。例如，如果Ainserteddeleted表中所改變的每一行數據都在inserted表中行的數據值是執行更新語句之后的A表中行的數據值。Inserted表中的新行，確定是否應該執行以及如何執行該插入【任務8-15】創建觸發器Student_info_ins，該觸發器能夠保證在學生信息表中添加新記USEUSEStudentCREATETRIGGERStudent_info_insONstudent_infoAFTERINSERTIFEXISTS(SELECT*FROMINSERTEDWHEREClass_idIN(SELECTClass_idROLLBACKTRANSACTIONINSERT ,20,'女運行后的結果如圖8-24所示。該圖中的字符串“數據插入”是進行INSERT操作時觸發了Student_info_tri觸發器產生的字符串數據已入”是進行INSERT操作時觸發了student_info_ins觸發器產生的字符串。8-24使用INSERT語句添加記據并且刪除一條舊的數據。UPDATEDeleted表中，將一個新行插入InsertedDeletedInserted表和被修改的表來【任務8-16】創建觸Student_info_upd，只有當對Student_info表中的數據執行更【任務分析】使用CREATETRIGGER語句創建此過程，其參數信息如下：USEUSEStudentCREATETRIGGERStudent_info_updONStudent_infoAFTERUPDATEUpdateStudent_infoset ’where8-25創建和使用IPDATE觸發到Deleted表中。并檢查Deleted表中的行，以確定是否需要執行觸發器操作或應如何執行觸據變被在Deleted表中。【任務8-17】創建觸發器Student_info_del，當學生信息表中刪除某一條記錄時，留言【任務分析】任務817涉及到CREATETRIGGER語句的應用，=Student_info_del，table=Student_info，觸發事件為DELETE，執