[第10章-存儲(chǔ)過(guò)程與觸發(fā)器課件

1、第10章 存儲(chǔ)過(guò)程與觸發(fā)器本章內(nèi)容10.1 存儲(chǔ)過(guò)程概述10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用10.3 觸發(fā)器概述10.4 觸發(fā)器的創(chuàng)建與使用10.5 事務(wù)處理10.6 SQL Server的鎖機(jī)制10.1 存儲(chǔ)過(guò)程概述 存儲(chǔ)過(guò)程是SQL Server服務(wù)器上一組預(yù)編譯的Transact-SQL語(yǔ)句，用于完成某項(xiàng)任務(wù)，它可以接受參數(shù)、返回狀態(tài)值和參數(shù)值，并且可以嵌套調(diào)用。10.1 存儲(chǔ)過(guò)程概述SQL Server存儲(chǔ)過(guò)程的類型包括：系統(tǒng)存儲(chǔ)過(guò)程用戶定義存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程。1. 存儲(chǔ)過(guò)程的類型10.1 存儲(chǔ)過(guò)程概述(1)系統(tǒng)存儲(chǔ)過(guò)程 是指由系統(tǒng)提供的存儲(chǔ)過(guò)程，主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中

2、并以sp_為前綴，它從系統(tǒng)表中獲取信息，從而為系統(tǒng)管理員管理SQL Server提供支持。通過(guò)系統(tǒng)存儲(chǔ)過(guò)程，SQL Server中的許多管理性或信息性的活動(dòng)(例如使用sp_depends、sp_helptexts可以了解數(shù)據(jù)數(shù)據(jù)庫(kù)對(duì)象、數(shù)據(jù)庫(kù)信息)都可以順利有效地完成。盡管系統(tǒng)存儲(chǔ)過(guò)程被放在master數(shù)據(jù)庫(kù)中，仍可以在其他數(shù)據(jù)庫(kù)中對(duì)其進(jìn)行調(diào)用(調(diào)用時(shí)，不必在存儲(chǔ)過(guò)程名前加上數(shù)據(jù)庫(kù)名)。當(dāng)創(chuàng)建一個(gè)新數(shù)據(jù)庫(kù)時(shí)，一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)在新數(shù)據(jù)庫(kù)中被自動(dòng)創(chuàng)建。10.1 存儲(chǔ)過(guò)程概述(2)用戶定義存儲(chǔ)過(guò)程是由用戶創(chuàng)建并能完成某一特定功能(例如查詢用戶所需數(shù)據(jù)信息)的存儲(chǔ)過(guò)程。它處于用戶創(chuàng)建的數(shù)據(jù)庫(kù)中，存儲(chǔ)

3、過(guò)程名前沒(méi)有前綴sp_。10.1 存儲(chǔ)過(guò)程概述(3)臨時(shí)存儲(chǔ)過(guò)程臨時(shí)存儲(chǔ)過(guò)程與臨時(shí)表類似，分為局部臨時(shí)存儲(chǔ)過(guò)程和全局臨時(shí)存儲(chǔ)過(guò)程，且可以分別向該過(guò)程名稱前面添加“#”或“#”前綴表示?！?”表示本地臨時(shí)存儲(chǔ)過(guò)程，“#”表示全局臨時(shí)存儲(chǔ)過(guò)程。使用臨時(shí)存儲(chǔ)過(guò)程必須創(chuàng)建本地連接，當(dāng)SQL Server關(guān)閉后，這些臨時(shí)存儲(chǔ)過(guò)程將自動(dòng)被刪除。由于SQL Server支持重新使用執(zhí)行計(jì)劃，所以連接到SQL Server 2000的應(yīng)用程序應(yīng)使用sp_executesql系統(tǒng)存儲(chǔ)過(guò)程，而不使用臨時(shí)存儲(chǔ)過(guò)程。10.1 存儲(chǔ)過(guò)程概述(4)擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程是SQL Server可以動(dòng)態(tài)裝載和執(zhí)行的動(dòng)態(tài)鏈接

4、庫(kù)(DLL)。當(dāng)擴(kuò)展存儲(chǔ)過(guò)程加載到SQL Server中，它的使用方法與系統(tǒng)存儲(chǔ)過(guò)程一樣。擴(kuò)展存儲(chǔ)過(guò)程只能添加到master數(shù)據(jù)庫(kù)中，其前綴是xp_。10.1 存儲(chǔ)過(guò)程概述2. 存儲(chǔ)過(guò)程的功能特點(diǎn)SQL Server的存儲(chǔ)過(guò)程可實(shí)現(xiàn)以下功能：(1)接收輸入?yún)?shù)并以輸出參數(shù)的形式為調(diào)用過(guò)程或批處理返回多個(gè)值。(2)包含執(zhí)行數(shù)據(jù)庫(kù)操作的編程語(yǔ)句，包括調(diào)用其他過(guò)程。(3)為調(diào)用過(guò)程或批處理返回一個(gè)狀態(tài)值，以表示成功或失敗(及失敗原因)。10.1 存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)(1)模塊化編程。(2)快速執(zhí)行。 (3)減少網(wǎng)絡(luò)通信量。(4)提供安全機(jī)制。(5)保證操作一致性。10.2.1 創(chuàng)建存儲(chǔ)過(guò)

5、程10.2.2 執(zhí)行存儲(chǔ)過(guò)程10.2.3 修改存儲(chǔ)過(guò)程10.2.4 刪除存儲(chǔ)過(guò)程10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.1 創(chuàng)建存儲(chǔ)過(guò)程1使用SQL Server管理平臺(tái)創(chuàng)建存儲(chǔ)過(guò)程 （1）打開(kāi)SQL Server管理平臺(tái)，展開(kāi)節(jié)點(diǎn)“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過(guò)程”，在窗口的右側(cè)顯示出當(dāng)前數(shù)據(jù)庫(kù)的所有存儲(chǔ)過(guò)程。單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程”命令 。10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用（2）在打開(kāi)的SQL命令窗口中，系統(tǒng)給出了創(chuàng)建存儲(chǔ)過(guò)程命令的模板，如圖10-2所示。在模板中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的Transact-SQL語(yǔ)

6、句后，單擊“執(zhí)行”按鈕即可創(chuàng)建存儲(chǔ)過(guò)程。 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程（3）建立存儲(chǔ)過(guò)程的命令被成功執(zhí)行后，在“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過(guò)程”中可以看到新建立的存儲(chǔ)過(guò)程 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程2. 使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程使用CREATE PROCEDURE語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程應(yīng)該考慮以下幾個(gè)方面：(1)在一個(gè)批處理中，CREATE PROCEDURE語(yǔ)句不能與其他SQL語(yǔ)句合并在一起。(2)數(shù)據(jù)庫(kù)所有者具有默認(rèn)的創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，它可把該權(quán)限傳遞給其他的用戶。(3)存儲(chǔ)過(guò)程作為數(shù)據(jù)庫(kù)對(duì)象其命名必須符合標(biāo)識(shí)符的命名規(guī)則。(4)只能在當(dāng)前數(shù)據(jù)

7、庫(kù)中創(chuàng)建屬于當(dāng)前數(shù)據(jù)庫(kù)的存儲(chǔ)過(guò)程。10.2.1 創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句的語(yǔ)法格式如下：CREATE PROCEDURE procedure_name ; number parameter data_type VARYING =default OUTPUT , .n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION FOR REPLICATION AS sql_statement ,.n 10.2.1 創(chuàng)建存儲(chǔ)過(guò)程例10-1 創(chuàng)建存儲(chǔ)過(guò)程，從表goods和表goods_classification的聯(lián)接中返回商品名、商品類別、單價(jià)。10

8、.2.1 創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE goods_info ASSELECT goods_name, classification_name, unit_priceFROM goods g INNER JOIN goods_classification gcON g.classification_id = gc.classification_id存儲(chǔ)過(guò)程創(chuàng)建后，存儲(chǔ)過(guò)程的名稱存放在sysobject表中，文本存放在syscomments表中。10.2.2 執(zhí)行存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)法格式：EXECUTE return_status= procedure_name ;numb

9、er|procedure_name_var parameter=value|variable OUTPUT|DEFAULT ,.n WITH RECOMPILE 10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用例如，執(zhí)行例10-1的存儲(chǔ)過(guò)程goods_info在SQL查詢分析器中輸入命令：EXEC goods_info運(yùn)行的結(jié)果：10.2.2 執(zhí)行存儲(chǔ)過(guò)程10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.3 修改存儲(chǔ)過(guò)程修改存儲(chǔ)過(guò)程可以通過(guò)SQL Server管理平臺(tái)和Transact-SQL語(yǔ)句實(shí)現(xiàn)。1使用SQL Server管理平臺(tái)修改存儲(chǔ)過(guò)程 10.2.3 修改存儲(chǔ)過(guò)程2. 使用ALTER PROCEDURE語(yǔ)句修

10、改存儲(chǔ)過(guò)程ALTER PROCEDURE的語(yǔ)法規(guī)則是：ALTER PROCEDURE procedure_name ; number parameter data_typeVARYING=default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTIONFOR REPLICATION AS sql_statement .n 10.2.3 修改存儲(chǔ)過(guò)程例10-2 使用ALTER PROCEDURE語(yǔ)句更改存儲(chǔ)過(guò)程。(1)創(chuàng)建存儲(chǔ)過(guò)程employee_dep，以獲取總經(jīng)理辦的男員工。CREATE PROCEDURE empl

11、oyee_dep AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department d ON e.department_id=d.department_id WHERE sex=男 AND e.department_id=D001GO執(zhí)行存儲(chǔ)過(guò)程employee_dep，結(jié)果如圖 10.2.3 修改存儲(chǔ)過(guò)程(2) 查看employee_dep存儲(chǔ)過(guò)程的文本信息SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments

12、c ON o.id = c.idWHERE o.type = P AND = employee_depGO10.2.3 修改存儲(chǔ)過(guò)程(3)使用ALTER PROCEDURE語(yǔ)句對(duì)employee_dep過(guò)程進(jìn)行修改，使其能夠顯示出所有男員工，并使employee_dep過(guò)程以加密方式存儲(chǔ)在表syscomments中ALTER PROCEDURE employee_depWITH ENCRYPTION AS SELECT employee_name, sex, address, department_nameFROM employee e INNER JOIN department

13、 d ON e.department_id=d.department_id WHERE sex=男 GO10.2.3 修改存儲(chǔ)過(guò)程執(zhí)行修改后的存儲(chǔ)過(guò)程employee_dep，結(jié)果如圖：10.2.3 修改存儲(chǔ)過(guò)程(4)從系統(tǒng)表sysobjects和syscomments提取修改后的存儲(chǔ)過(guò)程employee_dep的文本信息可以運(yùn)行步驟（2）中的代碼，結(jié)果如圖 這是由于在ALTER PROCEDURE語(yǔ)句中使用WITH ENCRYPTION關(guān)鍵字對(duì)存儲(chǔ)過(guò)程employee_dep的文本進(jìn)行了加密，其文本信息顯示為NULL。 10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.4 刪除存儲(chǔ)過(guò)程 存儲(chǔ)過(guò)程可以被

14、快速刪除和重建，因?yàn)樗鼪](méi)有存儲(chǔ)數(shù)據(jù)。1使用SQL Server管理平臺(tái)刪除存儲(chǔ)過(guò)程 （1）打開(kāi)SQL Server管理平臺(tái)，展開(kāi)節(jié)點(diǎn)“對(duì)象資源管理器”“數(shù)據(jù)庫(kù)服務(wù)器”“可編程性”“存儲(chǔ)過(guò)程”，選擇要?jiǎng)h除的存儲(chǔ)過(guò)程，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對(duì)象”對(duì)話框中單擊“確定”按鈕即可刪除存儲(chǔ)過(guò)程。 10.2.4 刪除存儲(chǔ)過(guò)程2. 使用DROP PROCEDURE刪除存儲(chǔ)過(guò)程DROP PROCEDURE的語(yǔ)法如下：DROP PROCEDURE procedure_name ,.n 例如刪除例10-2創(chuàng)建的存儲(chǔ)過(guò)程employee_dep：DROP PROCEDU

15、RE employee_depGO10.2 存儲(chǔ)過(guò)程的創(chuàng)建與使用10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程和調(diào)用者之間通過(guò)參數(shù)交換數(shù)據(jù)，可以按輸入的參數(shù)執(zhí)行，也可由參數(shù)輸出執(zhí)行結(jié)果。調(diào)用者通過(guò)存儲(chǔ)過(guò)程返回的狀態(tài)值對(duì)存儲(chǔ)過(guò)程進(jìn)行管理。1. 參數(shù)存儲(chǔ)過(guò)程的參數(shù)在創(chuàng)建過(guò)程時(shí)聲明。SQL Server支持兩類參數(shù)：輸入?yún)?shù)和輸出參數(shù)。10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值(1)輸入?yún)?shù)輸入?yún)?shù)允許調(diào)用程序?yàn)榇鎯?chǔ)過(guò)程傳送數(shù)據(jù)值。要定義存儲(chǔ)過(guò)程的輸入?yún)?shù)，必須在CREATE PROCEDURE語(yǔ)句中聲明一個(gè)或多個(gè)變量及類型。10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例10-3 創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程，從表employe

16、e、sell_order、goods、goods_classification的連接中返回輸入的員工名、該員工銷售的商品名、商品類別、銷售量等信息。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

17、 goods_classification gc ON gc.classification_id=g.classification_idWHERE employee_name LIKE employee_name10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值存儲(chǔ)過(guò)程sell_info以employee_name變量作為輸入?yún)?shù)，執(zhí)行時(shí)，可以省略參數(shù)名，直接給參數(shù)值。在SQL查詢分析器中輸入命令：EXEC sell_info 東方牧運(yùn)行結(jié)果如圖。參數(shù)值可以包含通配符“%”，例如，查找所有姓“錢”的員工的銷售情況可以使用以下命令：EXEC sell_info 錢%10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值執(zhí)行時(shí)，參數(shù)

18、可以由位置標(biāo)識(shí)，也可以由名字標(biāo)識(shí)。例如，定義一個(gè)具有3個(gè)參數(shù)的存儲(chǔ)過(guò)程：CREATE PROC myproc val1 int, val2 int, val3 intAS .參數(shù)以位置傳遞：EXEC myproc 10,20,15參數(shù)以名字傳遞，每個(gè)值由對(duì)應(yīng)的參數(shù)名引導(dǎo)：EXEC myproc val2=20,val1=10,val3=15按名字傳遞參數(shù)比按位置傳遞參數(shù)具有更大的靈活性。但是，按位置傳遞參數(shù)卻具有更快的速度。10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值(2)輸出參數(shù)輸出參數(shù)允許存儲(chǔ)過(guò)程將數(shù)據(jù)值或游標(biāo)變量傳回調(diào)用程序。OUTPUT關(guān)鍵字用以指出能返回到調(diào)用它的批處理或過(guò)程中的參數(shù)。為了使用

19、輸出參數(shù)，在CREATE PROCEDURE和EXECUTE語(yǔ)句中都必須使用OUTPUT關(guān)鍵字。10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例10-4 創(chuàng)建存儲(chǔ)過(guò)程price_goods，通過(guò)輸入?yún)?shù)在goods表中查找商品，以輸出參數(shù)獲取商品單價(jià)。CREATE PROC price_goods goods_name varchar(80)=NULL, price_goods real OUTPUTASSELECT price_goods=unit_priceFROM goodsWHERE goods_name=goods_name10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值執(zhí)行price_goods存儲(chǔ)過(guò)程的代

20、碼如下：DECLARE price realEXEC price_goods Canon LBP2900,price OUTPUTSELECT price運(yùn)行結(jié)果是商品名為Canon LBP2900的商品單價(jià)：1380.0EXECUTE語(yǔ)句還需要關(guān)鍵字OUTPUT以允許參數(shù)值返回給變量。10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值(1)用RETURN語(yǔ)句定義返回值存儲(chǔ)過(guò)程可以返回整型狀態(tài)值，表示過(guò)程是否成功執(zhí)行，或者過(guò)程失敗的原因。如果存儲(chǔ)過(guò)程沒(méi)有顯式設(shè)置返回代碼的值，則SQL Server返回代碼為 0，表示成功執(zhí)行；若返回-1-99之間的整數(shù)，表示沒(méi)有成功執(zhí)行。也可以使用RETURN語(yǔ)句，用大于0或

21、小于-99的整數(shù)來(lái)定義自己的返回狀態(tài)值，以表示不同的執(zhí)行結(jié)果。2. 返回存儲(chǔ)過(guò)程的狀態(tài)10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值例10-5 創(chuàng)建存儲(chǔ)過(guò)程，輸入商品類別，返回各種商品名稱。在存儲(chǔ)過(guò)程中，用值15表示用戶沒(méi)有提供參數(shù)；值-l01表示沒(méi)有輸入商品類別；值0表示過(guò)程運(yùn)行沒(méi)有出錯(cuò)。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) RET

22、URN -101SELECT g.goods_name FROM goods_classification gc,goods gWHERE gc.classification_id =g.classification_id AND gc.classification_name=cl_nameRETURN 010.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值在執(zhí)行過(guò)程時(shí)，要正確接收返回的狀態(tài)值，必須使用以下語(yǔ)句；EXECUTE status_var=procedure_name(2)捕獲返回狀態(tài)值10.2.5 存儲(chǔ)過(guò)程參數(shù)與狀態(tài)值DECLARE return_status intEXEC return_stat

23、us=cl_goods 筆記本計(jì)算機(jī)IF return_status=15 SELECT 語(yǔ)法錯(cuò)誤ELSE IF return_status=-101 SELECT 沒(méi)有找到該商品類別執(zhí)行時(shí)，將對(duì)不同的輸入值返回不同的狀態(tài)值及處理結(jié)果。例10-5的存儲(chǔ)過(guò)程cl_goods執(zhí)行時(shí)使用以下語(yǔ)句：觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程。觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的，而存儲(chǔ)過(guò)程可以通過(guò)過(guò)程名字直接調(diào)用。當(dāng)對(duì)某一表進(jìn)行UPDATE、INSERT、DELETE操作時(shí)，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)的處理必須符合由這些SQL語(yǔ)句所定義的規(guī)則。觸發(fā)器的主要作用就是能

24、夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的參照完整性和數(shù)據(jù)的一致性。 10.3 觸發(fā)器概述10.4.1 創(chuàng)建觸發(fā)器10.4.2 刪除觸發(fā)器10.4.3 修改觸發(fā)器10.4 觸發(fā)器的創(chuàng)建與使用10.4 觸發(fā)器的創(chuàng)建與使用1使用SQL Server管理平臺(tái)創(chuàng)建觸發(fā)器 10.4.1 創(chuàng)建觸發(fā)器10.4.1 創(chuàng)建觸發(fā)器2. 使用CREATE TRIGGER語(yǔ)句創(chuàng)建觸發(fā)器CREATE TRIGGER語(yǔ)句的語(yǔ)法格式如下：CREATE TRIGGER trigger_name ON table_name | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , U

25、PDATE , DELETE AS sql_statement . n 10.4.1 創(chuàng)建觸發(fā)器例10-6 在employee表上創(chuàng)建一個(gè)DELETE類型的觸發(fā)器，該觸發(fā)器的名稱為tr_employee。(1)創(chuàng)建觸發(fā)器tr_employeeCREATE TRIGGER tr_employee ON employeeFOR DELETEAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工被刪除SELECT msgRETURN10.4.1 創(chuàng)建觸發(fā)器(2)執(zhí)行觸發(fā)器tr_employee觸發(fā)器不能通過(guò)名字來(lái)執(zhí)行，而是在相應(yīng)的SQL語(yǔ)句被執(zhí)

26、行時(shí)自動(dòng)觸發(fā)的。例如執(zhí)行以下DELETE語(yǔ)句：DELETE FROM employeeWHERE employee_name = 張三該語(yǔ)句要?jiǎng)h除員工姓名為“張三”記錄，由此激活了表employee 的DELETE類型的觸發(fā)器tr_employee，系統(tǒng)執(zhí)行tr_employee觸發(fā)器中AS之后的語(yǔ)句，并顯示以下信息：1個(gè)員工被刪除10.4.1 創(chuàng)建觸發(fā)器在觸發(fā)器的執(zhí)行過(guò)程中，SQL Server建立和管理兩個(gè)臨時(shí)的虛擬表：Deleted表和Inserted表。這兩個(gè)表包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。在執(zhí)行INSERT或UPDATE語(yǔ)句之后所有被添加或被更新的記錄都會(huì)存儲(chǔ)在In

27、serted表中。在執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，從觸發(fā)程序表中被刪除的行會(huì)發(fā)送到Deleted表。對(duì)于更新操作，SQL Server先將要進(jìn)行修改的記錄存儲(chǔ)到Deleted表中，然后再將修改后的數(shù)據(jù)復(fù)制到Inserted表以及觸發(fā)程序表。3. Deleted表和Inserted表10.4.1 創(chuàng)建觸發(fā)器例10-7 為表customer創(chuàng)建一個(gè)名為test_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時(shí)，激活該觸發(fā)器。創(chuàng)建test_tr觸發(fā)器：CREATE TRIGGER test_trON customer FOR INSERT,UPDATE,DELETEAS SELECT * FROM i

28、nserted SELECT * FROM deletedcustomer表執(zhí)行以下插入操作：INSERT INTO customer(customer_id,customer_name,telphone)VALUES(12346,張三,1234567)10.4.1 創(chuàng)建觸發(fā)器INSERT操作激活觸發(fā)器test_tr，輸出如圖10-19所示的表格。10.4.2 修改觸發(fā)器通過(guò)SQL Server管理平臺(tái)、系統(tǒng)存儲(chǔ)過(guò)程或Transact_SQL語(yǔ)句，可以修改觸發(fā)器的名字和正文。 1. 使用sp_rename系統(tǒng)存儲(chǔ)過(guò)程修改觸發(fā)器的名字：sp_rename oldname, newname10.4

29、 觸發(fā)器的創(chuàng)建與使用10.4.3 修改觸發(fā)器2使用SQL Server管理平臺(tái)修改觸發(fā)器的正文 修改觸發(fā)器的操作步驟如下：（1）打開(kāi)SQL Server管理平臺(tái)，展開(kāi)節(jié)點(diǎn)“對(duì)象資源管理器”“Sales”數(shù)據(jù)庫(kù)|“表”“customer”表|“觸發(fā)器”，選擇要?jiǎng)h除的觸發(fā)器（如例10-7創(chuàng)建的test_tr觸發(fā)器），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“修改”命令。（2）此時(shí)在右邊的編輯器窗口中出現(xiàn)觸發(fā)器的源代碼（將CREATE TRIGGER改為了ALTER TRIGGER），可以直接進(jìn)行修改。修改完后單擊工具欄中的“執(zhí)行”按鈕執(zhí)行該觸發(fā)器代碼，從而達(dá)到目的。 10.4.3 修改觸發(fā)器3. 使用

30、ALTER TRIGGER語(yǔ)句修改觸發(fā)器修改觸發(fā)器的語(yǔ)法如下：ALTER TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATE AS sql_statement .n 10.4.3 修改觸發(fā)器ALTER TRIGGER tr_employee ON employeeFOR INSERTAS DECLARE msg varchar(50) SELECT msg=STR(ROWCOUNT)+個(gè)員工數(shù)據(jù)被插入SELECT msgRETURN對(duì)emplo

31、yee表執(zhí)行以下插入語(yǔ)句：INSERT employee(employee_id,employee_name)VALUES (E016,王五)激活I(lǐng)NSERT觸發(fā)器tr_employee，顯示信息：1個(gè)員工數(shù)據(jù)被插入例如，將例10-6的觸發(fā)器tr_employee修改為INSERT操作后進(jìn)行。10.4 觸發(fā)器的創(chuàng)建與使用10.4.3 刪除觸發(fā)器1使用SQL Server管理平臺(tái)刪除觸發(fā)器 操作步驟如下：（1）打開(kāi)SQL Server管理平臺(tái)，展開(kāi)節(jié)點(diǎn)“對(duì)象資源管理器”“Sales”數(shù)據(jù)庫(kù)|“表”“customer”表|“觸發(fā)器”，選擇要?jiǎng)h除的觸發(fā)器（如例10-7創(chuàng)建的test_tr觸發(fā)器），單

32、擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“刪除”命令。（2）在彈出的“刪除對(duì)象”對(duì)話框中單擊“確定”按鈕即可刪除觸發(fā)器。 10.4.2 刪除觸發(fā)器2. 使用DROP TRIGGER刪除指定觸發(fā)器刪除觸發(fā)器語(yǔ)句的語(yǔ)法格式如下：DROP TRIGGER trigger_name , .n 例如，刪除例10-6的觸發(fā)器tr_employee：DROP TRIGGER tr_employee10.5.1 事務(wù)概述10.5.2 事務(wù)管理10.5 事務(wù)處理10.5 事務(wù)處理事務(wù)(Transaction)是SQL Server中的一個(gè)邏輯工作單元，該單元將被作為一個(gè)整體進(jìn)行處理。事務(wù)保證連續(xù)多個(gè)操作必須全部執(zhí)行成

33、功，否則必須立即回復(fù)到未執(zhí)行任何操作的狀態(tài)，即執(zhí)行事務(wù)的結(jié)果要不全部將數(shù)據(jù)所要執(zhí)行的操作完成，要不全部數(shù)據(jù)都不修改。10.5.1 事務(wù)概述10.5.1 事務(wù)概述例如，企業(yè)取消了倉(cāng)儲(chǔ)部，需要將“倉(cāng)儲(chǔ)部”從department表中刪除，而employee表中的部門編號(hào)與倉(cāng)儲(chǔ)部相對(duì)應(yīng)的員工也應(yīng)刪除。假設(shè)倉(cāng)儲(chǔ)部編號(hào)為D004，第一條DELETE語(yǔ)句修改department表為：DELETE FROM department WHERE department_id = D004第二條DELETE語(yǔ)句修改employee表為：DELETE FROM employee WHERE department_id

34、= D004因此，必須保證這兩條DELETE語(yǔ)句同時(shí)執(zhí)行，或都不執(zhí)行。這時(shí)可以使用數(shù)據(jù)庫(kù)中的事務(wù)(Transaction)技術(shù)來(lái)實(shí)現(xiàn)。1事務(wù)的由來(lái)2事務(wù)屬性由于事務(wù)作為一個(gè)邏輯工作單元，當(dāng)事務(wù)執(zhí)行遇到錯(cuò)誤時(shí)，將取消事務(wù)所做的修改。一個(gè)邏輯單元必須具有4個(gè)屬性：原子性(Atomicity)、一致性(Consistency)隔離性(Isolation)持久性(Durability)，這些屬性稱為ACID。10.5.1 事務(wù)概述3事務(wù)模式SQL Server以3種事務(wù)模式管理事務(wù)。(1) 自動(dòng)提交事務(wù)模式：每條單獨(dú)的語(yǔ)句都是一個(gè)事務(wù)。在此模式下，每條Transact-SQL語(yǔ)句在成功執(zhí)行完成后，都被

35、自動(dòng)提交，如果遇到錯(cuò)誤，則自動(dòng)回滾該語(yǔ)句。該模式為系統(tǒng)默認(rèn)的事務(wù)管理模式。(2) 顯式事務(wù)模式：該模式允許用戶定義事務(wù)的啟動(dòng)和結(jié)束。事務(wù)以BEGIN TRANSACTION語(yǔ)句顯式開(kāi)始，以COMMIT或ROLLBACK語(yǔ)句顯式結(jié)束。(3) 隱性事務(wù)模式：在當(dāng)前事務(wù)完成提交或回滾后，新事務(wù)自動(dòng)啟動(dòng)。隱性事務(wù)不需要使用BEGIN TRANSACTION語(yǔ)句標(biāo)識(shí)事務(wù)的開(kāi)始，但需要以COMMIT或ROLLBACK語(yǔ)句來(lái)提交或回滾事務(wù)。10.5.1 事務(wù)概述10.5 事務(wù)處理1啟動(dòng)和結(jié)束事務(wù)啟動(dòng)事務(wù)語(yǔ)句的語(yǔ)法格式如下：BEGIN TRANSACTION transaction_name | tran_n

36、ame_variable WITH MARK description 結(jié)束事務(wù)語(yǔ)句的語(yǔ)法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable 10.5.2 事務(wù)管理10.5.2 事務(wù)管理例10-8 建立一個(gè)顯式事務(wù)以顯示Sales數(shù)據(jù)庫(kù)的employee表的數(shù)據(jù)。BEGIN TRANSACTION SELECT * FROM employee COMMIT TRANSACTION本例創(chuàng)建的事務(wù)以BEGIN TRANSACTION語(yǔ)句開(kāi)始，以COMMIT TRANSACTION語(yǔ)句結(jié)束。10.5.2 事務(wù)管理DECLARE t

37、ransaction_name varchar(32)SELECT transaction_name=tran_deleteBEGIN TRANSACTION transaction_name DELETE FROM department WHERE department_id=D004 DELETE FROM employee WHERE department_id=D004COMMIT TRANSACTION tran_delete例10-9 建立一個(gè)顯式命名事務(wù)以刪除department表的“倉(cāng)儲(chǔ)部”記錄行。10.5.2 事務(wù)管理CREATE TABLE imp_tran( num ch

38、ar(2) NOT NULL, cname char(6) NOT NULL)GO SET IMPLICIT_TRANSACTIONS ON -啟動(dòng)隱性事務(wù)模式GO- 第一個(gè)事務(wù)由INSERT語(yǔ)句啟動(dòng)INSERT INTO imp_tran VALUES (01, Zhang)INSERT INTO imp_tran VALUES (02, Wang)COMMIT TRANSACTION -提交第一個(gè)隱性事務(wù)GO- 第二個(gè)隱式事務(wù)由SELECT語(yǔ)句啟動(dòng)SELECT COUNT(*) FROM imp_tranINSERT INTO imp_tran VALUES (03, Li)SELECT

39、* FROM imp_tranCOMMIT TRANSACTION -提交第二個(gè)隱性事務(wù)GOSET IMPLICIT_TRANSACTIONS OFF -關(guān)閉隱性事務(wù)模式GO例10-10 隱性事務(wù)處理過(guò)程。10.5.2 事務(wù)管理當(dāng)事務(wù)事務(wù)回滾使用ROLLBACK TRANSACTION語(yǔ)句實(shí)現(xiàn)，其語(yǔ)法格式如下：ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable 2事務(wù)回滾10.5.2 事務(wù)管理事務(wù)回滾到指定位置如果要讓事務(wù)回滾到指定位置，則需要在事務(wù)中設(shè)

40、定保存點(diǎn)(SavePoint)。所謂保存點(diǎn)是指定其所在位置之前的事務(wù)語(yǔ)句，不能回滾的語(yǔ)句即此語(yǔ)句前面的操作被視為有效。其語(yǔ)法格式如下：SAVE TRANSACTION savepoint_name | savepoint_variable10.5.2 事務(wù)管理例10-11 使用ROLLBACK TRANSACTION語(yǔ)句標(biāo)識(shí)事務(wù)結(jié)束。BEGIN TRANSACTION UPDATE goods SET stock_quantity=stock_quantity-5 WHERE goods_id=G00006 INSERT INTO sell_order(order_id1,goods_id,o

41、rder_num,order_date) VALUES(S00005,G00006,5,getdate()ROLLBACK TRANSACTION10.5.2 事務(wù)管理BEGIN TRANSACTION my_transaction_delete DELETE FROM department WHERE department_id=D005 SAVE TRANSACTION after_delete -設(shè)置保存點(diǎn) UPDATE employee SET department_id=D001 WHERE department_id=D005 IF (error=0 OR rowcount=0)

42、BEGIN ROLLBACK TRANSACTION after_delete - 如果出錯(cuò)回滾到保存點(diǎn)after_delete COMMIT TRANSACTION my_transaction_delete END ELSE COMMIT TRANSACTION my_transaction_deleteGO例10-12 刪除倉(cāng)儲(chǔ)部，再將倉(cāng)儲(chǔ)部的職工劃分到總經(jīng)理辦。10.5.2 事務(wù)管理CREATE TRIGGER trig_uptab ON goodsFOR UPDATEASSAVE TRANSACTION tran_uptabINSERT INTO newgoods SELECT *

43、FROM insertedIF (error0)BEGIN ROLLBACK TRANSACTION tran_uptabEND例10-13 定義為表goods觸發(fā)器trig_uptab，如果goods表更新數(shù)據(jù)，則把新數(shù)據(jù)復(fù)制到表newgoods中，若出錯(cuò)，則取消復(fù)制操作。10.5.2 事務(wù)管理和BEGINEND語(yǔ)句類似，BEGIN TRANSACTION和COMMIT TRANSACTION語(yǔ)句也可以進(jìn)行嵌套，即事務(wù)可以嵌套執(zhí)行。3事務(wù)嵌套10.5.2 事務(wù)管理CREATE TABLE employee_tran( num char(2) NOT NULL, cname char(6) N

44、OT NULL)GO BEGIN TRANSACTION Tran1 -TRANCOUNT為1 INSERT INTO employee_tran VALUES (01, Zhang) BEGIN TRANSACTION Tran2 -TRANCOUNT為2 INSERT INTO employee_tran VALUES (02, Wang) BEGIN TRANSACTION Tran3 -TRANCOUNT為3 PRINT TRANCOUNT INSERT INTO employee_tran VALUES (03, Li) COMMIT TRANSACTION Tran3 -TRANC

45、OUNT為2 PRINT TRANCOUNT COMMIT TRANSACTION Tran2 -TRANCOUNT為1 PRINT TRANCOUNTCOMMIT TRANSACTION Tran1 -TRANCOUNT為0PRINT TRANCOUNT例10-14 提交事務(wù)。運(yùn)行結(jié)果：321010.6.1 鎖模式10.6.2 隔離級(jí)別10.6.3 查看和終止鎖10.6.4 死鎖及其防止10.6 SQL Server的鎖機(jī)制10.6 SQL Server的鎖機(jī)制鎖(Lock)作為一種安全機(jī)制，用于控制多個(gè)用戶的并發(fā)操作，以防止用戶讀取下在由其他用戶更改的數(shù)據(jù)或者多個(gè)用戶同時(shí)修改同一數(shù)據(jù)，從而

46、確保事務(wù)完整性和數(shù)據(jù)庫(kù)一致性。10.6.1 鎖模式10.6.1 鎖模式當(dāng)對(duì)一個(gè)數(shù)據(jù)源加鎖后，此數(shù)據(jù)源就有了一定的訪問(wèn)限制，稱對(duì)此數(shù)據(jù)源進(jìn)行了“鎖定”。SQL Server有多種粒度鎖，允許一個(gè)事務(wù)鎖定不同類型的資源： 數(shù)據(jù)行(Row)：數(shù)據(jù)頁(yè)中的單行數(shù)據(jù)。 索引行(Key)：索引頁(yè)中的單行數(shù)據(jù)，即索引的鍵值。 頁(yè)(Page)：頁(yè)是SQL Server存取數(shù)據(jù)的基本單位，其大小為8KB。 擴(kuò)展盤區(qū)(Extent)：一個(gè)盤區(qū)由8個(gè)連續(xù)的頁(yè)組成。 表(Table)。 數(shù)據(jù)庫(kù)(Database)。 允許一個(gè)事務(wù)鎖定的資源類型10.6.1 鎖模式確定并發(fā)事務(wù)訪問(wèn)資源方式的鎖模式:(1) 共享鎖(Shar

47、ed Lock)。(2) 排它鎖(Exclusive Lock)。(3) 更新鎖(Update Lock)。從程序員的角度，鎖可以分為以下兩種類型： (1) 樂(lè)觀鎖(Optimistic Lock)。樂(lè)觀鎖假定在處理數(shù)據(jù)時(shí)，不需要在應(yīng)用程序的代碼中做任何事情就可以直接在記錄上加鎖，即完全依靠數(shù)據(jù)庫(kù)來(lái)管理鎖的工作。一般情況下，當(dāng)執(zhí)行事務(wù)處理時(shí)，SQL Server會(huì)自動(dòng)對(duì)事務(wù)處理范圍內(nèi)更新到的表做鎖定。(2) 悲觀鎖(Pessimistic Lock)。悲觀鎖需要程序員直接管理數(shù)據(jù)或?qū)ο笊系募渔i處理，并負(fù)責(zé)獲取、共享和放棄正在使用的數(shù)據(jù)上的任何鎖。10.6.1 鎖模式10.6 SQL Serve

48、r的鎖機(jī)制隔離(Isolation)是計(jì)算機(jī)安全技術(shù)中的概念，其本質(zhì)上是一種封鎖機(jī)制。它是指自動(dòng)數(shù)據(jù)處理系統(tǒng)中的用戶和資源的相關(guān)牽制關(guān)系，也就是用戶和進(jìn)程彼此分開(kāi)，且和操作系統(tǒng)的保護(hù)控制也分開(kāi)來(lái)。事務(wù)準(zhǔn)備接受不一致數(shù)據(jù)的級(jí)別稱為隔離級(jí)別(Isolation Level)。10.6.2 隔離級(jí)別10.6.2 隔離級(jí)別較低的隔離級(jí)別可以增加并發(fā)，但代價(jià)是降低數(shù)據(jù)的正確性。相反，較高的隔離級(jí)別可以確保數(shù)據(jù)的正確性，但可能對(duì)并發(fā)產(chǎn)生負(fù)面影響。應(yīng)用程序要求的隔離級(jí)別確定了SQL Server使用的鎖定行為。隔離級(jí)別是一個(gè)事務(wù)必須與其他事務(wù)進(jìn)行隔離的程度。10.6.2 隔離級(jí)別(1) 提交讀(READ C

49、OMMITTED)。它是SQL Server的默認(rèn)級(jí)別。在此隔離級(jí)別下，SELECT語(yǔ)句不會(huì)也不能返回尚未提交(Committed)的數(shù)據(jù)(即臟數(shù)據(jù))。(2) 未提交讀(READ UNCOMMITTED)。與提交讀隔離級(jí)別相反，它允許讀取臟數(shù)據(jù)，即已經(jīng)被其他用戶修改但尚未提交的數(shù)據(jù)。它是最低的事務(wù)隔離級(jí)別，僅可保證不讀取物理?yè)p壞的數(shù)據(jù)。(3) 可重復(fù)讀(REPEATABLE READ)。在此隔離級(jí)別下，用SELECT語(yǔ)句讀取的數(shù)據(jù)在整個(gè)語(yǔ)句執(zhí)行過(guò)程中不會(huì)被更改。此選項(xiàng)會(huì)影響系統(tǒng)的效能，非必要情況最好不用此隔離級(jí)別。(4) 可串行讀(SERIALIZABLE)。將共享鎖保持到事務(wù)完成，而不是不管事務(wù)是否完成都在不再需要所需的表或數(shù)據(jù)頁(yè)時(shí)就立即釋放共享鎖。它是最高的事務(wù)隔離級(jí)別，事務(wù)之間完全隔離。SQL Server支持以下4種隔離級(jí)別：10.6.2 隔離級(jí)別使用SET TRANSACTION ISOLATION LEVEL語(yǔ)句設(shè)置會(huì)話的隔離級(jí)別 其語(yǔ)法格式如下：SET TRANSACTION ISOLATI