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

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

《數(shù)據(jù)庫(kù)技術(shù)與應(yīng)用-SQLServer2008》10.1存儲(chǔ)過(guò)程

概述10.2存儲(chǔ)過(guò)程

的創(chuàng)建與

使用10.3觸發(fā)器概

述10.4觸發(fā)器的創(chuàng)建與使

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

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

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

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

10.4觸發(fā)器的創(chuàng)建與使用 10.1存儲(chǔ)過(guò)程概述10.2存儲(chǔ)過(guò)程的創(chuàng)建與使用10.3觸發(fā)器概述10.4觸發(fā)器的創(chuàng)建與使用10.5事務(wù)處理10.6鎖機(jī)制2．使用SQLServer2008管理平臺(tái)修改觸發(fā)器的正文修改觸發(fā)器的操作步驟如下：（1）打開SQLServer2008管理平臺(tái)，展開節(jié)點(diǎn)“對(duì)象資源管理器”→“數(shù)據(jù)庫(kù)服務(wù)器”→“數(shù)據(jù)庫(kù)”→“Sales”數(shù)據(jù)庫(kù)→“表”→“customer”表→“觸發(fā)器”，選擇要?jiǎng)h除的觸發(fā)器（如例10-7創(chuàng)建的test_tr觸發(fā)器），單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“修改”命令。（2）此時(shí)在右邊的編輯器窗口中出現(xiàn)觸發(fā)器的源代碼（將CREATETRIGGER改為了ALTERTRIGGER），如圖10-13所示，可以直接進(jìn)行修改。修改完后單擊工具欄中的“執(zhí)行”按鈕執(zhí)行該觸發(fā)器代碼，從而達(dá)到目的。

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

10.4觸發(fā)器的創(chuàng)建與使用 10.1存儲(chǔ)過(guò)程概述10.2存儲(chǔ)過(guò)程的創(chuàng)建與使用10.3觸發(fā)器概述10.4觸發(fā)器的創(chuàng)建與使用10.5事務(wù)處理10.6鎖機(jī)制3．使用ALTERTRIGGER語(yǔ)句修改觸發(fā)器

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

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