第10章 存儲過程與觸發器

第10章存儲過程與觸發器

《數據庫技術與應用-SQLServer2008》10.1存儲過程

概述10.2存儲過程

的創建與

使用10.3觸發器概

述10.4觸發器的創建與使

用10.5事務處理10.6鎖機制.10.1存儲過程概述 1.存儲過程存儲過程是一組Transact-SQL語句的集合，經編譯后存放在數據庫服務器端，供客戶端調用，因此存儲過程可以充分地利用服務器的高性能運算能力，而無需把大量的結果集傳送到客戶端處理，從而可大大減少網絡數據傳輸開銷，提高應用程序訪問數據庫的速度和效率。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 2.觸發器觸發器實質上是一種特殊類型的存儲過程，它在插入、修改或刪除指定表中的數據時觸發執行。使用觸發器可提高數據庫應用程序和靈活性和健壯性，實現復雜的業務規則，更有效地實施數據完整性。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 1.存儲過程的類型SQLServer存儲過程的類型包括：系統存儲過程、用戶定義存儲過程、臨時存儲過程、擴展存儲過程。（1）系統存儲過程系統存儲過程是指由系統提供的存儲過程，主要存儲在master數據庫中并以sp_為前綴，它從系統表中獲取信息，從而為系統管理員管理SQLServer提供支持。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 1.存儲過程的類型（2）用戶定義存儲過程用戶定義存儲過程是由用戶創建并能完成某一特定功能（例如查詢用戶所需數據信息）的存儲過程。它處于用戶創建的數據庫中，存儲過程名前沒有前綴sp_。本章所涉及的存儲過程主要是指用戶定義存儲過程。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 1.存儲過程的類型（3）臨時存儲過程臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加“#”或“##”前綴表示?！?”表示本地臨時存儲過程，“##”表示全局臨時存儲過程。使用臨時存儲過程必須創建本地連接，當SQLServer關閉后，這些臨時存儲過程將自動被刪除。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 1.存儲過程的類型（4）擴展存儲過程擴展存儲過程是SQLServer2008的實例可以動態加載和運行的動態鏈接庫（DLL）。它直接在SQLServer2008實例的地址空間中運行，可以使用SQLServer擴展存儲過程API完成編程。當擴展存儲過程加載到SQLServer中，它的使用方法與系統存儲過程一樣。擴展存儲過程只能添加到master數據庫中，其前綴是xp_。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 2.存儲過程的功能特點SQLServer中的存儲過程可以實現以下功能：（1）接收輸入參數并以輸出參數的形式為調用過程或批處理返回多個值。（2）包含執行數據庫操作的編程語句，包括調用其他過程。（3）為調用過程或批處理返回一個狀態值，以表示成功或失?。笆≡颍?。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.1存儲過程概述 2.存儲過程的功能特點存儲過程具有以下優點：（1）模塊化編程。創建一次存儲過程，存儲在數據庫中后，就可以在程序中重復調用任意多次。存儲過程可以由專業人員創建，可以獨立于程序源代碼來修改它們。（2）快速執行。當某操作要求大量的Transact-SQL代碼或者要重復執行時，存儲過程要比Transact-SQL批處理代碼快得多。當創建存儲過程時，它得到了分析和優化。在第一次執行之后，存儲過程就駐留在內存中，省去了重新分析、重新優化和重新編譯等工作。（3）減少網絡通信量。存儲過程可以由幾百條Transact-SQL語句組成，但執行時，僅用一條語句，所以只有少量的SQL語句在網絡線上傳輸。從而減少了網絡流量和網絡傳輸時間。（4）提供安全機制。對沒有權限執行存儲體（組成存儲過程的語句）的用戶也可以授權執行該存儲過程。（5）保證操作一致性。由于存儲過程是一段封裝的查詢，從而對于重復的操作將保持功能的一致性。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2存儲過程的創建與使用 （1）打開SQLServer管理平臺，展開節點“對象資源管理器”→“數據庫服務器”→“可編程性”→“存儲過程”，在窗口的右側顯示出當前數據庫的所有存儲過程。單擊鼠標右鍵，在彈出的快捷菜單中選擇“新建存儲過程”命令，如圖10-1所示。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制在SQLServer2008中，可以使用SQLServer管理平臺和Transact-SQL語句CREATEPROCEDURE創建存儲過程，創建存儲過程后，還可以進行存儲過程的執行、修改和刪除等操作。10.2.1創建存儲過程1.使用SQLServer2008管理平臺創建存儲過程10.2存儲過程的創建與使用 1.使用SQLServer2008管理平臺創建存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制（2）在打開的SQL命令窗口中，系統給出了創建存儲過程命令的模板，如圖10-2所示。在模板中可以輸入創建存儲過程的Transact-SQL語句后，單擊“執行”按鈕即可創建存儲過程。（3）建立存儲過程的命令被成功執行后，在“對象資源管理器”→“數據庫服務器”→“可編程性”→“存儲過程”中可以看到新建立的存儲過程，如圖10-3所示。10.2存儲過程的創建與使用 1.使用SQLServer2008管理平臺創建存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2存儲過程的創建與使用 2.使用CREATEPROCEDURE語句創建存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制在創建存儲過程之前，應該考慮以下幾個方面：（1）在一個批處理中，CREATEPROCEDURE語句不能與其他SQL語句合并在一起。（2）數據庫所有者具有默認的創建存儲過程的權限，它可把該權限傳遞給其他的用戶。（3）存儲過程作為數據庫對象其命名必須符合標識符的命名規則。（4）只能在當前數據庫中創建屬于當前數據庫的存儲過程。10.2存儲過程的創建與使用 2.使用CREATEPROCEDURE語句創建存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制創建存儲過程語句的語法格式如下：CREATEPROC[EDURE]procedure_name[；number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[,…n]10.2存儲過程的創建與使用 2.使用CREATEPROCEDURE語句創建存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制【例10-1】創建存儲過程，從表goods和表goods_classification的聯接中返回商品名、商品類別、單價。CREATEPROCEDUREgoods_infoASSELECTgoods_name,classification_name,unit_priceFROMgoodsgINNERJOINgoods_classificationgcONg.classification_id=gc.classification_id存儲過程創建后，存儲過程的名稱存放在sysobject表中，文本存放在syscomments表中。10.2存儲過程的創建與使用 要運行某個存儲過程，只要簡單地通過名字就可以引用它。如果對存儲過程的調用不是批處理中的第一條語句，則需要使用EXECUTE關鍵字。面是執行存儲過程的語法格式：[[EXEC[UTE]]{[@return_status=]procedure_name[；number]|@procedure_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][，…n][WITHRECOMPILE]10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.2執行存儲過程10.2存儲過程的創建與使用 例如，執行例10-1的存儲過程goods_info。在SQL查詢編輯器中輸入命令:EXECgoods_info運行的結果如圖10-4所示。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.2執行存儲過程10.2存儲過程的創建與使用 修改存儲過程可以通過SQLServer管理平臺和Transact-SQL語句實現。1．使用SQLServer2008管理平臺修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程（1）打開SQLServer管理平臺，展開節點“對象資源管理器”→“數據庫服務器”→“可編程性”→“存儲過程”，選擇要修改的存儲過程，單擊鼠標右鍵，在彈出的快捷菜單中選擇“修改”命令。（2）此時在右邊的編輯器窗口中出現存儲過程的源代碼（將CREATEPROCEDURE改為了ALTERPROCEDURE），如圖10-5所示可直接進行修改。修改完后單擊工具欄中的“執行”按鈕執行該存儲過程，從而達到目的。10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程使用ALTERPROCEDURE語句。其語法規則如下：ALTERPROC[EDURE]procedure_name[；number][{@parameterdata_type}[VARYING][=default][OUTPUT]][，…n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE，ENCRYPTION}][FORREPLICATION]ASsql_statement[，…n]其中的參數和保留字的含義與CREATEPROCEDURE語句中的含義相似。10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程【例10-2】使用ALTERPROCEDURE語句更改存儲過程。（1）創建存儲過程employee_dep，以獲取經理辦的男員工。CREATEPROCEDUREemployee_depASSELECTemployee_name,sex,address,department_nameFROMemployeeeINNERJOINdepartmentdONe.department_id=d.department_idWHEREsex='男'ANDe.department_id='D003'10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程【例10-2】使用ALTERPROCEDURE語句更改存儲過程。（2）用SELECT語句查詢系統表sysobjects和syscomments，查看employee_dep存儲過程的文本信息的代碼如下：SELECTo.id，c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type='P'AND='employee_dep'

10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程【例10-2】使用ALTERPROCEDURE語句更改存儲過程。（3）使用ALTERPROCEDURE語句對employee_dep過程進行修改，使其能夠顯示出所有男員工，并使employee_dep過程以加密方式存儲在表syscomments中，其代碼如下：ALTERPROCEDUREemployee_depWITHENCRYPTIONASSELECTemployee_name,sex,address,department_nameFROMemployeeeINNERJOINdepartmentdONe.department_id=d.department_idWHEREsex='男'

10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程【例10-2】使用ALTERPROCEDURE語句更改存儲過程。（4）從系統表sysobjects和syscomments提取修改后的存儲過程employee_dep的文本信息可以運行步驟（2）中的代碼，結果如圖10-8所示。10.2存儲過程的創建與使用 2．使用ALTERPROCEDURE語句修改存儲過程10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.3修改存儲過程【例10-2】使用ALTERPROCEDURE語句更改存儲過程。這是由于在ALTERPROCEDURE語句中使用WITHENCRYPTION關鍵字對存儲過程employee_dep的文本進行了加密，其文本信息顯示為NULL。也可以使用系統存儲過程sp_helptext顯示存儲過程的定義（存儲在syscomments系統表內），其命令如下：sp_helptextemployee_dep結果為“對象'employee_dep'的文本已加密”。

10.2存儲過程的創建與使用 存儲過程可以被快速刪除和重建，因為它沒有存儲數據。刪除存儲過程可以使用SQLServer管理平臺和Transact-SQL語句刪除。10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.4刪除存儲過程1.使用SQLServer2008管理平臺刪除存儲過程操作步驟如下：（1）打開SQLServer管理平臺，展開節點“對象資源管理器”→“數據庫服務器”→“數據庫”→選定的數據庫→“可編程性”→“存儲過程”，選擇要刪除的存儲過程，單擊鼠標右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對象”對話框中單擊“確定”按鈕即可刪除存儲過程。10.2存儲過程的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.4刪除存儲過程2.使用DROPPROCEDURE語句刪除存儲過程DROPPROCEDURE語句可將一個或多個存儲過程從當前數據庫中刪除。其語法如下：DROPPROCEDURE{procedure_name}[，…n]例如，刪除例10-2創建的存儲過程employee_dep可使用以下語句：DROPPROCEDUREemployee_depGO刪除某個存儲過程時，將從sysobjects和syscomments系統表中刪除該過程的相關信息。10.2存儲過程的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.5存儲過程參數與狀態值存儲過程和調用者之間通過參數交換數據，可以按輸入的參數執行，也可由參數輸出執行結果。調用者通過存儲過程返回的狀態值對存儲過程進行管理。參數存儲過程的參數在創建過程時聲明。SQLServer支持兩類參數：輸入參數和輸出參數。（1）輸入參數輸入參數允許調用程序為存儲過程傳送數據值。定義存儲過程的輸入參數必須在CREATEPROCEDURE語句中聲明一個或多個變量及類型。（2）輸出參數輸出參數允許存儲過程將數據值或游標變量傳回調用程序。使用輸出參數，在CREATEPROCEDURE和EXECUTE語句中都必須使用OUTPUT關鍵字。10.2存儲過程的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.5存儲過程參數與狀態值2.返回存儲過程的狀態（1）用RETURN語句定義返回值存儲過程可以返回整型狀態值，表示過程是否成功執行，或者過程失敗的原因。如果存儲過程沒有顯式設置返回代碼的值，則SQLServer返回代碼為0，表示成功執行；若返回-1~-99之間的整數，表示沒有成功執行。也可以使用RETURN語句，用大于0或小于-99的整數來定義自己的返回狀態值，以表示不同的執行結果。在建立過程的時候，需要定義出錯條件并把它們與整型的出錯代碼聯系起來。10.2存儲過程的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制返回存儲過程的狀態【例10-5】創建創建存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值15表示用戶沒有提供參數；值-101表示沒有輸入商品類別；值0表示過程運行沒有出錯。/*存儲過程在出錯時設置出錯狀態*/CREATEPROCcl_goods@cl_namevarchar(40)=NULLASIF@cl_name=NULLRETURN15IFNOTEXISTS(SELECT*FROMgoods_classificationWHEREclassification_name=@cl_name)RETURN-101SELECTg.goods_nameFROMgoods_classificationgc,goodsgWHEREgc.classification_id=g.classification_idANDgc.classification_name=@cl_nameRETURN010.2存儲過程的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.2.5存儲過程參數與狀態值2.返回存儲過程的狀態（2）捕獲返回狀態值在執行過程時，要正確接收返回的狀態值，必須使用以下語句；EXECUTE@status_var=procedure_name其中，@status_var變量應在EXECUTE命令之前聲明。它可以接收返回的狀態碼。如此，當存儲過程執行出錯時，調用它的批處理或應用程序將會采取相應的措施。例10-5的存儲過程cl_goods執行時使用以下語句。/*檢查狀態并報告出錯原因*/DECLARE@return_statusintEXEC@return_status=cl_goods'筆記本計算機'IF@return_status=15SELECT'語法錯誤'ELSEIF@return_status=-101SELECT'沒有找到該商品類別'執行時，將對不同的輸入值返回不同的狀態值及處理結果。10.3觸發器概述 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制觸發器是一種特殊類型的存儲過程，它不同于前面介紹的存儲過程。觸發器主要是通過事件進行觸發而被執行的，而存儲過程可以通過過程名字直接調用。當對某一表進行UPDATE、INSERT、DELETE操作時，SQLServer就會自動執行觸發器所定義的SQL語句，從而確保對數據的處理必須符合由這些SQL語句所定義的規則。10.3觸發器概述 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制觸發器的主要作用就是能夠實現由主鍵和外鍵所不能保證的參照完整性和數據的一致性。除此之處，觸發器還有如下功能：（1）強化約束。觸發器能夠實現比CHECK語句更為復雜的約束。（2）跟蹤變化。觸發器可以偵測數據庫內的操作，從而不允許數據庫中不經許可的指定更新和變化。（3）級聯運行。觸發器可以偵測數據庫內的操作，并自動地級聯影響整個數據庫的各項內容。例如，某個表上的觸發器中包含有對另外一個表的數據操作（如刪除、更新、插入），該操作又導致該表的觸發器被觸發。（4）存儲過程的調用。為了響應數據庫更新，觸發器可以調用一個或多個存儲過程，甚至可以通過外部過程的調用而在DBMS本身之外進行操作。10.3觸發器概述 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制觸發器可以擴展SQLServer約束、默認值和規則的完整性檢查邏輯，可以解決高級形式的業務規則、復雜行為限制、實現定制記錄等方面的問題。例如，觸發器能夠找出某表在數據修改前后狀態發生的差異，并根據這種差異執行一定的處理。一個表的多個觸發器能夠對同一種數據操作采取多種不同的處理。但是，只要約束和默認值提供了全部所需的功能，就應使用約束和默認值。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器1．使用SQLServer2008管理平臺創建觸發器（1）打開SQLServer2008管理平臺，展開節點“對象資源管理器”→“Sales”數據庫→“表”→“employee”表，在“觸發器”節點上，單擊鼠標右鍵，在彈出的快捷菜單中選擇“新建觸發器”命令，如圖10-10所示。在SQLServer中，可以使用SQLServer2008管理平臺和Transact-SQL語句CREATETRIGGER定義表的觸發器、引發觸發器的事件以及觸發器執行引發的操作。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器1．使用SQLServer2008管理平臺創建觸發器（2）在打開的SQL命令窗口中，系統給出了創建觸發器的模板，如圖10-11所示。在模板中可以輸入創建觸發器的Transact-SQL語句后，單擊“執行”按鈕即可創建觸發器。（3）建立存儲過程的命令被成功執行后，將該觸發器保存到相關的系統表中。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器2．使用CREATETRIGGER語句創建觸發器使用CREATETRIGGER語句創建觸發器以前必須考慮到以下幾個方面：（1）CREATETRIGGER語句必須是批處理的第一個語句。（2）表的所有者具有創建觸發器的默認權限，且不能把該權限傳給其他用戶。（3）觸發器是數據庫對象，所以其命名必須符合命名規則。（4）不能在視圖或臨時表上創建觸發器，而只能在基表或創建視圖的表上創建觸發器。（5）觸發器只能創建在當前數據庫中，一個觸發器只能對應一個表。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器2．使用CREATETRIGGER語句創建觸發器CREATETRIGGER語句的語法格式如下：CREATETRIGGERtrigger_nameON{table_name|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][，][UPDATE][，][DELETE]}ASsql_statement[，…n]10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器2．使用CREATETRIGGER語句創建觸發器【例10-6】在employee表上創建一個DELETE類型的觸發器，該觸發器的名稱為tr_employee。（1）創建觸發器tr_employee。CREATETRIGGERtr_employeeONemployeeFORDELETEASDECLARE@msgvarchar(50)SELECT@msg=STR(@@ROWCOUNT)+'個員工被刪除'SELECT@msgRETURN（2）執行觸發器tr_employee。觸發器不能通過名字來執行，而是在相應的SQL語句被執行時自動觸發的。例如執行以下DELETE語句：DELETEFROMemployeeWHEREemployee_name='張三'該語句要刪除員工姓名為“張三”記錄，由此激活了表employee的DELETE類型的觸發器tr_employee，系統執行tr_employee觸發器中AS之后的語句，并顯示以下信息：1個員工被刪除10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器3．Deleted表和Inserted表在觸發器的執行過程中，SQLServer2008建立和管理兩個臨時的虛擬表：Deleted表和Inserted表。這兩個表包含了在激發觸發器的操作中插入或刪除的所有記錄。可以用這一特性來測試某些數據修改的效果，以及設置觸發操作的條件。這兩個特殊表可供用戶瀏覽，但是用戶不能直接改變表中的數據。在執行INSERT或UPDATE語句之后所有被添加或被更新的記錄都會存儲在Inserted表中。在執行DELETE或UPDATE語句時，從觸發程序表中被刪除的行會發送到Deleted表。對于更新操作，SQLServer先將要進行修改的記錄存儲到Deleted表中，然后再將修改后的數據復制到Inserted表以及觸發程序表。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.1創建觸發器3．Deleted表和Inserted表激活觸發程序時Deleted表和Inserted表的內容如表10-1所示。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.2修改觸發器通過SQLServer2008管理平臺、系統存儲過程或Transact_SQL語句，可以修改觸發器的名字和正文。1．使用sp_rename系統存儲過程修改觸發器的名字語法格式為：sp_renameoldname，newname其中，oldname為修改前的觸發器名，newname為修改后的觸發器名。系統存儲過程還可以獲得觸發器的定義信息，例如，使用系統存儲過程sp_helptrigger查看觸發器的類型，使用系統存儲過程sp_helptext查看觸發器的文本信息，使用sp_depends查看觸發器的相關性。

10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制2．使用SQLServer2008管理平臺修改觸發器的正文修改觸發器的操作步驟如下：（1）打開SQLServer2008管理平臺，展開節點“對象資源管理器”→“數據庫服務器”→“數據庫”→“Sales”數據庫→“表”→“customer”表→“觸發器”，選擇要刪除的觸發器（如例10-7創建的test_tr觸發器），單擊鼠標右鍵，在彈出的快捷菜單中選擇“修改”命令。（2）此時在右邊的編輯器窗口中出現觸發器的源代碼（將CREATETRIGGER改為了ALTERTRIGGER），如圖10-13所示，可以直接進行修改。修改完后單擊工具欄中的“執行”按鈕執行該觸發器代碼，從而達到目的。

10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制3．使用ALTERTRIGGER語句修改觸發器修改觸發器的語法如下：ALTERTRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][，][INSERT][，][UPDATE]}ASsql_statement[，…n]其中，參數的含義與CREATETRIGGER語句的相同。使用代碼修改觸發器通常在應用程序中進行，包括觸發器將實現的功能及觸發器名稱等內容。

10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制3．使用ALTERTRIGGER語句修改觸發器

例如，將例10-6的觸發器tr_employee修改為INSERT操作后進行。ALTERTRIGGERtr_employeeONemployeeFORINSERTASDECLARE@msgvarchar(50)SELECT@msg=STR(@@ROWCOUNT)+'個員工數據被插入'SELECT@msgRETURN對employee表執行以下插入語句：INSERTemployee(employee_id,employee_name)VALUES('E016','王五')激活INSERT觸發器tr_employee，顯示信息如下：1個員工數據被插入10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.3刪除觸發器用戶在使用觸發器后可以將其刪除，但只有觸發器所有者才有權刪除觸發器。可以通過刪除觸發器或刪除觸發器表來刪除觸發器。刪除表時，也將刪除所有與表關聯的觸發器。刪除觸發器時，將從sysobjects和syscomments系統表中刪除有關觸發器的信息。1．使用SQLServer2008管理平臺刪除觸發器操作步驟如下：（1）打開SQLServer2008管理平臺，展開節點“對象資源管理器”→“數據庫服務器”→“數據庫”→“Sales”數據庫→“表”→“customer”表→“觸發器”，選擇要刪除的觸發器（如例10-7創建的test_tr觸發器），單擊鼠標右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對象”對話框中單擊“確定”按鈕即可刪除觸發器。10.4觸發器的創建與使用 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.4.3刪除觸發器2．使用DROPTRIGGER語句刪除指定觸發器刪除觸發器語句的語法格式如下：DROPTRIGGERtrigger_name[，…n]使用代碼刪除觸發器通常在應用程序中進行，適合于動態刪除臨時創建的觸發器。例如，刪除例10-6的觸發器tr_employee，可以使用以下代碼：DROPTRIGGERtr_employee刪除觸發器所在的表時，SQLServer2008將自動刪除與該表相關的觸發器。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制事務（transaction）是SQLServer2008中的一個邏輯工作單元，該單元將被作為一個整體進行處理。事務保證連續多個操作必須全部執行成功，否則必須立即回復到未執行任何操作的狀態，即執行事務的結果要么全部將數據所要執行的操作完成，要么全部數據都不修改。10.5.1事務概述1．事務的由來在SQLServer中，使用DELETE或UPDATE語句對數據庫進行更新時一次只能操作一個表，這會帶來數據庫的數據不一致的問題。例如，企業取消了倉儲部，需要將“倉儲部”從department表中刪除，而employee表中的部門編號與倉儲部相對應的員工也應刪除。因此，兩個表都需要修改，這種修改只能通過兩條DELETE語句進行。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.1事務概述1．事務的由來假設倉儲部編號為D004，第一條DELETE語句修改department表為：DELETEFROMdepartmentWHEREdepartment_id='D004'第二條DELETE語句修改employee表為：DELETEFROMemployeeWHEREdepartment_id='D004'在執行第一條DELETE語句后，數據庫中的數據已處于不一致的狀態，因為此時已經沒有“倉儲部”了，但employee表中仍然保存著屬于倉儲部的員工記錄。只有執行了第二條DELETE語句后數據才重新處于一致狀態。如果執行完第一條語句后，計算機突然出現故障，無法再繼續執行第二條DELETE語句，則數據庫中的數據將處于永遠不一致的狀態。因此，必須保證這兩條DELETE語句都被執行，或都不執行。這時可以使用數據庫中的事務技術來實現。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.1事務概述2．事務屬性事務是指用戶定義的一個數據庫操作序列，這些操作要么全部執行要么全不執行。由于事務作為一個不可分割的邏輯工作單元，當事務執行遇到錯誤時，將取消事務所做的修改。一個邏輯單元必須具有4個屬性：原子性（atomicity）、一致性（consistency）、隔離性（isolation）、持久性（durability），這些屬性稱為ACID。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.1事務概述3．事務模式SQLServer以3種事務模式管理事務：（1）自動提交事務模式。每條單獨的語句都是一個事務。在此模式下，每條Transact-SQL語句在成功執行完成后，都被自動提交，如果遇到錯誤，則自動回滾該語句。該模式為系統默認的事務管理模式。（2）顯式事務模式。該模式允許用戶定義事務的啟動和結束。事務以BEGINTRANSACTION語句顯式開始，以COMMIT或ROLLBACK語句顯式結束。（3）隱性事務模式。在當前事務完成提交或回滾后，新事務自動啟動。隱性事務不需要使用BEGINTRANSACTION語句標識事務的開始，但需要以COMMIT或ROLLBACK語句來提交或回滾事務。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理SQLServer按事務模式進行事務管理，設置事務啟動和結束的時間，正確處理事務結束之前產生的錯誤。1．啟動和結束事務在應用程序中，通常用BEGINTRANSACTION語句來標識一個事務的開始，用COMMITTRANSACTION語句標識事務結束。啟動事務語句的語法格式如下：BEGINTRAN[SACTION][transaction_name|@tran_name_variable[WITHMARK['description']]]結束事務語句的語法格式如下：COMMIT[TRAN[SACTION][transaction_name|@tran_name_variable]]10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理1．啟動和結束事務【例10-8】建立一個顯式事務以顯示Sales數據庫的employee表的數據。

BEGINTRANSACTIONSELECT*FROMemployeeCOMMITTRANSACTION本例創建的事務以BEGINTRANSACTION語句開始，以COMMITTRANSACTION語句結束。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理1．啟動和結束事務【例10-9】建立建立一個顯式命名事務以刪除department表的“倉儲部”記錄行。DECLARE@transaction_namevarchar(32)SELECT@transaction_name='tran_delete'BEGINTRANSACTION@transaction_nameDELETEFROMdepartmentWHEREdepartment_id='D004'DELETEFROMemployeeWHEREdepartment_id='D004'COMMITTRANSACTIONtran_delete本例命名了一個事務tran_delete，該事務用于刪除department表的“倉儲部”記錄行及相關數據。在BEGINTRANSACTION和COMMITTRANSACTION語句之間的所有語句被作為一個整體，只有執行到COMMITTRANSACTION語句時，事務中對數據庫的更新操作才算確認。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理2．事務回滾當事務執行過程中遇到錯誤時，該事務修改的所有數據都恢復到事務開始時的狀態或某個指定位置，事務占用的資源將被釋放。這個操作過程叫事務回滾。事務回滾使用ROLLBACKTRANSACTION語句實現，其語法格式如下：ROLLBACK[TRAN[SACTION][transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable]]10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理2．事務回滾【例10-11】使用ROLLBACKTRANSACTION語句標識事務結束。BEGINTRANSACTIONUPDATEgoodsSETstock_quantity=stock_quantity-5WHEREgoods_id='G00006'INSERTINTOsell_order(order_id1,goods_id,order_num,order_date)VALUES('S00005','G00006',5,getdate())ROLLBACKTRANSACTION本例建立的事務對goods表和sell_order表進行更新和插入操作。但當服務器遇到ROLLBACKTRANSACTION語句時，就會拋棄事務處理中的所有變化，把數據恢復到開始工作之前的狀態。因此事務結束后，goods表和sell_order表都不會改變。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.5.2事務管理3．事務嵌套和BEGIN…END語句類似，BEGINTRANSACTION和COMMITTRANSACTION語句也可以進行嵌套，即事務可以嵌套執行。10.5事務處理 10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制3．事務嵌套【例10-14】提交事務。CREATETABLEemployee_tran(numchar(2)NOTNULL,cnamechar(6)NOTNULL)GOBEGINTRANSACTIONTran1--@@TRANCOUNT為1INSERTINTOemployee_tranVALUES('01'，'Zhang')BEGINTRANSACTIONTran2--@@TRANCOUNT為2INSERTINTOemployee_tranVALUES('02'，'Wang')BEGINTRANSACTIONTran3--@@TRANCOUNT為3PRINT@@TRANCOUNTINSERTINTOemployee_tranVALUES('03'，'Li')COMMITTRANSACTIONTran3--@@TRANCOUNT為2PRINT@@TRANCOUNTCOMMITTRANSACTIONTran2--@@TRANCOUNT為1PRINT@@TRANCOUNTCOMMITTRANSACTIONTran1--@@TRANCOUNT為0PRINT@@TRANCOUNT運行結果如下：321010.6SQLServer 的鎖機制10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制鎖（lock）作為一種安全機制，用于控制多個用戶的并發操作，以防止用戶讀取正在由其他用戶更改的數據或者多個用戶同時修改同一數據，從而確保事務完整性和數據庫一致性。雖然SQLServer會自動強制執行鎖，但是用戶可以通過對鎖進行了解并在應用程序中自定義鎖來設計出更有效率的應用程序。10.6SQLServer 的鎖機制10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.6.1鎖模式SQLServer2008使用不同的鎖模式鎖定資源，這些鎖模式確定了并發事務訪問資源的方式。（1）共享鎖（SharedLock）。共享鎖鎖定的資源可以被其他用戶讀取，但其他用戶不能修改它（只讀操作）。例如在SELECT語句執行時，SQLServer通常會對對象進行共享鎖鎖定。通常加共享鎖的數據頁被讀取完畢后，共享鎖就會立即被釋放。（2）排他鎖（ExclusiveLock）。排他鎖鎖定的資源只允許進行鎖定操作的程序使用，其他任何對它的操作均不會被接受。例如執行數據更新語句（INSERT、UPDATE或DELETE）時，SQLServer會自動使用排他鎖，確保不會同時對同一資源進行多重更新。當對象上有其他鎖存在時，無法對其加排他鎖。排他鎖一直到事務結束才能被釋放。（3）更新鎖（UpdateLock）。更新鎖用于可更新的資源中，是為了防止死鎖而設立的。10.6SQLServer 的鎖機制10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.6.1鎖模式從程序員的角度，鎖可以分為以下兩種類型。（1）樂觀鎖（OptimisticLock）。樂觀鎖假定在處理數據時，不需要在應用程序的代碼中做任何事情就可以直接在記錄上加鎖，即完全依靠數據庫來管理鎖的工作。一般情況下，當執行事務處理時，SQLServer會自動對事務處理范圍內更新到的表做鎖定。（2）悲觀鎖（PessimisticLock）。悲觀鎖需要程序員直接管理數據或對象上的加鎖處理，并負責獲取、共享和放棄正在使用的數據上的任何鎖。10.6SQLServer 的鎖機制10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.6.2隔離級別事務準備接受不一致數據的級別稱為隔離級別（IsolationLevel）。隔離級別是一個事務必須與其他事務進行隔離的程度。較低的隔離級別可以增加并發，但代價是降低數據的正確性。相反，較高的隔離級別可以確保數據的正確性，但可能對并發產生負面影響。應用程序要求的隔離級別確定了SQLServer使用的鎖定行為。10.6SQLServer 的鎖機制10.1存儲過程概述10.2存儲過程的創建與使用10.3觸發器概述10.4觸發器的創建與使用10.5事務處理10.6鎖機制10.6.2隔離級別在SQLServer支持以下4種隔離級別：（1）提交讀（ReadCommitted）。它是SQLServer的默認級別。在此隔離級別下，SELECT語句不會也不能返回尚未提交（committed）的即臟數據。（2）未提交讀（ReadUncommitted）。與提交讀隔離級別相反，它允許讀取臟數據，即已經被其他用戶修改但尚未提交的數據。它是最低的事務隔離級別，僅可保證不讀取物理損壞的數據。（3）可重復讀（R