第10章存儲過程與觸發(fā)器ppt課件

1、第第10章章 存儲過程與觸發(fā)器存儲過程與觸發(fā)器本章內(nèi)容本章內(nèi)容10.1 存儲過程概述存儲過程概述10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用10.3 觸發(fā)器概述觸發(fā)器概述10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用10.5 事務(wù)處理事務(wù)處理10.6 SQL Server的鎖機制的鎖機制10.1 存儲過程概述存儲過程概述 n存儲過程是存儲過程是SQL Server服務(wù)器上一組預(yù)編服務(wù)器上一組預(yù)編譯的譯的Transact-SQL語句，用于完成某項任語句，用于完成某項任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。值，并且可以嵌套調(diào)用。10.1

2、 10.1 存儲過程概述存儲過程概述nSQL Server存儲過程的類型包括：存儲過程的類型包括：n系統(tǒng)存儲過程系統(tǒng)存儲過程n用戶定義存儲過程用戶定義存儲過程n臨時存儲過程臨時存儲過程n擴展存儲過程擴展存儲過程1. 存儲過程的類型存儲過程的類型10.1 10.1 存儲過程概述存儲過程概述(1)系統(tǒng)存儲過程系統(tǒng)存儲過程 n是指由系統(tǒng)提供的存儲過程，主要存儲在是指由系統(tǒng)提供的存儲過程，主要存儲在master數(shù)據(jù)庫中數(shù)據(jù)庫中并以并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理理員管理SQL Server提供支持。提供支持。n通過系統(tǒng)存儲過程，通過

3、系統(tǒng)存儲過程，SQL Server中的許多管理性或信息性中的許多管理性或信息性的活動的活動(例如使用例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)可以了解數(shù)據(jù)數(shù)據(jù)庫對象、數(shù)據(jù)庫信息數(shù)據(jù)庫對象、數(shù)據(jù)庫信息)都可以順利有效地完成。盡管都可以順利有效地完成。盡管系統(tǒng)存儲過程被放在系統(tǒng)存儲過程被放在master數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)數(shù)據(jù)庫中，仍可以在其他數(shù)據(jù)庫中對其進(jìn)行調(diào)用庫中對其進(jìn)行調(diào)用(調(diào)用時，不必在存儲過程名前加上數(shù)調(diào)用時，不必在存儲過程名前加上數(shù)據(jù)庫名據(jù)庫名)。當(dāng)創(chuàng)建一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會。當(dāng)創(chuàng)建一個新數(shù)據(jù)庫時，一些系統(tǒng)存儲過程會在新數(shù)據(jù)庫中被自動創(chuàng)建。在新

4、數(shù)據(jù)庫中被自動創(chuàng)建。10.1 10.1 存儲過程概述存儲過程概述(2)用戶定義存儲過程用戶定義存儲過程n是由用戶創(chuàng)建并能完成某一特定功能是由用戶創(chuàng)建并能完成某一特定功能(例如查詢用例如查詢用戶所需數(shù)據(jù)信息戶所需數(shù)據(jù)信息)的存儲過程。的存儲過程。n它處于用戶創(chuàng)建的數(shù)據(jù)庫中，存儲過程名前沒有它處于用戶創(chuàng)建的數(shù)據(jù)庫中，存儲過程名前沒有前綴前綴sp_。10.1 10.1 存儲過程概述存儲過程概述(3)臨時存儲過程臨時存儲過程n臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全臨時存儲過程與臨時表類似，分為局部臨時存儲過程和全局臨時存儲過程，且可以分別向該過程名稱前面添加局臨時存儲過程，且可以分別向該過

5、程名稱前面添加“#”或或“# #”前綴表示。前綴表示。“#”表示本地臨時存儲過程，表示本地臨時存儲過程，“# #”表示全局臨時存儲過程。使用臨時存儲過程必須創(chuàng)建本地表示全局臨時存儲過程。使用臨時存儲過程必須創(chuàng)建本地連接，當(dāng)連接，當(dāng)SQL Server關(guān)閉后，這些臨時存儲過程將自動被關(guān)閉后，這些臨時存儲過程將自動被刪除。刪除。n由于由于SQL Server支持重新使用執(zhí)行計劃，所以連接到支持重新使用執(zhí)行計劃，所以連接到SQL Server 2000的應(yīng)用程序應(yīng)使用的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲過程，系統(tǒng)存儲過程，而不使用臨時存儲過程。而不使用臨時存儲過程。10.1 10.1

6、存儲過程概述存儲過程概述(4)擴展存儲過程擴展存儲過程n擴展存儲過程是擴展存儲過程是SQL Server可以動態(tài)裝載和執(zhí)行可以動態(tài)裝載和執(zhí)行的動態(tài)鏈接庫的動態(tài)鏈接庫(DLL)。當(dāng)擴展存儲過程加載到。當(dāng)擴展存儲過程加載到SQL Server中，它的使用方法與系統(tǒng)存儲過程一中，它的使用方法與系統(tǒng)存儲過程一樣。擴展存儲過程只能添加到樣。擴展存儲過程只能添加到master數(shù)據(jù)庫中，數(shù)據(jù)庫中，其前綴是其前綴是xp_。10.1 10.1 存儲過程概述存儲過程概述2. 存儲過程的功能特點存儲過程的功能特點nSQL Server的存儲過程可實現(xiàn)以下功能：的存儲過程可實現(xiàn)以下功能：n(1)接收輸入?yún)?shù)并以輸出參

7、數(shù)的形式為調(diào)用過程接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過程或批處理返回多個值?；蚺幚矸祷囟鄠€值。n(2)包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其包含執(zhí)行數(shù)據(jù)庫操作的編程語句，包括調(diào)用其他過程。他過程。n(3)為調(diào)用過程或批處理返回一個狀態(tài)值，以表示為調(diào)用過程或批處理返回一個狀態(tài)值，以表示成功或失敗成功或失敗(及失敗原因及失敗原因)。10.1 10.1 存儲過程概述存儲過程概述存儲過程具有以下優(yōu)點存儲過程具有以下優(yōu)點(1)(1)模塊化編程。模塊化編程。(2)(2)快速執(zhí)行?？焖賵?zhí)行。 (3)(3)減少網(wǎng)絡(luò)通信量。減少網(wǎng)絡(luò)通信量。(4)(4)提供安全機制。提供安全機制。(5)(5)保證操作一致性

8、。保證操作一致性。10.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用1. 使用企業(yè)管理器創(chuàng)建存儲過程使用企業(yè)管理器創(chuàng)建存儲過程存儲過程創(chuàng)建存儲過程創(chuàng)建10.2.1 10.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程2. 使用向?qū)?chuàng)建存儲過程使用向?qū)?chuàng)建存儲過程3. 使用使用CREATE PROCEDURE語句創(chuàng)建存儲過程語句創(chuàng)建存儲過程n使用使用CREATE PROCEDURE語句創(chuàng)建存儲過程應(yīng)該考慮以語句創(chuàng)建存儲過程應(yīng)該考慮以下幾個方面：下幾個方面：n(1)在一個批處理中，在一個批處理中，CREATE PROCEDURE語句不能與其語句不能與其

9、他他SQL語句合并在一起。語句合并在一起。n(2)數(shù)據(jù)庫所有者具有默認(rèn)的創(chuàng)建存儲過程的權(quán)限，它可把數(shù)據(jù)庫所有者具有默認(rèn)的創(chuàng)建存儲過程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。該權(quán)限傳遞給其他的用戶。n(3)存儲過程作為數(shù)據(jù)庫對象其命名必須符合標(biāo)識符的命名存儲過程作為數(shù)據(jù)庫對象其命名必須符合標(biāo)識符的命名規(guī)則。規(guī)則。n(4)只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫的存儲過程。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫的存儲過程。10.2.1 10.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程創(chuàng)建存儲過程語句的語法格式如下：創(chuàng)建存儲過程語句的語法格式如下：CREATE PROCEDURE procedure_name ; n

10、umber parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement ,.n 10.2.1 10.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程例例10-1 創(chuàng)建存儲過程，從表創(chuàng)建存儲過程，從表goods和表和表goods_classification的聯(lián)接中返回商品名、商品類別、的聯(lián)接中返回商品名、商品類別、單價。單價。10.2.1 10.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程CREATE PROCEDU

11、RE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification gcON g.classification_id = gc.classification_id存儲過程創(chuàng)建后，存儲過程的名稱存放在存儲過程創(chuàng)建后，存儲過程的名稱存放在sysobject表中，文表中，文本存放在本存放在syscomments表中。表中。執(zhí)行存儲過程執(zhí)行存儲過程n執(zhí)行存儲過程的語法格式：執(zhí)行存儲過程的語法格式：nEXECUTEn return_status=n p

12、rocedure_name ;number|procedure_name_var parameter=value|variablen OUTPUT|DEFAULTn ,.n nWITH RECOMPILE 10.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用例如，執(zhí)行例例如，執(zhí)行例10-1的存儲過程的存儲過程goods_infon在在SQL查詢分析器中輸入命令：查詢分析器中輸入命令：nEXEC goods_infon運行的結(jié)果：運行的結(jié)果：10.2.2 10.2.2 執(zhí)行存儲過程執(zhí)行存儲過程10.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用修改存儲過程修改存儲過程1. 使用企

13、業(yè)管理器修改存儲過程使用企業(yè)管理器修改存儲過程2. 使用使用ALTER PROCEDURE語句修改存儲過程語句修改存儲過程nALTER PROCEDURE的語法規(guī)則是：的語法規(guī)則是：nALTER PROCEDURE procedure_name ; number nparameter data_typenVARYING=default OUTPUT ,.n nWITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONnFOR REPLICATION nAS sql_statement .n 10.2.3 10.2.3 修改存儲過程修改存儲過程查看存儲

14、過程的文本信息查看存儲過程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = P AND = procedure_name GO10.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用刪除存儲過程刪除存儲過程 1.使用企業(yè)管理器刪除存儲過程使用企業(yè)管理器刪除存儲過程2. 使用使用DROP PROCEDURE刪除存儲過程刪除存儲過程DROP PROCEDURE的語法如下：的語法如下：DROP PROCEDURE procedure_name

15、 ,.n n例如刪除例例如刪除例10-1創(chuàng)建的存儲過程創(chuàng)建的存儲過程goods_info：nDROP PROCEDURE goods_infonGO10.2 10.2 存儲過程的創(chuàng)建與使用存儲過程的創(chuàng)建與使用存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值n存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的存儲過程和調(diào)用者之間通過參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲過參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過存儲過程返回的狀態(tài)值對存儲過程進(jìn)行管理。程返回的狀態(tài)值對存儲過程進(jìn)行管理。n1. 參數(shù)參數(shù)n存儲過程的參數(shù)在創(chuàng)建過程時聲明。存儲過程的參數(shù)在創(chuàng)建過程時聲明。

16、nSQL Server支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。n(1)輸入?yún)?shù)輸入?yún)?shù)n輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。要定義輸入?yún)?shù)允許調(diào)用程序為存儲過程傳送數(shù)據(jù)值。要定義存儲過程的輸入?yún)?shù)，必須在存儲過程的輸入?yún)?shù)，必須在CREATE PROCEDURE語語句中聲明一個或多個變量及類型。句中聲明一個或多個變量及類型。例例10-3 創(chuàng)建帶參數(shù)的存儲過程，從表創(chuàng)建帶參數(shù)的存儲過程，從表employee、sell_order、goods、goods_classification的連接中返回輸入的員工名、該的連接中返回輸入的員工名、該員工銷售的商品名、商品類別

17、、銷售量等信息。員工銷售的商品名、商品類別、銷售量等信息。CREATE PROC sell_info employee_name varchar(20)AS SELECT employee_name, goods_name,classification_name, order_numFROM employee e INNER JOIN sell_order s ON e.employee_id=s.employee_id JOIN goods g ON g.goods_id=s.goods_id JOIN goods_classification gc ON gc.classification

18、_id=g.classification_idWHERE employee_name LIKE employee_name10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值n執(zhí)行存儲過程執(zhí)行存儲過程sell_info以以nEXEC sell_info 東方牧東方牧n參數(shù)值可以包含通配符參數(shù)值可以包含通配符“%”，例如，查找所有姓，例如，查找所有姓“錢錢的員工的銷售情況可以使用以下命令：的員工的銷售情況可以使用以下命令：nEXEC sell_info 錢錢%10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值存儲過程執(zhí)行時，參數(shù)可以由位置標(biāo)識，也可以由存儲過程執(zhí)

19、行時，參數(shù)可以由位置標(biāo)識，也可以由名字標(biāo)識。名字標(biāo)識。n例如，定義一個具有例如，定義一個具有3個參數(shù)的存儲過程：個參數(shù)的存儲過程：nCREATE PROC myproc val1 int, val2 int, val3 intnAS .n參數(shù)以位置傳遞：參數(shù)以位置傳遞：nEXEC myproc 10, 20, 15n參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：參數(shù)以名字傳遞，每個值由對應(yīng)的參數(shù)名引導(dǎo)：nEXEC myproc val2=20, val1=10, val3=15存儲過程中參數(shù)的標(biāo)識存儲過程中參數(shù)的標(biāo)識10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值(2)輸出參數(shù)

20、輸出參數(shù)n輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。輸出參數(shù)允許存儲過程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。nOUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過程中的參數(shù)。中的參數(shù)。n為了使用輸出參數(shù)，在為了使用輸出參數(shù)，在CREATE PROCEDURE和和EXECUTE語句中都必須使用語句中都必須使用OUTPUT關(guān)鍵字。關(guān)鍵字。10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值例例10-4 創(chuàng)建存儲過程創(chuàng)建存儲過程price_goods，通過輸入?yún)?shù)在，通過輸入?yún)?shù)在goods表表中查找商品，以輸出參數(shù)獲取商品單價。中查找

21、商品，以輸出參數(shù)獲取商品單價。CREATE PROC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值n執(zhí)行執(zhí)行price_goods存儲過程的代碼如下：存儲過程的代碼如下：nDECLARE price realnEXEC price_goods Canon LBP2900, price OUTPUTnSELECT

22、 priceEXECUTE語句還需要關(guān)鍵字語句還需要關(guān)鍵字OUTPUT以允許參數(shù)以允許參數(shù)值返回給變量。值返回給變量。10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值(1)用用RETURN語句定義返回值語句定義返回值存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或存儲過程可以返回整型狀態(tài)值，表示過程是否成功執(zhí)行，或者過程失敗的原因。者過程失敗的原因。如果存儲過程沒有顯式設(shè)置返回代碼的值，則如果存儲過程沒有顯式設(shè)置返回代碼的值，則SQL Server返返回代碼為回代碼為 0，表示成功執(zhí)行；若返回，表示成功執(zhí)行；若返回-1 -99之間的整數(shù)，之間的整數(shù)，表示沒有成功執(zhí)行。也

23、可以使用表示沒有成功執(zhí)行。也可以使用RETURN語句，用大于語句，用大于0或小于或小于-99的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的整數(shù)來定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。的執(zhí)行結(jié)果。2. 返回存儲過程的狀態(tài)返回存儲過程的狀態(tài)(2)捕獲返回狀態(tài)值捕獲返回狀態(tài)值使用以下語句接收返回的狀態(tài)值使用以下語句接收返回的狀態(tài)值 EXECUTE status_var=procedure_name10.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值例例10-5 創(chuàng)建存儲過程，輸入商品類別，返回各種商品名稱。創(chuàng)建存儲過程，輸入商品類別，返回各種商品名稱。在存儲過程中，用值在存儲過程中

24、，用值15表示用戶沒有提供參數(shù)；值表示用戶沒有提供參數(shù)；值-l01表示表示沒有輸入商品類別；值沒有輸入商品類別；值0表示過程運行沒有出錯。表示過程運行沒有出錯。CREATE PROC cl_goods cl_name varchar(40)=NULLASIF cl_name=NULL RETURN 15IF NOT EXISTS (SELECT * FROM goods_classification WHERE classification_name=cl_name) RETURN -101SELECT g.goods_name FROM goods_classification gc,goo

25、ds gWHERE gc.classification_id =g.classification_id AND gc.classification_name=cl_nameRETURN 010.2.5 10.2.5 存儲過程參數(shù)與狀態(tài)值存儲過程參數(shù)與狀態(tài)值DECLARE return_status intEXEC return_status=cl_goods 筆記本計算機筆記本計算機IF return_status=15 SELECT 語法錯誤語法錯誤ELSE IF return_status=-101 SELECT 沒有找到該商品類別沒有找到該商品類別例例10-5的存儲過程的存儲過程cl_g

26、oods執(zhí)行時使用以下語句：執(zhí)行時使用以下語句：n批處理是包含一個或多個批處理是包含一個或多個Transact-SQL語句的組，從應(yīng)用程序語句的組，從應(yīng)用程序一次性地發(fā)送到一次性地發(fā)送到SQL Server執(zhí)行。執(zhí)行。 SQL Server將批處理語句編將批處理語句編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃，每次執(zhí)行其中的譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃，每次執(zhí)行其中的一條語句。一條語句。n函數(shù)是由一個或多個函數(shù)是由一個或多個Transact-SQL語句組成的子程序，可用于語句組成的子程序，可用于封裝代碼以便重新使用。封裝代碼以便重新使用。n存儲過程是一組存儲過程是一組SQL語句的集合，類

27、似于程序的模塊。它與函語句的集合，類似于程序的模塊。它與函數(shù)不同，因為它不返回取代其名稱的值，也不能直接用在表達(dá)數(shù)不同，因為它不返回取代其名稱的值，也不能直接用在表達(dá)式中。式中。 批處理、函數(shù)、存儲過程的區(qū)別批處理、函數(shù)、存儲過程的區(qū)別n存儲過程、批處理文件和函數(shù)都是由一個或多個存儲過程、批處理文件和函數(shù)都是由一個或多個Transact-SQL語句組成的，通?？梢酝瓿梢幌盗械牟僮?，但是它們的適用范語句組成的，通常可以完成一系列的操作，但是它們的適用范圍和產(chǎn)生的效果是不同的，使用時應(yīng)當(dāng)認(rèn)真分析，區(qū)別對待。圍和產(chǎn)生的效果是不同的，使用時應(yīng)當(dāng)認(rèn)真分析，區(qū)別對待。n觸發(fā)器是一種特殊類型的存儲過程，用于

28、實現(xiàn)比較復(fù)雜的觸發(fā)器是一種特殊類型的存儲過程，用于實現(xiàn)比較復(fù)雜的數(shù)據(jù)約束。數(shù)據(jù)約束。n觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲過程觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲過程可以通過過程名字直接調(diào)用。當(dāng)對某一表進(jìn)行可以通過過程名字直接調(diào)用。當(dāng)對某一表進(jìn)行UPDATE、INSERT、DELETE操作時，操作時，SQL Server就會自動執(zhí)行觸就會自動執(zhí)行觸發(fā)器所定義的發(fā)器所定義的SQL語句，從而確保對數(shù)據(jù)的處理必須符合語句，從而確保對數(shù)據(jù)的處理必須符合由這些由這些SQL語句所定義的規(guī)則。語句所定義的規(guī)則。n觸發(fā)器的主要作用就是能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證觸發(fā)器的主要作用就是能夠?qū)?/p>

29、現(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。的參照完整性和數(shù)據(jù)的一致性。 10.3 觸發(fā)器概述觸發(fā)器概述n觸發(fā)器的其它功能觸發(fā)器的其它功能n強化約束：觸發(fā)器能夠?qū)崿F(xiàn)比強化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHEAK語句更為復(fù)雜的約語句更為復(fù)雜的約束。束。n跟蹤變化：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許跟蹤變化：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，從而不允許數(shù)據(jù)庫中不經(jīng)許可的指定更新和變化。數(shù)據(jù)庫中不經(jīng)許可的指定更新和變化。n級聯(lián)運行：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級級聯(lián)運行：觸發(fā)器可以偵測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫地各項內(nèi)容。聯(lián)影響整個數(shù)據(jù)庫地各項內(nèi)容。n存儲過程的調(diào)用：為了

30、響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用存儲過程的調(diào)用：為了響應(yīng)數(shù)據(jù)庫更新，觸發(fā)器可以調(diào)用一個或多個存儲過程，甚至可以通過外部過程的調(diào)用而在一個或多個存儲過程，甚至可以通過外部過程的調(diào)用而在DBMS本身之外進(jìn)行操作。本身之外進(jìn)行操作。n稱號：觸發(fā)器有一個符合標(biāo)志符命名規(guī)則的名稱。稱號：觸發(fā)器有一個符合標(biāo)志符命名規(guī)則的名稱。n定義的目標(biāo)：觸發(fā)器必須定義在表或視圖上。定義的目標(biāo)：觸發(fā)器必須定義在表或視圖上。n觸發(fā)條件：是觸發(fā)條件：是UPDATE、INSERT，還是，還是DELETE語句。語句。n觸發(fā)邏輯：觸發(fā)之后如何處理。觸發(fā)邏輯：觸發(fā)之后如何處理。 觸發(fā)器的四要素觸發(fā)器的四要素1. 使用企業(yè)管理器創(chuàng)建觸發(fā)

31、器使用企業(yè)管理器創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器2. 使用使用CREATE TRIGGER語句創(chuàng)建觸發(fā)器語句創(chuàng)建觸發(fā)器nCREATE TRIGGER語句的語法格式如下：語句的語法格式如下：nCREATE TRIGGER trigger_name nON table_name | view n WITH ENCRYPTION n FOR | AFTER | INSTEAD OF -觸發(fā)器的類型觸發(fā)器的類型n INSERT , UPDATE , DELETE

32、nAS sql_statement . n 10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器觸發(fā)器的類型觸發(fā)器的類型nAFTER觸發(fā)器觸發(fā)器n該觸發(fā)器會在觸發(fā)該觸發(fā)器會在觸發(fā)INSERT、UPDATE或或DELETE動作之后動作之后執(zhí)行。執(zhí)行。n該觸發(fā)器只有在已插入一行或是多行和所有約束已被處理且該觸發(fā)器只有在已插入一行或是多行和所有約束已被處理且通過后才觸發(fā)。通過后才觸發(fā)。n該觸發(fā)器只能在表上定義，可以為表的同一操作定義多個觸該觸發(fā)器只能在表上定義，可以為表的同一操作定義多個觸發(fā)器。發(fā)器。nINSTEAD OF觸發(fā)器觸發(fā)器n該觸發(fā)器代替觸發(fā)動作進(jìn)行激活，即表示不執(zhí)行其所定義的該觸發(fā)器代替觸發(fā)

33、動作進(jìn)行激活，即表示不執(zhí)行其所定義的操作操作(INSERT、 UPDATE、DELETE)而僅執(zhí)行觸發(fā)器本身。而僅執(zhí)行觸發(fā)器本身。n兩者的區(qū)別兩者的區(qū)別nAFTER觸發(fā)器是告訴觸發(fā)器是告訴SQL語句執(zhí)行了語句執(zhí)行了INSERT、UPDATE或或DELETE操作后干什么操作后干什么(操作已經(jīng)做了，數(shù)據(jù)已經(jīng)變動操作已經(jīng)做了，數(shù)據(jù)已經(jīng)變動了了)。nINSTEAD OF觸發(fā)器是告訴當(dāng)執(zhí)行觸發(fā)器是告訴當(dāng)執(zhí)行INSERT、 UPDATE或或者者DELETE操作時用什么別的操作來代替操作時用什么別的操作來代替(操作不做，而操作不做，而是做另外一種觸發(fā)器定義的操作是做另外一種觸發(fā)器定義的操作) 。10.4.1

34、 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器例例10-6 在在employee表上創(chuàng)建一個表上創(chuàng)建一個DELETE類型的觸發(fā)器，類型的觸發(fā)器，該觸發(fā)器的名稱為該觸發(fā)器的名稱為tr_employee.(1)創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個員工被刪除個員工被刪除 SELECT msg RETURN10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器執(zhí)行觸發(fā)器tr_employee觸發(fā)器

35、不能通過名字來執(zhí)行，而是在相應(yīng)的觸發(fā)器不能通過名字來執(zhí)行，而是在相應(yīng)的SQL語句被執(zhí)行語句被執(zhí)行時自動觸發(fā)的。時自動觸發(fā)的。例如執(zhí)行以下例如執(zhí)行以下DELETE語句：語句：DELETE FROM employeeWHERE employee_name = 劉葉劉葉10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器n在觸發(fā)器的執(zhí)行過程中，在觸發(fā)器的執(zhí)行過程中，SQL Server建立和管理兩個臨時的虛擬建立和管理兩個臨時的虛擬表：表：Deleted表和表和Inserted表。這兩個表包含了在激發(fā)觸發(fā)器的操表。這兩個表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄?？梢杂眠@一特性來測試某些數(shù)據(jù)修作中

36、插入或刪除的所有記錄?？梢杂眠@一特性來測試某些數(shù)據(jù)修改的效果，以及設(shè)置觸發(fā)器操作的條件。這兩個特殊表可供用戶改的效果，以及設(shè)置觸發(fā)器操作的條件。這兩個特殊表可供用戶瀏覽，但是用戶不能直接改變表中的數(shù)據(jù)。瀏覽，但是用戶不能直接改變表中的數(shù)據(jù)。n在執(zhí)行在執(zhí)行INSERT或或UPDATE語句之后所有被添加或被更新的記錄語句之后所有被添加或被更新的記錄都會存儲在都會存儲在Inserted表中。表中。n在執(zhí)行在執(zhí)行DELETE或或UPDATE語句時，從觸發(fā)程序表中被刪除的行語句時，從觸發(fā)程序表中被刪除的行會發(fā)送到會發(fā)送到Deleted表。表。n對于更新操作，對于更新操作，SQL Server先將要進(jìn)行修

37、改的記錄存儲到先將要進(jìn)行修改的記錄存儲到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。表以及觸發(fā)程序表。3. Deleted表和表和Inserted表表10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器Deleted表和表和Inserted表在執(zhí)行觸發(fā)程序時的情況表在執(zhí)行觸發(fā)程序時的情況Transact-SQL語句語句Inserted表表Deleted表表INSERT所要添加的行所要添加的行空空UPDATE新的行新的行舊的行舊的行DELETE空空刪除的行刪除的行10.4.1 10.4.1 創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器例例10-7 為表為表

38、customer創(chuàng)建一個名為創(chuàng)建一個名為test_tr的觸發(fā)器，的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時，激活該觸發(fā)器。當(dāng)執(zhí)行添加、更新或刪除時，激活該觸發(fā)器。n創(chuàng)建創(chuàng)建test_tr觸發(fā)器：觸發(fā)器：nCREATE TRIGGER test_trnON customer FOR INSERT,UPDATE,DELETEnASn SELECT * FROM insertedn SELECT * FROM deletedncustomer表執(zhí)行以下插入操作：表執(zhí)行以下插入操作：nINSERT INTO customer(customer_id,customer_name,telephone)nVALUE

39、S(12346,張三張三,1234567)10.4 10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用刪除觸發(fā)器刪除觸發(fā)器1. 使用企業(yè)管理器刪除觸發(fā)器使用企業(yè)管理器刪除觸發(fā)器10.4.2 10.4.2 刪除觸發(fā)器刪除觸發(fā)器2. 使用使用DROP TRIGGER刪除指定觸發(fā)器刪除指定觸發(fā)器n刪除觸發(fā)器語句的語法格式如下：刪除觸發(fā)器語句的語法格式如下：nDROP TRIGGER trigger_name , .n n例如，刪除例例如，刪除例10-6的觸發(fā)器的觸發(fā)器tr_employee：nDROP TRIGGER tr_employee修改觸發(fā)器修改觸發(fā)器n通過企業(yè)管理器、系統(tǒng)存儲過程或通過企業(yè)管理

40、器、系統(tǒng)存儲過程或Transact_SQL語句，語句，可以修改觸發(fā)器的名字和正文?？梢孕薷挠|發(fā)器的名字和正文。10.4 10.4 觸發(fā)器的創(chuàng)建與使用觸發(fā)器的創(chuàng)建與使用1. 使用使用sp_rename系統(tǒng)存儲過程修改觸發(fā)器的名字：系統(tǒng)存儲過程修改觸發(fā)器的名字：語法格式為：語法格式為：sp_rename oldname, newname2. 使用企業(yè)管理器修改觸發(fā)器的正文使用企業(yè)管理器修改觸發(fā)器的正文3. 使用使用ALTER TRIGGER語句修改觸發(fā)器語句修改觸發(fā)器10.4.3 10.4.3 修改觸發(fā)器修改觸發(fā)器nALTER TRIGGER修改觸發(fā)器的語法如下：修改觸發(fā)器的語法如下：nALTER

41、 TRIGGER trigger_name nON table | view nWITH ENCRYPTION nFOR | AFTER | INSTEAD OFnDELETE , INSERT , UPDATE nAS sql_statement .n 10.4.3 10.4.3 修改觸發(fā)器修改觸發(fā)器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個員工數(shù)據(jù)被插入個員工數(shù)據(jù)被插入SELECT msgRETURN例如，將例例如，將例10-6的觸發(fā)

42、器的觸發(fā)器tr_employee修改為修改為INSERT操作后進(jìn)行。操作后進(jìn)行。n對對employee表執(zhí)行以下插入語句：表執(zhí)行以下插入語句：nINSERT employee(employee_id,employee_name)VALUES (E016,王五王五)n激活激活I(lǐng)NSERT觸發(fā)器觸發(fā)器tr_employee，顯示信息如下：，顯示信息如下：n1個員工數(shù)據(jù)被插入個員工數(shù)據(jù)被插入n事務(wù)事務(wù)(Transaction)是是SQL Server中的一個邏輯工作單元，中的一個邏輯工作單元，該單元將被作為一個整體進(jìn)行處理。該單元將被作為一個整體進(jìn)行處理。n事務(wù)可以是一組事務(wù)可以是一組SQL語句、一

43、條語句、一條SQL語句或整個程序，在語句或整個程序，在通常情況下，一個應(yīng)用程序里包含多個事務(wù)。通常情況下，一個應(yīng)用程序里包含多個事務(wù)。n事務(wù)保證連續(xù)多個操作必須全部執(zhí)行成功，否則必須立即事務(wù)保證連續(xù)多個操作必須全部執(zhí)行成功，否則必須立即回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要么全回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要么全部將數(shù)據(jù)所要執(zhí)行的操作完成，要么全部數(shù)據(jù)都不修改。部將數(shù)據(jù)所要執(zhí)行的操作完成，要么全部數(shù)據(jù)都不修改。10.5.1 事務(wù)概述事務(wù)概述10.5 事務(wù)處理事務(wù)處理10.5.1 10.5.1 事務(wù)概述事務(wù)概述n例如，企業(yè)取消了倉儲部，需要將例如，企業(yè)取消了倉儲部，需要將“

44、倉儲部從倉儲部從department表中表中刪除，而刪除，而employee表中的部門編號與倉儲部相對應(yīng)的員工也應(yīng)刪表中的部門編號與倉儲部相對應(yīng)的員工也應(yīng)刪除。因此，兩個表都需要修改，這種修改只能通過兩條語句除。因此，兩個表都需要修改，這種修改只能通過兩條語句DELETE進(jìn)行。進(jìn)行。n假設(shè)倉儲部編號為假設(shè)倉儲部編號為D004，第一條，第一條DELETE語句修改語句修改department表為：表為：nDELETE FROM department WHERE department_id = D004n第二條第二條DELETE語句修改語句修改employee表為：表為：nDELETE FROM e

45、mployee WHERE department_id = D004 n因此，必須保證這兩條因此，必須保證這兩條DELETE語句同時執(zhí)行，或都不執(zhí)行。這語句同時執(zhí)行，或都不執(zhí)行。這時可以使用數(shù)據(jù)庫中的事務(wù)技術(shù)來實現(xiàn)。時可以使用數(shù)據(jù)庫中的事務(wù)技術(shù)來實現(xiàn)。1事務(wù)的由來事務(wù)的由來n在在SQL Server中，使用中，使用DELETE或或UPDATE語句對數(shù)據(jù)庫進(jìn)行更語句對數(shù)據(jù)庫進(jìn)行更新時一次只能操作一個表，這會帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題。新時一次只能操作一個表，這會帶來數(shù)據(jù)庫的數(shù)據(jù)不一致的問題。2事務(wù)屬性事務(wù)屬性n事務(wù)是網(wǎng)絡(luò)數(shù)據(jù)庫中邏輯操作的基本單位，事務(wù)的事務(wù)是網(wǎng)絡(luò)數(shù)據(jù)庫中邏輯操作的基本單位，事

46、務(wù)的ACID屬性必不可少。屬性必不可少。n由于事務(wù)作為一個邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯誤時，由于事務(wù)作為一個邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯誤時，將取消事務(wù)所做的修改。一個邏輯單元必須具有將取消事務(wù)所做的修改。一個邏輯單元必須具有4個屬性：個屬性：n原子性原子性(Atomicity)n一致性一致性(Consistency)n隔離性隔離性(Isolation)n持久性持久性(Durability)n這些屬性稱為這些屬性稱為ACID。事務(wù)的事務(wù)的ACID屬性屬性n原子性原子性(Atomicity)n事務(wù)必須是工作的最小單位，即原子單元，對于其數(shù)據(jù)的修改，要么事務(wù)必須是工作的最小單位，即原子單元，對

47、于其數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。全都執(zhí)行，要么全都不執(zhí)行。n一致性一致性(Consistency)n事務(wù)在完成后，必須使所有的數(shù)據(jù)都保持一致性狀態(tài)。在相關(guān)數(shù)據(jù)庫事務(wù)在完成后，必須使所有的數(shù)據(jù)都保持一致性狀態(tài)。在相關(guān)數(shù)據(jù)庫中，事務(wù)必須遵守數(shù)據(jù)庫的約束和規(guī)則，以保持所有數(shù)據(jù)的完整性。中，事務(wù)必須遵守數(shù)據(jù)庫的約束和規(guī)則，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。事務(wù)結(jié)束時，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。n隔離性隔離性(Isolation)n一個事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。事務(wù)一個事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。

48、事務(wù)查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，查看數(shù)據(jù)時數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會查看中間狀態(tài)的數(shù)據(jù)。要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會查看中間狀態(tài)的數(shù)據(jù)。這稱為可串行性，因為它能夠重新裝載起始數(shù)據(jù)，并且重播一系列事這稱為可串行性，因為它能夠重新裝載起始數(shù)據(jù)，并且重播一系列事務(wù)，以使數(shù)據(jù)結(jié)束時的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài)相同。務(wù)，以使數(shù)據(jù)結(jié)束時的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài)相同。n持久性持久性(Durability)n事務(wù)完成后，它對于系統(tǒng)的影響是永久的。該修改即使出現(xiàn)系統(tǒng)故障事務(wù)完成后，它對于系統(tǒng)的影響是永

49、久的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。也將一直保持。3事務(wù)模式事務(wù)模式n應(yīng)用程序主要通過指定事務(wù)啟動和結(jié)束的時間來控制事務(wù)。應(yīng)用程序主要通過指定事務(wù)啟動和結(jié)束的時間來控制事務(wù)。這可以使用這可以使用Transact-SQL語句來控制事務(wù)的啟動和結(jié)束。語句來控制事務(wù)的啟動和結(jié)束。系統(tǒng)還必須能夠正確處理那些在事務(wù)完成之前便中止事務(wù)的系統(tǒng)還必須能夠正確處理那些在事務(wù)完成之前便中止事務(wù)的錯誤。錯誤。n事務(wù)是在連接層進(jìn)行管理的。當(dāng)事務(wù)在一個連接上啟動時，事務(wù)是在連接層進(jìn)行管理的。當(dāng)事務(wù)在一個連接上啟動時，在該連接上執(zhí)行的所有在該連接上執(zhí)行的所有Transact-SQL語句在該事務(wù)結(jié)束之語句在該事務(wù)結(jié)束

50、之前都是該事務(wù)的一部分。前都是該事務(wù)的一部分。n引入事務(wù)后，所有對數(shù)據(jù)庫的操作就是以事務(wù)為邏輯單位進(jìn)引入事務(wù)后，所有對數(shù)據(jù)庫的操作就是以事務(wù)為邏輯單位進(jìn)行。一個事務(wù)最終只有兩種狀態(tài)：提交狀態(tài)事務(wù)被正常執(zhí)行。一個事務(wù)最終只有兩種狀態(tài)：提交狀態(tài)事務(wù)被正常執(zhí)行和未提交狀態(tài)事務(wù)必須被回滾）。所以事務(wù)概念的引行和未提交狀態(tài)事務(wù)必須被回滾）。所以事務(wù)概念的引入解決了不可預(yù)見的用戶操作的發(fā)生。入解決了不可預(yù)見的用戶操作的發(fā)生。nSQL Server以以3種事務(wù)模式管理事務(wù)種事務(wù)模式管理事務(wù)n(1) 自動提交事務(wù)模式：每條單獨的語句都是一個事務(wù)。在自動提交事務(wù)模式：每條單獨的語句都是一個事務(wù)。在此模式下，每條

51、此模式下，每條Transact-SQL語句在成功執(zhí)行完成后，都被語句在成功執(zhí)行完成后，都被自動提交，如果遇到錯誤，則自動回滾該語句。該模式為系自動提交，如果遇到錯誤，則自動回滾該語句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。統(tǒng)默認(rèn)的事務(wù)管理模式。n(2) 顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動和結(jié)束。顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動和結(jié)束。事務(wù)以事務(wù)以BEGIN TRANSACTION語句顯式開始，以語句顯式開始，以COMMIT或或ROLLBACK語句顯式結(jié)束。語句顯式結(jié)束。n(3) 隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動啟動

52、。隱性事務(wù)不需要使用自動啟動。隱性事務(wù)不需要使用BEGIN TRANSACTION語語句標(biāo)識事務(wù)的開始，但需要以句標(biāo)識事務(wù)的開始，但需要以COMMIT或或ROLLBACK語語句來提交或回滾事務(wù)。句來提交或回滾事務(wù)。n提示：日常管理和開發(fā)應(yīng)用系統(tǒng)時推薦使用顯式事務(wù)模式。提示：日常管理和開發(fā)應(yīng)用系統(tǒng)時推薦使用顯式事務(wù)模式。1啟動和結(jié)束事務(wù)啟動和結(jié)束事務(wù)啟動事務(wù)語句的語法格式如下定義事務(wù)）：啟動事務(wù)語句的語法格式如下定義事務(wù)）：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description 結(jié)束事務(wù)語句的語法

53、格式如下提交事務(wù)）：結(jié)束事務(wù)語句的語法格式如下提交事務(wù)）：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事務(wù)管理事務(wù)管理nSQL Server 按事務(wù)模式進(jìn)行事務(wù)管理，設(shè)置事務(wù)啟動和按事務(wù)模式進(jìn)行事務(wù)管理，設(shè)置事務(wù)啟動和結(jié)束的時間，正確處理事務(wù)結(jié)束之前產(chǎn)生的錯誤。結(jié)束的時間，正確處理事務(wù)結(jié)束之前產(chǎn)生的錯誤。10.5.2 10.5.2 事務(wù)管理事務(wù)管理例例10-8 建立一個顯式事務(wù)以顯示建立一個顯式事務(wù)以顯示Sales數(shù)據(jù)庫的數(shù)據(jù)庫的employee表的數(shù)據(jù)。表的數(shù)據(jù)。BEGIN TRANSACTION SELEC

54、T * FROM employee COMMIT TRANSACTIONn本例創(chuàng)建的事務(wù)以本例創(chuàng)建的事務(wù)以BEGIN TRANSACTION語句開始，以語句開始，以COMMIT TRANSACTION語句結(jié)束。語句結(jié)束。10.5.2 10.5.2 事務(wù)管理事務(wù)管理DECLARE transaction_name varchar(32)SELECT transaction_name=tran_deleteBEGIN TRANSACTION transaction_name DELETE FROM department WHERE department_id=D004 DELETE FROM em

55、ployee WHERE department_id=D004COMMIT TRANSACTION tran_delete例例10-9 建立一個顯式命名事務(wù)以刪除建立一個顯式命名事務(wù)以刪除department表表的的“倉儲部記錄行。倉儲部記錄行。n本例命名了一個本例命名了一個tran_delete事務(wù)，該事務(wù)用于事務(wù)，該事務(wù)用于department表的表的“倉儲部記錄行及相關(guān)數(shù)據(jù)。倉儲部記錄行及相關(guān)數(shù)據(jù)。10.5.2 10.5.2 事務(wù)管理事務(wù)管理CREATE TABLE imp_tran( num char(2) NOT NULL, cname char(6) NOT NULL)GO SET

56、 IMPLICIT_TRANSACTIONS ON -啟動隱性事務(wù)模式啟動隱性事務(wù)模式GO- 第一個事務(wù)由第一個事務(wù)由INSERT語句啟動語句啟動INSERT INTO imp_tran VALUES (01, Zhang)INSERT INTO imp_tran VALUES (02, Wang)COMMIT TRANSACTION -提交第一個隱性事務(wù)提交第一個隱性事務(wù)GO- 第二個隱式事務(wù)由第二個隱式事務(wù)由SELECT語句啟動語句啟動SELECT COUNT(*) FROM imp_tranINSERT INTO imp_tran VALUES (03, Li)SELECT * FROM

57、 imp_tranCOMMIT TRANSACTION -提交第二個隱性事務(wù)提交第二個隱性事務(wù)GOSET IMPLICIT_TRANSACTIONS OFF -關(guān)閉隱性事務(wù)模式關(guān)閉隱性事務(wù)模式GO例例10-10 隱性事務(wù)處理過程。隱性事務(wù)處理過程。10.5.2 10.5.2 事務(wù)管理事務(wù)管理n事務(wù)回滾使用事務(wù)回滾使用ROLLBACK TRANSACTION語句實現(xiàn)，語句實現(xiàn)，其語法格式如下：其語法格式如下：nROLLBACK TRANSACTION ntransaction_name | tran_name_variablen | savepoint_name | savepoint_vari

58、able n其中，其中， savepoint_name用于指定回滾到某一指定位置的標(biāo)用于指定回滾到某一指定位置的標(biāo)記名稱，記名稱， savepoint_variable為存放該標(biāo)記名稱的變量。為存放該標(biāo)記名稱的變量。2事務(wù)回滾事務(wù)回滾n當(dāng)事務(wù)執(zhí)行過程中遇到錯誤時，該事務(wù)修改的所有數(shù)據(jù)都當(dāng)事務(wù)執(zhí)行過程中遇到錯誤時，該事務(wù)修改的所有數(shù)據(jù)都恢復(fù)到事務(wù)開始時的狀態(tài)或某個指定的位置，事務(wù)占用的恢復(fù)到事務(wù)開始時的狀態(tài)或某個指定的位置，事務(wù)占用的資源將被釋放。這個操作過程叫事務(wù)回滾資源將被釋放。這個操作過程叫事務(wù)回滾(Transaction Rollback)。10.5.2 10.5.2 事務(wù)管理事務(wù)管理例

59、例10-11 使用使用ROLLBACK TRANSACTION語句標(biāo)語句標(biāo)識事務(wù)結(jié)束。識事務(wù)結(jié)束。BEGIN TRANSACTION UPDATE goods SET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,order_num,order_date) VALUES(S00005,G00006,5,getdate( )ROLLBACK TRANSACTION10.5.2 10.5.2 事務(wù)管理事務(wù)管理事務(wù)回滾到指定位置事務(wù)回滾到指定位置n如果要讓

60、事務(wù)回滾到指定位置，則需要在事務(wù)中設(shè)定保存如果要讓事務(wù)回滾到指定位置，則需要在事務(wù)中設(shè)定保存點點(SavePoint)。n所謂保存點是指定其所在位置之前的事務(wù)語句，不能回滾所謂保存點是指定其所在位置之前的事務(wù)語句，不能回滾的語句，即此語句前面的操作被視為有效。其語法格式如的語句，即此語句前面的操作被視為有效。其語法格式如下：下：nSAVE TRANSACTION nsavepoint_name | savepoint_variablen對于長事務(wù)，可以在事務(wù)中設(shè)置若干個保存點。保存點好對于長事務(wù)，可以在事務(wù)中設(shè)置若干個保存點。保存點好比是對事務(wù)做上若干個標(biāo)記，這樣回滾事務(wù)就不必回滾整比是對事務(wù)