第8章存儲過程和觸發器存儲過程概述8.1存儲過程的創建8.2執行存儲過程8.3存儲過程的參數8.4存儲過程的返回值8.5存儲過程的查看、修改和刪除8.6觸發器的概念8.7

觸發器的創建8.8觸發器的使用8.9

觸發器的修改及刪除8.108.1存儲過程概述8.1.1存儲過程的基本概念存儲過程是獨立存在于表之外的數據庫對象，由被編譯在一起的一組Transact-SQL語句組成。它可以被客戶調用，也可以被另一個存儲過程或觸發器調用，它的參數可以被傳遞，它的出錯代碼也可以被檢驗。

在SQLServer中，使用存儲過程的優點如下：①存儲過程在服務器端運行，執行速度快。②存儲過程執行一次后，其執行規劃就駐留在高速緩沖存儲器，在以后的操作中，只需從高速緩沖存儲器中調用已編譯好的二進制代碼執行，提高了系統性能。③確保數據庫的安全。使用存儲過程可以完成所有的數據庫操作，并可通過編程方式控制上述操作對數據庫信息訪問的權限。④自動完成需要預先執行的任務。存儲過程可以在系統啟動時自動執行，而不必在系統啟動后再進行手工操作，大大方便了用戶的使用，可以自動完成一些需要預先執行的任務。8.1.2存儲過程的類型SQLServer支持五種類型的存儲過程：系統存儲過程、本地存儲過程、臨時存儲過程、遠程存儲過程和擴展存儲過程。在不同情況下需要執行不同的存儲過程。1．系統存儲過程

系統存儲過程是由系統提供的存儲過程，可以作為命令執行各種操作。系統存儲過程定義在系統數據庫master中，其前綴是sp_。2．本地存儲過程

本地存儲過程是指在用戶數據庫中創建的存儲過程，這種存儲過程完成特定數據庫操作任務，其名稱不能以sp_為前綴。3．臨時存儲過程

臨時存儲過程屬于本地存儲過程。如果本地存儲過程的名稱前面有一個“#”，該存儲過程就稱為局部臨時存儲過程，這種存儲過程只能在一個用戶會話中使用；如果本地存儲過程的名稱前有兩個“##”，該過程就是全局臨時存儲過程，這種存儲過程可以在所有用戶會話中使用。4．遠程存儲過程

遠程存儲過程指從遠程服務器上調用的存儲過程。5．擴展存儲過程

在SQLServer環境之外執行的動態鏈接庫稱為擴展存儲過程，其前綴是sp_。使用時需要先加載到SQLServer系統中，并且按照使用存儲過程的方法執行。8.2存儲過程的創建

默認情況下，用戶創建的存儲過程歸數據庫所有者擁有，數據庫的所有者可以把許可授權給其他用戶。

存儲過程由CREATEPROCEDURE語句創建，存儲過程的定義包括：過程名和參數的說明以及過程體，即包含執行存儲過程操作的Transact-SQL語句。要使用存儲過程，首先要創建一個存儲過程。8.2.1使用CREATEPROCEDURE語句創建1．語法格式CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]2．參數說明procedure_name：新存儲過程的名稱。過程名必須符合標識符規則，且對于數據庫及其所有者必須惟一。如果要創建局部臨時過程，可以在procedure_name前面加一個編號符#，要創建全局臨時過程，可以在procedure_name前面加兩個編號符##。完整的名稱包括（#或##）不能超過128個字符。指定過程所有者的名稱是可選的。number：是可選的整數，用來對同名的過程分組，以便用一條[DROPPROCEDURE]語句即可將同組的過程一起除去。@parameter：過程中的參數。在[CREATEPROCEDURE]語句中可以聲明一個或多個參數。用戶必須在執行過程時提供每個所聲明參數的值（除非定義了該參數的默認值）。存儲過程最多可以有2100個參數。使用@符號作為第一個字符來指定參數名稱。參數名稱必須符合標識符的規則。每個過程的參數僅用于該過程本身；相同的參數名稱可以用在其他過程中。默認情況下，參數只能代替常量，而不能用于代替表名、列名或其他數據庫對象的名稱。data_type：參數的數據類型。所有數據類型（包括text、ntext和image）均可以用作存儲過程的參數。不過，cursor數據類型只能用于[OUTPUT]語句的參數。如果指定的數據類型為cursor，也必須同時指定[VARYING]和[OUTPUT]關鍵字。說明：對于可以是cursor數據類型的輸出參數，沒有最大數目的限制。VARYING：指定作為輸出參數支持的結果集（由存儲過程動態構造，內容可以變化）。僅適用于游標參數。default：參數的默認值。如果定義了默認值，不必指定該參數的值即可執行過程。默認值必須是常量或[NULL]。如果過程將對該參數使用[LIKE]關鍵字，那么默認值中可以包含通配符（％、_、[]和^）。OUTPUT：表明參數是返回參數。該選項的值可以返回給[EXEC[UTE]。使用[OUTPUT]參數可將信息返回給調用過程。text、ntext和image類型數據可用作[OUTPUT]參數。使用[OUTPUT]關鍵字的輸出參數可以走游標占位符。n：表示最多可以指定2100個參數的占位符。RECOMPILE|ENCRYPTION|（RECOMPILE，ENCRYPTION）：RECOMPILE表明SQLServer不會緩存該過程的計劃，該過程將在運行時重新編譯。在使用非典型值或臨時值而不希望覆蓋緩存在內存中的執行計劃時，應該使用[RECOMPILE]選項。ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE語句文本的條目。使用ENCRYPTION可防止將過程作為SQLServer復制的一部分發布。說明：在升級過程中，SQLServer利用存儲在syscomments中的加密注釋來重新創建加密過程。FORREPLICATION：指定不能在訂閱服務器上執行為復制創建的存儲過程。使用[FORREPLICATION]選項創建的存儲過程可用作存儲過程篩選，且只能在復制過程中執行。該選項不能和[WITHRECOMPILE]選項一起使用。AS：指定過程要執行的操作。sql_statement：過程中要包含的任意數目和類型的Transact-SQL語句，但有一些限制。ASsql_statement[…n]：其中的[n]是表示此過程可以包含多條Transact-SQL語句的占位符。3．注意事項①用戶定義的存儲過程只能在當前數據庫中創建（臨時過程除外，臨時過程總是在tempdb中創建）。②成功執行CREATEPROCEDURE語句后，過程名稱存儲在sysobjects系統表中，而CREATEPROCEDURE語句的文本存儲在syscomments中。③自動執行存儲過程。SQLServer啟動時可以自動執行一個或多個存儲過程。這些存儲過程必須由系統管理員在master數據庫中創建，并在sysadmin固定服務器角色下作為后臺過程執行。這些過程不能有任何輸入參數。④sql_statement的限制。除了SETSHOWPLANTEXT和SETSHOWPLANALL外，其他SET語句均可在存儲過程內使用。

必須使用對象所有者名對數據庫對象進行限定的語句有：CREATETABLE、ALTERTABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS及DBCC語句。⑤權限。CREATEPROCEDURE的權限默認授予sysadmin固定服務器角色成員、db_owner和db_ddladmin固定數據庫角色成員。sysadmin固定服務器角色成員和dlowner固定數據庫角色成員可以將CREATEPROCEDURE權限轉讓給其他用戶。USEStudent--檢查是否已存在同名的存儲過程，若有，則刪除。IFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_info_pro'ANDtype='P')DROPPROCEDUREstu_info_proGO--創建存儲過程CREATEPROCEDUREstu_info_proASSELECTstudent_Name,student_Sex,addressFROMStudent_InfoGOEXECstu_info_pro8.2.2使用企業管理器創建（1）打開企業管理器，展開服務器組，并展開相應的服務器。（2）打開“數據庫”文件夾，并打開要創建存儲過程的數據庫。（3）選擇“存儲過程”選項，右擊鼠標，執行“新建存儲過程”命令，打開創建存儲過程對話框如圖8-2所示。圖8-2創建存儲過程對話框（4）在“文本”列表框中顯示了CREATEPROCEDURE語句的框架，可以修改要創建的存儲過程的名稱，然后加入存儲過程所包含的SQL語句。（5）單擊“檢查語法”按鈕，可以檢查創建存儲過程的SQL語句的語法是否正確。（6）如果要將其設置為下次創建存儲過程的模板，可單擊“另存為模板”按鈕。（7）完成后，單擊“確定”按鈕即可創建一個存儲過程。8.2.3使用向導創建

（1）在企業管理器中，執行“工具”下拉菜單中的“向導”命令，打開“選擇向導”對話框。（2）在“數據庫”文件夾選擇“創建存儲過程”向導，單擊“確定”按鈕，出現“創建存儲過程向導”歡迎對話框。（3）單擊“下一步”按鈕，出現選擇數據庫對話框，如圖8-3所示。（4）在“數據庫名稱”下拉列表中選擇數據庫后，單擊“下一步”按鈕，出現選擇存儲過程對話框，如圖8-4所示。圖8-3選擇數據庫對話框圖8-4選擇存儲過程對話框（5）單擊“下一步”按鈕，出現完成創建存儲過程對話框。若單擊“完成”按鈕，即可完成存儲過程的創建。（6）單擊“編輯”按鈕，可編輯存儲過程，如圖8-6所示。圖8-5完成創建存儲過程對話框圖8-6編輯存儲過程對話框（7）單擊“編輯SQL”按鈕，即可打開“編輯存儲過程SQL”對話框，其中的列表框顯示了創建該存儲過程的Transact-SQL語句。可以在已有的Transact-SQL語句的基礎上進行編輯，也可以單擊“分析”按鈕來執行語法檢查。如圖8-7所示。圖8-7“編輯存儲過程SQL”對話框（8）單擊“確定”按鈕，返回到圖8-5所示的對話框，再單擊“確定”按鈕即可。8.3執行存儲過程1．語法格式[[EXEC[UTE]]{[@return_status=]{procedure_name[;number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}[,…n][WITHRECOMPILE]2．參數說明@return_status：一個可選的整型變量，保存存儲過程的返回狀態。此變量在用于EXECUTE語句前，必須在批處理、存儲過程或函數中已聲明。procedure_name：調用的存儲過程名稱。number：可選的整數，用于將相同名稱的過程進行組合，使得它們可以用一句DROPPROCEDURE語句除去。該參數不能用于擴展存儲過程。在同一應用程序中使用的過程一般都以該方式組合。@procedure_name_var：局部定義變量名，代表存儲過程名稱。@parameter：過程參數，在[CREATEPROCEDURE]語句中定義。參數名稱前必須加上符號@。在以“@parameter_name=value”格式使用時，參數名稱和常量不一定按照[CREATEPROCEDURE]語句中定義的順序出現。但是，如果有一個參數使用“@parameter_name=value”格式，則其他所有參數都必須使用這種格式。

默認情況下，參數可為空。如果傳遞NULL參數值，且該參數用于CREATE或ALTERTABLE語句中不允許為NULL的列（例如，插入至不允許為NULL的列），SQLServer就會報錯。為避免將NULL參數值傳遞給不允許為NULL的列，可以在過程中添加程序設計邏輯或采用默認值（使用CREATE或ALTERTABLE語句中的DEFAULT關鍵字）。value：過程中參數的值。如果參數名稱沒有指定，參數值必須以CREATEPROCEDURE語句中定義的順序給出。@variable：用來保存參數或者返回參數的變量。OUTPUT：指定存儲過程必須返回一個參數。該存儲過程的匹配參數也必須由關鍵字OUTPUT創建。使用游標變量作參數時使用該關鍵字。

DEFAULT：根據過程的定義提供參數的默認值。當過程需要的參數值沒有事先定義好默認值或缺少參數或指定了[DEFAULT]關鍵字，就會出錯。n：占位符，表示在它前面的項目可以多次重復執行。例如，EXCUTE語句可以指定一個或者多個@parameter、value或@variable。WITHRECOMPILE：強制編譯新的計劃。如果所提供的參數為非典型參數或者數據有很大的改變，使用該選項，在以后的程序執行中使用更改過的計劃。該選項不能用于擴展存儲過程。建議盡量少使用該選項，因為它消耗較多的系統資源。3．注意事項①如果存儲過程名的前三個字符為sp_，SQLServer會在Master數據庫中尋找該過程。如果沒能找到合法的過程名，SQLServer會尋找所有者名稱為dbo的過程。②參數可以通過value或@parametername：value提供。③執行存儲過程時，若語句是批處理中的第一個語句，則不一定要指定EXECUTE關鍵字。8.4存儲過程的參數8.4.1參數傳遞方式1．順序法在傳遞參數時，使傳遞的參數和定義時的參數順序一致，對于使用默認值的參數可以用DEFAULT值代替。USEStudentGoCREATEPROCEDUREAdd_class(@class_idint,@class_namechar(20),@directorchar(10),@professionchar(14))ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計算機應用022','張波','計算機應用'select*fromclass_infoUSEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='stu_score_pro'ANDtype='P')DROPPROCEDUREstu_score_proGoCREATEPROCEDUREstu_score_pro@snamechar(10),@cnamechar(10)ASSELECTa.student_name,c.course_name,c.course_start,b.result,c.course_scoreFROMstudent_infoaJOINresult_infobONa.student_id=b.student_idJOINcourse_infocONb.course_no=c.course_noWHEREc.course_name=@cnameanda.student_name=@snameEXECstu_score_pro'陳白露','馬克思主義'2．提示法

在傳遞參數時，采用提示的方法，如“@class_id=200202”的形式，此時，各個參數的順序可以任意排列。EXECAdd_class@class_id=200202,@class_name='計算機應用022',@director='張波',@profession='計算機應用'EXECAdd_class@class_name='計算機應用022',@class_id=200202,@profession='計算機應用',@director='張波'8.4.2使用默認參數

創建存儲過程時，可以為參數提供一個默認值，默認值必須為常量或者NULL。USEStudentGoCREATEPROCEDUREAdd_class@class_idint,@class_namechar(20),@directorchar(10)='無',@professionchar(14)='無'ASINSERTINTOclass_infoVALUES(@class_id,@class_name,@director,@profession)EXECAdd_class200202,'計算機應用022'Goselect*fromclass_infoGo8.4.3使用帶OUTPUT的返回參數

在創建存儲過程時，可以定義返回參數。在執行存儲過程時，可以將結果返回給返回參數。USEStudentGoCREATEPROCEDUREQuery_student(@student_idint,@student_namechar(10)OUTPUT,@addressvarchar(50)OUTPUT)ASSELECT@student_name=student_name,@address=addressFROMstudent_infoWHEREstudent_id=@student_idDECLARE@student_namechar(10)DECLARE@addressvarchar(50)EXECQuery_student20000203,@student_nameOUTPUT,@addressOUTPUTSELECT'姓名'=@student_name,'家庭住址'=@addressGO8.5存儲過程的返回值

存儲過程在執行后都會返回一個整型值。如果執行成功，則返回0；否則返回?1～99之間的數值。8.5.1RETURN語句1．語法RETURN[整數表達式]2．功能從查詢或過程中無條件退出。[RETURN]語句可在任何時候從過程、批處理或語句塊中退出，不執行位于[RETURN]之后的語句。3．說明①[整數表達式]：返回的整型值。存儲過程可以給調用過程或應用程序返回整型值。②在建立存儲過程的時候，需要定義任意的出錯條件，并把它們與整型的出錯代碼聯系起來。③用于存儲過程時，[RETURN]不能返回空值。如果過程試圖返回空值，系統將生成警告信息并返回0值。USEStudentGoCREATEPROCEDUREtest_return(@input_numint=0)ASIF@input_num=0RETURN0IF@input_num>0RETURN1IF@input_num<0RETURN-18.5.2捕獲存儲過程的返回值

若要正確接收存儲過程的返回值，可使用EXECUTE語句。1．語法EXECUTE@return_status=procedure_name2．功能

執行存儲過程，將RETURN語句返回的值送狀態變量@return_status。3．說明

在執行EXECUTE語句之前，首先要聲名變量@return_status。DECLARE@ret_numintEXEC@ret_num=test_return100SELECT'返回值'=@ret_numGO8.6存儲過程的查看、修改和刪除8.6.1存儲過程的查看1．使用sp_helptext查看存儲過程EXECsp_helptexttest_return2．使用企業管理器（1）打開企業管理器，展開服務器組，并展開相應的服務器；（2）打開數據庫文件夾，然后選擇存儲過程所在的數據庫“student”；（3）打開“存儲過程”文件夾，在右側詳細信息窗格中右擊存儲過程add_class，執行“屬性”命令，打開“存儲過程屬性”對話框，如圖8-13所示；圖8-13存儲過程屬性對話框（4）可以在此對話框中，直接修改存儲過程的定義，也可以設置存儲過程的權限。完成后，單擊“確定”按鈕即可。8.6.2存儲過程的修改

修改存儲過程使用ALTERPROCEDURE語句。1．功能更改先前通過執行CREATEPROCEDURE語句創建的存儲過程，但不會更改權限，也不影響相關的存儲過程或觸發器。2．語法ALTERPROC[EDURE]procedure_name[；number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[…n]3．說明

各參數含義與CREATEPROCEDURE命令相同。如果原來的過程定義是用WITHENCRYPTION或WITHRECOMPILE創建的，那么只有在ALTERPROCEDURE中也包含這些選項時，這些選項才有效。ALTERPROCEDURE權限默認授予sysadmin固定服務器角色成員、db_owner和db_ddladmin固定數據庫角色成員以及過程的所有者且不可轉讓。用ALTERPROCEDURE更改后，過程的權限和啟動屬性保持不變。8.6.3存儲過程的刪除1．通過企業管理器刪除在要刪除的存儲過程中右擊鼠標，然后執行“刪除”命令，在彈出的對話框中單擊“全部除去”按鈕即可。2．通過DROPPROCEDURE語句來完成語法格式：

DROPPROCEDURE{procedure}[，…n]功能：從當前數據庫中刪除一個或多個存儲過程或存儲過程組。參數含義：①procedure：指要刪除的存儲過程或存儲過程組的名稱；②n：表示可以指定多個存儲過程同時刪除。③默認情況下，將DROPPROCEDURE權限授予過程所有者，該權限不可轉讓。db_owner和db_ddladmin固定數據庫角色成員和sysadmin固定服務器角色成員可以通過在DROPPROCEDURE內指定所有者刪除任何對象。

DROPPROCEDUREtest_return8.7觸發器的概念

觸發器是一種特殊類型的存儲過程，它不能顯示地被調用，它是在指定的表中插入記錄、更改記錄或者刪除記錄時，被自動激活。所以，觸發器可以用來對表實施復雜的完整性約束，當觸發器所保護的數據發生改變時，觸發器會自動被激活，從而防止對數據的不正確的修改。

在觸發器中可以查詢其他表，也可以查詢其他更復雜的T-SQL語句。觸發器和引起觸發器執行的T-SQL語句被當作一次事務處理，因此可以在觸發器中回滾這個事務。

如果發現引起觸發器執行的T-SQL語句執行了一個非法操作，則可以通過回滾事務使語句不能執行，回滾后SQLServer會自動返回到此事務執行前的狀態。8.8觸發器的創建8.8.1使用企業管理器創建觸發器（1）打開企業管理器，展開服務器組，并展開相應的服務器。（2）打開“數據庫”文件夾，選擇要創建觸發器的數據庫。（3）選擇“表”文件夾，然后在要創建觸發器的表上右擊鼠標，執行“所有任務”子菜單下的“管理觸發器”命令，打開“觸發器屬性”對話框。在此對話框中的“文本”列表框中，輸入用于創建觸發器的Transact-SQL語句。單擊“檢查語法”按鈕可以檢查SQL語句的語法是否正確。注意：如果在“名稱”文本框中選擇已經創建的觸發器，則單擊下面的“刪除”按鈕即可刪除該觸發器。（4）輸入完成后，單擊“確定”按鈕，即可創建觸發器。8.8.2使用Transact-SQL語句創建觸發器1．語法格式CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][，][INSERT][，][UPDATE]}[NOTFORREPLICATION]AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][…n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[…n]}]sql_statement[…n]}}2．參數說明參數trigger_name用于指定觸發器名。觸發器名必須符合標識符規則，并且在數據庫中必須惟一，可以包含觸發器所有者名。

tablelview是觸發器表或觸發器視圖，即在其上執行觸發器的表或視圖。有時，可以包含表或視圖的所有者名。關鍵字WITHENCRYPTION可防止將觸發器作為SQLServer復制的一部分發布。

AFTER關鍵字用于說明觸發器在指定操作都成功執行后觸發，AFTER是默認設置，不能在視圖上定義AFTER觸發器。INSTEADOF指定用觸發器中的操作代替觸發語句的操作，在表或視圖上，每個INSERT、UPDATE或DELETE語句最多可以定義一個INSTEADOF觸發器。如果觸發器表存在約束，則在INSTEADOF觸發器執行之后和AFTER觸發器執行之前檢查這些約束。如果違反了約束，則回滾INSTEADOF觸發器操作且不執行AFTER觸發器，INSTEADOF觸發器不能在WITHCHECKOPTION可更新視圖上定義。關鍵字DELETE、INSERT和UPDATE用于指定在表或視圖上執行這一操作時將激活相應的觸發器，必須指定一項或多項，項與項之間用逗號分隔。關鍵字選項NOTFORREPLICATION指該觸發器對于復制進程無效。IFUPDATE(column)子句用于測試在指定的列上進行的INSERT或UPDATE操作，不能用于DELETE操作；UPDATE(column)中的column為表或者視圖中的列名稱，說明這一列的數據是否被INSERT或者UPDATE操作修改過。如果修改過，則返回TRUE，否則返回FALSE。IF(COLUMNS_UPDATED())子句用于測試是否插入或更新了指定的列。返回的二進制位數據，表示插入或更新了表中的哪些列，若對應位為0，表示沒有插入或更新；若對應位為1，表示對該列進行了插入或更新。關于表文件的列與二進制位的對應關系為：如果表的列從左向右分別為C0，C1，C2，C3，C4…則分別對應二進制位的第0位、第1位、第2位、第3位、第4位……依此類推。

如果在表上創建的觸發器包含8列以上，則COLUMNS_UPDATED()返回多個字節。在INSERT操作中，COLUMNS_UPDATED將對所有列返回TRUE值，IF(COLUMNS_UPDATED())僅用于INSERT或UPDATE觸發器。bitwise_opemtor為用于比較運算的位運算符。updated_bitmask為整型的位屏蔽碼，假定該表上有UPDATE觸發器，若要檢查列C1、C2、C4是否都有更新，可指定updated_bitmask的值為00010110（即22）；若要檢查是否只有列C1有更新，可指定updated_bitmask的值為00000010（即2）。comparison_operator為比較運算符。使用等號（=）檢查updated_bitmask中指定的所有列是否都實際進行了更新。使用大于號（>）檢查update_bitmask中指定的任一列或某些列是否已更新。

column_bitmask為列屏蔽碼，用來檢查是否已更新或插入了對應列。參數sql_statement為觸發器的T-SQL語句，當執行DELETE、INSERT或UPDATE操作時，對應的觸發器操作將生效。

n表示觸發器中可以包含多條T-SQL語句。USEStudentGO/*如果表B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='B1')DROPTABLEB1GOCREATETABLEB1(student_numberint,student_namechar(30))GO/*如果觸發器Query1_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query1_B1'ANDtype='TR')DROPTRIGGERQuery1_B1GO/*創建觸發器Query1_B1*/CREATETRIGGERQuery1_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMB1GOINSERTB1VALUES(200401,'張山')8.9觸發器的使用8.9.1inserted表和deleted表

在觸發器執行的時候，會產生兩個臨時表：inserted表和deleted表。它們的結構和觸發器所在的表的結構相同，SQLServer2000自動創建和管理這些表。可以使用這兩個臨時的駐留內存的表測試某些數據修改的效果及設置觸發器操作的條件；然而，不能直接對表中的數據進行更改。inserted表用于存儲INSERT和UPDATE語句所影響的行的副本。在一個插入或更新事務處理中，新建行被同時添加到inserted表和觸發器表中。inserted表中的行是觸發器表和新行的副本。deleted表用于存儲DELETE和UPDATE語句所影響的行的復本。在執行DELETE或UPDATE語句時，行從觸發器表中刪除，并傳輸到deleted表中。delete表和觸發器表通常沒有相同的行。執行INSERT操作：插入到觸發器表中的新行被插入到inserted表中。執行DELETE操作：從觸發器表中刪除的行被插入到deleted表中。執行UPDATE操作：先從觸發器表中刪除舊行，然后再插入新行。其中被刪除的舊行被插入到deleted表中，插入的新行被插入到inserted表中。/*如果觸發器Query2_B1存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Query2_B1'ANDtype='TR')DROPTRIGGERQuery2_B1GO/*創建觸發器Query2_B1*/CREATETRIGGERQuery2_B1ONB1FORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeletedGOUPDATEB1SETstudent_name='張峰'WHEREstudent_number=2004018.9.2INSERT觸發器和UPDATE觸發器

當向表中插入或者更新記錄時，INSERT或者UPDATE觸發器被執行。一般情況下，這兩種觸發器常用來檢查插入或者修改后的數據是否滿足要求。USEStudentIFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_insert'ANDtype='TR')DROPTRIGGERcheck_insertGOCREATETRIGGERcheck_insertONresult_infoFORINSERTASIFEXISTS(SELECT*FROMinsertedaWHEREa.student_idNOTIN(SELECTb.student_idFROMstudent_infob)ORa.course_noNOTIN(SELECTc.course_noFROMcourse_infoc))BEGINRAISERROR('違背數據的完整性',16,1)ROLLBACKTRANSACTION/*回滾事務*/ENDinsertresult_infoVALUES('020',20040101,'31',20)USEStudentGO/*檢查是否存在score表，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='score')DROPTABLEscoreGO/*創建score表*/CREATETABLEscore(student_noint,scoreint)/*檢查是否存在check_score觸發器，若存在，則刪除*/IFEXISTS(SELECTnameFROMsysobjectsWHEREname='check_score'ANDtype='TR')DROPTRIGGERche