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

存儲(chǔ)過(guò)程與觸發(fā)器ppt1第一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日本章要點(diǎn)存儲(chǔ)過(guò)程概述創(chuàng)建存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的參數(shù)存儲(chǔ)過(guò)程的返回值查看和修改存儲(chǔ)過(guò)程刪除存儲(chǔ)過(guò)程觸發(fā)器概述inserted和deleted表事務(wù)的概念及應(yīng)用創(chuàng)建觸發(fā)器修改和重命名觸發(fā)器刪除觸發(fā)器2第二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1存儲(chǔ)過(guò)程在創(chuàng)建SQLServer數(shù)據(jù)庫(kù)應(yīng)用程序時(shí)，Transact-SQL語(yǔ)言是應(yīng)用程序和SQLServer數(shù)據(jù)庫(kù)之間的主要編程接口?？捎萌缦聝煞N方法存儲(chǔ)和執(zhí)行Transact-SQL語(yǔ)句。(1)將Transact-SQL程序保存在本地，創(chuàng)建向SQLServer發(fā)送命令并處理結(jié)果的應(yīng)用程序。(2)可以將Transact-SQL程序保存在SQLServer中，即存儲(chǔ)過(guò)程，在本地創(chuàng)建執(zhí)行存儲(chǔ)過(guò)程及處理結(jié)果的應(yīng)用程序。任何一組Transact-SQL語(yǔ)句構(gòu)成的代碼塊，都可以作為存儲(chǔ)過(guò)程保存起來(lái)。它在服務(wù)器端對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行處理，并將結(jié)果返回到客戶端。這樣就避免了從客戶端多次連接并訪問(wèn)數(shù)據(jù)庫(kù)的操作，減少了網(wǎng)絡(luò)上的傳輸量，同時(shí)也提高了客戶端的工作效率（因?yàn)檫@些操作都是在服務(wù)器端完成的）。3第三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.1存儲(chǔ)過(guò)程概述存儲(chǔ)過(guò)程是集中存儲(chǔ)在SQLServer中的SQL語(yǔ)句和流程控制語(yǔ)句的預(yù)編譯集合，用以實(shí)現(xiàn)某種任務(wù)（如查詢）。這些語(yǔ)句在一個(gè)名稱下存儲(chǔ)并作為一個(gè)單元進(jìn)行處理。4第四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用存儲(chǔ)過(guò)程的優(yōu)勢(shì)可以減少客戶端代碼的重復(fù)。只需創(chuàng)建存儲(chǔ)過(guò)程一次并將其存儲(chǔ)在數(shù)據(jù)庫(kù)中，以后即可在客戶端程序中多次調(diào)用該存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程可由在數(shù)據(jù)庫(kù)編程方面有專長(zhǎng)的人員創(chuàng)建，并可獨(dú)立于程序源代碼而單獨(dú)修改。允許更快地執(zhí)行。如果某操作需要大量的Transact-SQL代碼或需要重復(fù)執(zhí)行，使用存儲(chǔ)過(guò)程將比在客戶端執(zhí)行Transact-SQL批代碼的執(zhí)行速度要快。存儲(chǔ)過(guò)程在服務(wù)器端經(jīng)過(guò)預(yù)編譯，生成查詢計(jì)劃，可以直接執(zhí)行，而在客戶端每次運(yùn)行Transact-SQL語(yǔ)句時(shí)，都要從客戶端重復(fù)發(fā)送，并且在SQLServer每次執(zhí)行這些語(yǔ)句時(shí)，都要對(duì)其進(jìn)行編譯和優(yōu)化。因此，存儲(chǔ)過(guò)程執(zhí)行速度更快。5第五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用存儲(chǔ)過(guò)程的優(yōu)勢(shì)減少網(wǎng)絡(luò)流量。例如，一個(gè)需要數(shù)百行Transact-SQL代碼的操作由一條執(zhí)行過(guò)程代碼的單獨(dú)語(yǔ)句就可實(shí)現(xiàn)，而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼?？梢院?jiǎn)化數(shù)據(jù)庫(kù)管理。例如，要修改某種查詢，如果查詢重復(fù)存放在客戶機(jī)上，則要在所有客戶機(jī)上修改查詢，而使用存儲(chǔ)過(guò)程可以集中修改?？勺鳛榘踩珯C(jī)制使用。例如，即使對(duì)于沒(méi)有直接執(zhí)行存儲(chǔ)過(guò)程中語(yǔ)句的權(quán)限的用戶，也可授予他們執(zhí)行該存儲(chǔ)過(guò)程的權(quán)限。6第六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日存儲(chǔ)過(guò)程的分類系統(tǒng)存儲(chǔ)過(guò)程:SQLServer內(nèi)置的存儲(chǔ)過(guò)程，存儲(chǔ)在master庫(kù)中，主要用途是執(zhí)行SQLServer的某些管理功能、顯示有關(guān)數(shù)據(jù)庫(kù)和用戶的信息。系統(tǒng)存儲(chǔ)過(guò)程名以SP_開(kāi)頭，可以在任何數(shù)據(jù)庫(kù)中執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程。用戶存儲(chǔ)過(guò)程：用戶自行創(chuàng)建并存儲(chǔ)在用戶數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程。臨時(shí)存儲(chǔ)過(guò)程：分為局部臨時(shí)存儲(chǔ)過(guò)程和全局臨時(shí)存儲(chǔ)過(guò)程。7第七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日存儲(chǔ)過(guò)程的分類局部臨時(shí)存儲(chǔ)過(guò)程名稱以#開(kāi)頭，存放在tempdb數(shù)據(jù)庫(kù)中，只由創(chuàng)建并連接的用戶使用，當(dāng)該用戶斷開(kāi)連接時(shí)將自動(dòng)刪除局部臨時(shí)存儲(chǔ)過(guò)程。全局臨時(shí)存儲(chǔ)過(guò)程名稱以##開(kāi)頭，存放在tempdb數(shù)據(jù)庫(kù)中，允許所有連接的用戶使用，在所有用戶斷開(kāi)連接時(shí)自動(dòng)被刪除。遠(yuǎn)程存儲(chǔ)過(guò)程：位于遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程。擴(kuò)展存儲(chǔ)過(guò)程：利用外部語(yǔ)言（如C）編寫的存儲(chǔ)過(guò)程，以彌補(bǔ)SQLServer的不足之處，擴(kuò)展新的功能8第八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.2創(chuàng)建存儲(chǔ)過(guò)程使用CREATEPROCEDURE語(yǔ)句使用企業(yè)管理器中的菜單命令9第九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日CREATEPROCEDURE語(yǔ)句CREATEPROC[EDURE]存儲(chǔ)過(guò)程名[;編號(hào)][{@參數(shù)數(shù)據(jù)類型}[VARYING][=默認(rèn)值][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL語(yǔ)句[...n]10第十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明存儲(chǔ)過(guò)程名必須符合標(biāo)識(shí)符的命名規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須是惟一的。要?jiǎng)?chuàng)建局部臨時(shí)存儲(chǔ)過(guò)程，可以在存儲(chǔ)過(guò)程名前面加一個(gè)#號(hào)，要?jiǎng)?chuàng)建全局臨時(shí)過(guò)程，可以在存儲(chǔ)過(guò)程名前面加兩個(gè)#號(hào)。完整的名稱（包括#或##）不能超過(guò)128個(gè)字符。編號(hào)：可選整數(shù)，用來(lái)對(duì)同名的存儲(chǔ)過(guò)程分組，以便用一條DROPPROCEDURE語(yǔ)句即可將同組的存儲(chǔ)過(guò)程一起刪除。11第十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明@參數(shù)：過(guò)程中的參數(shù)。在CREATEPROCEDURE語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。用戶必須在執(zhí)行存儲(chǔ)過(guò)程時(shí)提供每個(gè)所聲明參數(shù)的值（除非定義了該參數(shù)的默認(rèn)值）。參數(shù)名稱前需要使用@符號(hào)。參數(shù)名稱必須符合標(biāo)識(shí)符的命名規(guī)則。每個(gè)過(guò)程的參數(shù)僅用于該過(guò)程本身。在其他過(guò)程中可以使用相同的參數(shù)名稱。默認(rèn)情況下，參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫(kù)對(duì)象的名稱。12第十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明數(shù)據(jù)類型：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型（包括text、ntext和image）均可以用作存儲(chǔ)過(guò)程的參數(shù)。不過(guò)，cursor數(shù)據(jù)類型只能用于OUTPUT參數(shù)。如果指定的數(shù)據(jù)類型為cursor，也必須同時(shí)指定VARYING和OUTPUT關(guān)鍵字。VARYING：指定作為輸出參數(shù)支持的結(jié)果集（由存儲(chǔ)過(guò)程動(dòng)態(tài)構(gòu)造，內(nèi)容可以變化）。僅適用于游標(biāo)參數(shù)。13第十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明默認(rèn)值：參數(shù)的默認(rèn)值。如果定義了默認(rèn)值，不必指定該參數(shù)的值即可執(zhí)行存儲(chǔ)過(guò)程。默認(rèn)值必須是常量或NULL。如果要在存儲(chǔ)過(guò)程中對(duì)該參數(shù)使用LIKE關(guān)鍵字，那么默認(rèn)值中可以包含通配符（%、_、[]和[^]）。OUTPUT：表明參數(shù)是返回參數(shù)。該選項(xiàng)的值可以返回給EXEC[UTE]。使用OUTPUT參數(shù)可將信息返回給調(diào)用過(guò)程。text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標(biāo)占位符。14第十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明RECOMPILE：表明不保存該存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃，該存儲(chǔ)過(guò)程將在運(yùn)行時(shí)重新編譯。ENCRYPTION：指定SQLServer對(duì)syscomments表中包含本CREATEPROCEDURE語(yǔ)句文本的條目進(jìn)行加密AS：用于指定該存儲(chǔ)過(guò)程要執(zhí)行的操作。SQL語(yǔ)句：存儲(chǔ)過(guò)程中要包含的Transact-SQL語(yǔ)句。15第十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日存儲(chǔ)過(guò)程定義1.無(wú)參數(shù)存儲(chǔ)過(guò)程2.有參數(shù)存儲(chǔ)過(guò)程（輸入?yún)?shù)、輸出參數(shù)output）16第十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例題1：建立一個(gè)查詢存儲(chǔ)過(guò)程，實(shí)現(xiàn)查詢成績(jī)表中的所有及格成績(jī)。（無(wú)參數(shù)存儲(chǔ)過(guò)程）createprocseleprocas

select*from成績(jī)表where成績(jī)>=60執(zhí)行存儲(chǔ)過(guò)程：execseleproc結(jié)果如圖：17第十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例2：創(chuàng)建一個(gè)向成績(jī)表添加記錄的存儲(chǔ)過(guò)程。（有參數(shù)存儲(chǔ)過(guò)程）createprocinsertproc@snochar(10),@cnochar(3),@gradefloatasinsertinto成績(jī)表values(@sno,@cno,@grade)執(zhí)行存儲(chǔ)過(guò)程：execinsertproc'0009','03',9818第十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例3：創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，要求根據(jù)輸入的課程號(hào)統(tǒng)計(jì)該課程平均分。createproccnoavgscore@cnochar(2),@avgscorefloatoutputasdeclare@cnonchar(2)

set@cnon=''

select@cnon=課程號(hào)from成績(jī)表

where課程號(hào)=@cno

if@cnon<>''

begin

select@avgscore=avg(成績(jī))from成績(jī)表

where課程號(hào)=@cno

end

else

print'此課程號(hào)不存在'

go19第十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日運(yùn)行該存儲(chǔ)過(guò)程：declare@ifloatexeccnoavgscore'02',@ioutputprint@i20第二十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日說(shuō)明：Withencryption子句的作用是將存儲(chǔ)過(guò)程的原代碼加密；Withrecompile子句的作用是對(duì)該存儲(chǔ)過(guò)程重編譯。21第二十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例4創(chuàng)建存儲(chǔ)過(guò)程“增加成績(jī)”，將表“成績(jī)表”中所有學(xué)生的成績(jī)?cè)黾?0%。USE學(xué)生管理GOCREATEPROCEDURE增加成績(jī)ASUPDATE成績(jī)表SET成績(jī)=成績(jī)*1.1GO執(zhí)行存儲(chǔ)過(guò)程：exec增加成績(jī)

22第二十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例5（細(xì)節(jié)考慮）在創(chuàng)建一個(gè)存儲(chǔ)過(guò)程時(shí)，如果已經(jīng)存在同名的存儲(chǔ)過(guò)程，則不允許創(chuàng)建新的存儲(chǔ)過(guò)程?？梢詫⒁陨洗a改寫為：USE學(xué)生管理--如果存在名稱為“增加成績(jī)”的存儲(chǔ)過(guò)程，則將其刪除IFEXISTS(SELECTnameFROM

sysobjects

WHEREname='增加成績(jī)'ANDtype='P')DROPPROCEDURE增加成績(jī)GOCREATEPROCEDURE增加成績(jī)ASUPDATE成績(jī)表SET成績(jī)=成績(jī)*1.1GO23第二十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程在企業(yè)管理器中，展開(kāi)要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù),用鼠標(biāo)右擊“存儲(chǔ)過(guò)程”，在彈出菜單中單擊“新建存儲(chǔ)過(guò)程”命令，打開(kāi)新建存儲(chǔ)過(guò)程對(duì)話框，如下圖：24第二十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程在該對(duì)話框的文本框中有以下的默認(rèn)語(yǔ)句：CREATEPROCEDURE[OWNER].[PROCEDURENAME]AS這是CREATEPROCEDURE語(yǔ)句的開(kāi)始部分，存儲(chǔ)過(guò)程的具體文本需要用戶自己輸入。例如，將以上默認(rèn)語(yǔ)句修改成：

CREATEPROCEDURE增加成績(jī)ASUPDATE成績(jī)表SET成績(jī)=成績(jī)*1.1GO25第二十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.3執(zhí)行存儲(chǔ)過(guò)程[[EXEC[UTE]]{[@返回狀態(tài)=]{存儲(chǔ)過(guò)程名|@存儲(chǔ)過(guò)程名變量}}[[@參數(shù)名稱=]{值|@變量[OUTPUT]|[DEFAULT]}]

[,...n][WITHRECOMPILE]26第二十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明返回狀態(tài)：是一個(gè)可選的整型變量，保存存儲(chǔ)過(guò)程的返回狀態(tài)。這個(gè)變量在用于EXECUTE語(yǔ)句前，必須在批處理、存儲(chǔ)過(guò)程或函數(shù)中聲明過(guò)。存儲(chǔ)過(guò)程名：要調(diào)用的存儲(chǔ)過(guò)程的名稱。@存儲(chǔ)過(guò)程名變量：局部變量名，代表存儲(chǔ)過(guò)程的名稱。@參數(shù)名稱：存儲(chǔ)過(guò)程的參數(shù)，在CREATEPROCEDURE語(yǔ)句中定義。參數(shù)名稱前必須加上符號(hào)@。在使用格式“@參數(shù)=值”時(shí)，參數(shù)名稱和常量不一定按照CREATEPROCEDURE語(yǔ)句中定義的順序出現(xiàn)。但是，如果有一個(gè)參數(shù)使用“@參數(shù)=值”格式，則其他所有參數(shù)都必須使用這種格式。27第二十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明值：過(guò)程中參數(shù)的值。如果沒(méi)有指定參數(shù)名稱，參數(shù)值必須以CREATEPROCEDURE語(yǔ)句中定義的順序給出。如果在CREATEPROCEDURE語(yǔ)句中定義了默認(rèn)值，用戶執(zhí)行該存儲(chǔ)過(guò)程時(shí)可以不必指定對(duì)應(yīng)的參數(shù)。如果該存儲(chǔ)過(guò)程使用了帶LIKE關(guān)鍵字的參數(shù)名稱，則默認(rèn)值必須是常量，并且可以包含%、_、[]及[^]通配符。默認(rèn)值也可以為NULL。通常，定義存儲(chǔ)過(guò)程時(shí)會(huì)指定當(dāng)參數(shù)值為NULL時(shí)應(yīng)該執(zhí)行的操作。@變量：是用來(lái)保存參數(shù)或者返回參數(shù)的變量。28第二十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明OUTPUT：指定存儲(chǔ)過(guò)程必須返回一個(gè)參數(shù)。該存儲(chǔ)過(guò)程的匹配參數(shù)也必須由關(guān)鍵字OUTPUT創(chuàng)建。使用游標(biāo)變量作參數(shù)時(shí)使用該關(guān)鍵字。DEFAULT：根據(jù)存儲(chǔ)過(guò)程的定義，提供參數(shù)的默認(rèn)值。當(dāng)過(guò)程需要的參數(shù)值沒(méi)有事先定義好的默認(rèn)值，或缺少參數(shù)，或指定了DEFAULT關(guān)鍵字時(shí)，就會(huì)出錯(cuò)。WITHRECOMPILE：強(qiáng)制編譯新的計(jì)劃。如果所提供的參數(shù)為非典型參數(shù)或者數(shù)據(jù)有很大的改變，使用該選項(xiàng)。在以后的程序執(zhí)行中使用更改過(guò)的計(jì)劃。該選項(xiàng)不能用于擴(kuò)展存儲(chǔ)過(guò)程。建議盡量少使用該選項(xiàng)，因?yàn)樗妮^多系統(tǒng)資源。29第二十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.4存儲(chǔ)過(guò)程的參數(shù)為了提高存儲(chǔ)過(guò)程的靈活性，SQLServer2000支持在存儲(chǔ)過(guò)程中使用參數(shù)。存儲(chǔ)過(guò)程的參數(shù)分為輸入?yún)?shù)和輸出參數(shù)兩種類型，輸入?yún)?shù)用于向存儲(chǔ)過(guò)程中帶入數(shù)據(jù)，而輸出參數(shù)則能將存儲(chǔ)過(guò)程中的數(shù)據(jù)返回到調(diào)用程序。在定義存儲(chǔ)過(guò)程時(shí),可以同時(shí)指定參數(shù),格式如下：

@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][OUTPUT][,...n]如果參數(shù)后面使用OUTPUT關(guān)鍵字，則表明它是輸出參數(shù)。30第三十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例6創(chuàng)建存儲(chǔ)過(guò)程add_proc，用于計(jì)算兩個(gè)參數(shù)之和并將其輸出。

CREATEPROCEDUREadd_proc@num1INT=0,@num2INT=0ASDECLARE@num3INTSET@num3=@num1+@num2PRINT@num3該存儲(chǔ)過(guò)程定義了兩個(gè)參數(shù)@num1和@num2，它們都是輸入?yún)?shù),參數(shù)類型為INT,默認(rèn)值為0。31第三十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日?qǐng)?zhí)行例6不帶參數(shù)執(zhí)行：EXECadd_proc運(yùn)行結(jié)果為：

0帶參數(shù)執(zhí)行：EXECadd_proc13,25

運(yùn)行結(jié)果為：38有時(shí)需要將存儲(chǔ)過(guò)程中的計(jì)算結(jié)果返回到調(diào)用程序中，以便進(jìn)行進(jìn)一步的處理，此時(shí)就需要在存儲(chǔ)過(guò)程中使用輸出參數(shù)。

32第三十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7創(chuàng)建存儲(chǔ)過(guò)程add_proc1，用于計(jì)算兩個(gè)參數(shù)之和，并使用輸出參數(shù)返回結(jié)果。

CREATEPROCEDUREadd_proc1@num1INT=0,@num2INT=0,@num3INTOUTPUT--@num3為輸出參數(shù)ASSET@num3=@num1+@num233第三十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日?qǐng)?zhí)行例7DECLARE@numASINTEXECadd_proc112,23,@numOUTPUTPRINT@num運(yùn)行結(jié)果為：3834第三十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例題8：編輯一個(gè)可自由運(yùn)算的存儲(chǔ)過(guò)程createproccalculate@num1asfloat,@num2float,@schar(2),@num3floatoutputasset@num3=case@swhen'+'then@num1+@num2when'-'then@num1-@num2when'*'then@num1*@num2when'/'then@num1/@num2end35第三十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日運(yùn)行該存儲(chǔ)過(guò)程declare@num3floatexeccalculate2,4,'/',@num3outputselect@num336第三十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.6查看和修改存儲(chǔ)過(guò)程使用企業(yè)管理器查看和修改存儲(chǔ)過(guò)程使用查詢分析器查看和修改存儲(chǔ)過(guò)程使用企業(yè)管理器重命名存儲(chǔ)過(guò)程使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程使用sp_rename重命名存儲(chǔ)過(guò)程37第三十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日1.使用企業(yè)管理器查看和修改存儲(chǔ)過(guò)程在企業(yè)管理器中展開(kāi)指定的數(shù)據(jù)庫(kù)，選中“存儲(chǔ)過(guò)程”項(xiàng)，可以查看指定數(shù)據(jù)庫(kù)中定義的所有存儲(chǔ)過(guò)程。38第三十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日1.使用企業(yè)管理器查看和修改存儲(chǔ)過(guò)程雙擊指定的存儲(chǔ)過(guò)程，可以打開(kāi)存儲(chǔ)過(guò)程屬性對(duì)話框，查看存儲(chǔ)過(guò)程的定義情況。用戶可以在存儲(chǔ)過(guò)程屬性對(duì)話框中修改存儲(chǔ)過(guò)程的代碼。

39第三十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日2.使用查詢分析器查看和修改存儲(chǔ)過(guò)程右鍵單擊要編輯的存儲(chǔ)過(guò)程，在彈出菜單中選擇“編輯”，可以在右鍵的窗格中顯示指定存儲(chǔ)過(guò)程的代碼。40第四十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日3.使用企業(yè)管理器重命名存儲(chǔ)過(guò)程在企業(yè)管理器，展開(kāi)“數(shù)據(jù)庫(kù)”文件夾，選擇存儲(chǔ)過(guò)程所在的數(shù)據(jù)庫(kù)，單擊“存儲(chǔ)過(guò)程”文件夾。在右側(cè)的窗口中，列出了選擇數(shù)據(jù)庫(kù)的所有存儲(chǔ)過(guò)程。右擊要重命名的存儲(chǔ)過(guò)程，選擇“重命名”菜單項(xiàng)，就可以在當(dāng)前位置上修改存儲(chǔ)過(guò)程的名字。41第四十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日4.使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程ALTERPROC[EDURE]存儲(chǔ)過(guò)程名[;編號(hào)][{@參數(shù)名數(shù)據(jù)類型}[VARYING][=默認(rèn)值][OUTPUT]][,...n]WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASSQL語(yǔ)句[...n]各參數(shù)含義與CREATEPROCEDURE語(yǔ)句相同42第四十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例9使用ALTERPROCEDURE語(yǔ)句修改存儲(chǔ)過(guò)程“增加成績(jī)”，對(duì)其進(jìn)行加密處理。USE學(xué)生管理GOALTERPROCEDURE增加成績(jī)WITHENCRYPTIONASUPDATE成績(jī)表SET成績(jī)=成績(jī)+10指定以上語(yǔ)句后，在企業(yè)管理器中查看“增加成績(jī)”存儲(chǔ)過(guò)程，將彈出一個(gè)對(duì)話框，提示用戶存儲(chǔ)過(guò)程已經(jīng)加密，不能夠查看。43第四十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日5.使用sp_rename重命名存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程sp_rename的功能是更改當(dāng)前數(shù)據(jù)庫(kù)中用戶創(chuàng)建對(duì)象（如表、列或用戶定義數(shù)據(jù)類型）的名稱。使用sp_rename重命名存儲(chǔ)過(guò)程的語(yǔ)法結(jié)構(gòu)如下：

sp_rename[@objname=]'對(duì)象名',[@newname=]'新對(duì)象名'參數(shù)說(shuō)明：[@objname=]'對(duì)象名'：指定存儲(chǔ)過(guò)程的當(dāng)前名稱。[@newname=]'新對(duì)象名'：指定存儲(chǔ)過(guò)程的新名稱。

44第四十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例10將存儲(chǔ)過(guò)程add_proc重命名為add_proc2，則可以使用以下命令：

EXECsp_rename'add_proc','add_proc2'運(yùn)行結(jié)果為：

注意:更改對(duì)象名的任一部分都可能破壞腳本和存儲(chǔ)過(guò)程。object已重命名為'add_proc2'。45第四十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.1.7刪除存儲(chǔ)過(guò)程使用企業(yè)管理器使用DROPPROCEDURE語(yǔ)句46第四十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用企業(yè)管理器在企業(yè)管理器中，右擊要?jiǎng)h除的存儲(chǔ)過(guò)程，選擇“刪除”命令，并確認(rèn)刪除。47第四十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用DROPPROCEDURE語(yǔ)句DROPPROCEDURE{存儲(chǔ)過(guò)程名}[,...n]48第四十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例11刪除存儲(chǔ)過(guò)程add_proc，可以使用以下命令：

DROPPROCEDUREadd_proc49第四十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例12刪除一組存儲(chǔ)過(guò)程設(shè)某“職工”數(shù)據(jù)庫(kù)中有一個(gè)“職工工資”表，該表包含有“基本工資”、“獎(jiǎng)金”和“實(shí)發(fā)工資”等列。創(chuàng)建一組存儲(chǔ)過(guò)程，求“職工工資”表的平均基本工資、平均獎(jiǎng)金和平均實(shí)發(fā)工資。USE職工GOCREATEPROCAveSalary;1ASSELECTAVG(基本工資)FROM職工工資GOCREATEPROCAveSalary;2ASSELECTAVG(獎(jiǎng)金)FROM職工工資GOCREATEPROCAveSalary;3ASSELECTAVG(實(shí)發(fā)工資)FROM職工工資GO50第五十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例13刪除一組存儲(chǔ)過(guò)程使用以下語(yǔ)句執(zhí)行以上存儲(chǔ)過(guò)程。EXECAveSalary;1EXECAveSalary;2EXECAveSalary;3要?jiǎng)h除以上創(chuàng)建的三個(gè)存儲(chǔ)過(guò)程，可以使用語(yǔ)句：

DROPPROCEDUREAveSalary注意，不能寫成：DROPPROCEDUREAveSalary;1DROPPROCEDUREAveSalary;2DROPPROCEDUREAveSalary;351第五十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.2觸發(fā)器觸發(fā)器概述inserted和deleted表事務(wù)的概念及應(yīng)用創(chuàng)建觸發(fā)器修改和重命名觸發(fā)器刪除觸發(fā)器52第五十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.2.1觸發(fā)器的基本概念觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，與普通存儲(chǔ)過(guò)程的區(qū)別：觸發(fā)器的執(zhí)行是由事件觸發(fā)的，而普通存儲(chǔ)過(guò)程是由命令調(diào)用執(zhí)行的。使用觸發(fā)器有助于強(qiáng)制保持?jǐn)?shù)據(jù)庫(kù)的數(shù)據(jù)完整性。例如，在觸發(fā)器中可以完成如下功能：不允許刪除或更新特定的記錄。不允許插入不符合邏輯關(guān)系的記錄。在刪除一條記錄的同時(shí)刪除其他表中與其相關(guān)的記錄。在修改一條記錄的同時(shí)修改其他表中與其相關(guān)的記錄。53第五十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日SQLServer2000提供了兩種觸發(fā)器INSTEADOF觸發(fā)器(前置觸發(fā)器)：INSTEADOF觸發(fā)器在指定的操作（INSERT、UPDATE或DELETE語(yǔ)句）之前被執(zhí)行，它的功能是不執(zhí)行指定的操作，而是執(zhí)行INSTEADOF觸發(fā)器中定義的操作?？梢栽诒砗鸵晥D上定義INSTEADOF觸發(fā)器。AFTER/FOR觸發(fā)器（后置觸發(fā)器）。在執(zhí)行了INSERT、UPDATE或DELETE語(yǔ)句操作之后執(zhí)行AFTER觸發(fā)器。AFTER觸發(fā)器只能在表上指定。54第五十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日觸發(fā)器的主要優(yōu)點(diǎn)觸發(fā)器是自動(dòng)執(zhí)行的，不需要管理員手動(dòng)維護(hù)數(shù)據(jù)庫(kù)的數(shù)據(jù)完整性。觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)中的相關(guān)表進(jìn)行級(jí)聯(lián)更改。例如，可以在表“院系”中定義觸發(fā)器，當(dāng)用戶刪除表“院系”中的記錄時(shí)，觸發(fā)器將刪除表“學(xué)生”中對(duì)應(yīng)院系的記錄。觸發(fā)器可以限制向表中插入無(wú)效的數(shù)據(jù)，這一點(diǎn)與CHECK約束的功能相似。但在CHECK約束中不能使用到其他表中的字段，而在觸發(fā)器中則沒(méi)有此限制。例如，可以在表“學(xué)生”中定義觸發(fā)器，限制插入的記錄其“院系編號(hào)”字段值必須在表“院系”中存在對(duì)應(yīng)的記錄。55第五十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.2.2inserted和deleted表deleted表用于存儲(chǔ)DELETE和UPDATE語(yǔ)句所影響的行的復(fù)本。在執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。deleted表和觸發(fā)器表通常沒(méi)有相同的行。inserted表用于存儲(chǔ)INSERT和UPDATE語(yǔ)句所影響的行的副本。在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到inserted表和觸發(fā)器表中。inserted表中的行是觸發(fā)器表中新行的副本。56第五十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日inserted和deleted表的變化在設(shè)置觸發(fā)器條件時(shí)，應(yīng)當(dāng)為引發(fā)觸發(fā)器的操作恰當(dāng)使用inserted和deleted表。通常在插入數(shù)據(jù)時(shí)，可以從inserted表中讀取新插入的值，此時(shí)deleted表不會(huì)發(fā)生變化。在刪除數(shù)據(jù)時(shí)，可以從deleted表中讀取已經(jīng)刪除或修改的值，而inserted表不會(huì)發(fā)生變化。在更新數(shù)據(jù)時(shí)，inserted表和deleted表都發(fā)生變化?？梢詮膁eleted表中讀取原有的值，從inserted表中讀取修改后的值。57第五十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.2.3事務(wù)的概念及應(yīng)用在觸發(fā)器中經(jīng)常會(huì)取消用戶先前進(jìn)行的操作，例如不允許插入不符合條件的數(shù)據(jù)。SQLServer提供了一種叫做事務(wù)的機(jī)制，它可以保證指定的對(duì)數(shù)據(jù)庫(kù)的一系列操作作為一個(gè)整體被執(zhí)行，在最終提交操作之間，用戶可以隨時(shí)取消前面的操作，將數(shù)據(jù)庫(kù)還原到?jīng)]有執(zhí)行操作前的狀態(tài)。58第五十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日事務(wù)的屬性原子性:事務(wù)必須是原子工作單元。它對(duì)數(shù)據(jù)庫(kù)所進(jìn)行的操作，要么全都執(zhí)行，要么全都不執(zhí)行。一致性:事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)。在相關(guān)數(shù)據(jù)庫(kù)中，所有規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。隔離性:由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。持久性:事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。59第五十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日日常生活中事務(wù)的例子用戶在網(wǎng)上商場(chǎng)選擇商品，然后向商家提交購(gòu)物請(qǐng)求，并進(jìn)行網(wǎng)上支付。此時(shí)，交易狀態(tài)為提交。商家獲得用戶提交的購(gòu)物請(qǐng)求，在確認(rèn)收到網(wǎng)上支付的金額后向用戶郵寄商品。此時(shí)，交易狀態(tài)為已處理。用戶在收到商品并確認(rèn)商品無(wú)質(zhì)量后，在網(wǎng)上商場(chǎng)確認(rèn)已收到商品。此時(shí)，交易狀態(tài)為成功。定義一個(gè)事務(wù)需要3種操作，即啟動(dòng)事務(wù)、回滾事務(wù)和提交事務(wù)。啟動(dòng)事務(wù)相當(dāng)于用戶提交購(gòu)物請(qǐng)求之前的狀態(tài)，回滾事務(wù)相當(dāng)于用戶取消當(dāng)前交易，提交事務(wù)相當(dāng)于用戶確認(rèn)交易成功。60第六十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日啟動(dòng)事務(wù)SQLServer中包括2種啟動(dòng)事務(wù)的模式，即顯式事務(wù)和隱式事務(wù)。顯式事務(wù)：通過(guò)BEGINTRANSACTION語(yǔ)句顯式啟動(dòng)事務(wù)。BEGINTRANSACTION語(yǔ)句的基本語(yǔ)法如下：

BEGINTRANSACTION[事務(wù)名]在顯式事務(wù)中，事務(wù)名是可選項(xiàng)。事務(wù)名必須符合標(biāo)識(shí)符命名規(guī)則。隱式事務(wù)：當(dāng)用戶沒(méi)有顯式地定義事務(wù)時(shí)，SQLServer按其默認(rèn)的規(guī)定自動(dòng)劃分事務(wù)。61第六十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日回滾事務(wù)如果服務(wù)器錯(cuò)誤使事務(wù)無(wú)法成功完成，SQLServer將自動(dòng)回滾該事務(wù)，并釋放該事務(wù)占用的所有資源。如果客戶端與SQLServer的網(wǎng)絡(luò)連接中斷了，那么當(dāng)網(wǎng)絡(luò)告知SQLServer該中斷時(shí)，將回滾該連接的所有未完成事務(wù)。如果用戶需要手動(dòng)回滾事務(wù)，可以使用語(yǔ)句：ROLLBACKTRANSACTION[事務(wù)名]其中，“事務(wù)名”是給BEGINTRANSACTION上的事務(wù)指派的名稱。62第六十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日提交事務(wù)COMMITTRANSACTION語(yǔ)句可以標(biāo)志一個(gè)成功的隱性事務(wù)或顯式事務(wù)的結(jié)束，它的基本語(yǔ)法如下：

COMMIT[TRANSACTION][事務(wù)名]注意，不能在發(fā)出COMMITTRANSACTION語(yǔ)句之后回滾事務(wù)，因?yàn)閿?shù)據(jù)修改已經(jīng)成為數(shù)據(jù)庫(kù)的永久部分。63第六十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-11定義一個(gè)事務(wù)，向“學(xué)生”表中插入兩條記錄。其中，第1條INSERT語(yǔ)句是正確的，而第2條INSERT語(yǔ)句是錯(cuò)誤的。執(zhí)行此事務(wù)語(yǔ)句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)第1條語(yǔ)句沒(méi)有被執(zhí)行。

USE學(xué)生管理GOBEGINTRANSACTIONINSERTINTO學(xué)生VALUES('test1',1,'一班',500,2)INSERTINTO學(xué)生VALUES(10,'test2',1,'二班',500,2)COMMITTRANSACTIONGO64第六十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-11第2條插入語(yǔ)句因?yàn)橹付藰?biāo)識(shí)列的值，所以產(chǎn)生錯(cuò)誤。執(zhí)行此事務(wù)語(yǔ)句的結(jié)果如下：服務(wù)器:消息8101，級(jí)別16，狀態(tài)1，行1在查詢分析器中執(zhí)行如下語(yǔ)句：

SELECT*FROM學(xué)生可以看到，因?yàn)榈?條INSERT語(yǔ)句出現(xiàn)錯(cuò)誤，導(dǎo)致事務(wù)回滾，所以第1條語(yǔ)句所插入的記錄也沒(méi)有出現(xiàn)在結(jié)果集中。65第六十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-12定義一個(gè)事務(wù)，向“學(xué)生”表中插入一條記錄，然后將事務(wù)回滾。執(zhí)行此事務(wù)語(yǔ)句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)INSERT語(yǔ)句插入的數(shù)據(jù)不在結(jié)果集中。

USE學(xué)生管理GOBEGINTRANSACTION

INSERTINTO學(xué)生VALUES('test1',1,'一班',500,2)ROLLBACKTRANSACTIONGO66第六十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-12執(zhí)行此事務(wù)語(yǔ)句的結(jié)果如下：（所影響的行數(shù)為1行）證明INSERT語(yǔ)句已經(jīng)被執(zhí)行。在查詢分析器中執(zhí)行如下語(yǔ)句：

SELECT*FROM學(xué)生可以看到，因?yàn)閳?zhí)行了ROLLBACKTRANSACTION語(yǔ)句，導(dǎo)致事務(wù)回滾，所以INSERT語(yǔ)句所插入的記錄也沒(méi)有出現(xiàn)在結(jié)果集中。67第六十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日7.2.4創(chuàng)建觸發(fā)器在企業(yè)管理器中手動(dòng)地創(chuàng)建使用CREATETRIGGER語(yǔ)句創(chuàng)建68第六十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日在創(chuàng)建觸發(fā)器之前，應(yīng)該考慮以下問(wèn)題CREATETRIGGER語(yǔ)句必須是批處理中的第一條語(yǔ)句。SQLServer將該批處理中隨后的其他所有語(yǔ)句解釋為CREATETRIGGER語(yǔ)句定義的一部分。創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)移給其他用戶。觸發(fā)器為數(shù)據(jù)庫(kù)對(duì)象，其名稱必須遵循標(biāo)識(shí)符的命名規(guī)則。雖然觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器。69第六十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日在創(chuàng)建觸發(fā)器之前，應(yīng)該考慮以下問(wèn)題雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表，不應(yīng)引用系統(tǒng)表。如果已經(jīng)給一個(gè)表的外鍵定義了級(jí)聯(lián)刪除或級(jí)聯(lián)更新，則不能在該表上定義INSTEADOFDELETE或INSTEADOFDELETEUPDATE觸發(fā)器。雖然TRUNCATETABLE語(yǔ)句（刪除表中的所有行）類似于沒(méi)有WHERE子句的DELETE語(yǔ)句，但它并不會(huì)引發(fā)DELETE觸發(fā)器。WRITETEXT語(yǔ)句（更新text、ntext或image類型的列）不會(huì)引發(fā)INSERT或UPDATE觸發(fā)器。70第七十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日使用CREATETRIGGER語(yǔ)句創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表名|視圖名}[WITHENCRYPTION]{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS[IFUPDATE(列)[{AND|OR}UPDATE(列)][...n]]SQL語(yǔ)句[...n]}71第七十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明觸發(fā)器名：必須符合標(biāo)識(shí)符的命名規(guī)則，并且在數(shù)據(jù)庫(kù)中必須唯一?？梢赃x擇是否指定觸發(fā)器所有者的名稱。表名|視圖名：是在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖?？梢赃x擇是否指定表或視圖的所有者名稱。WITHENCRYPTION：加密syscomments表中包含CREATETRIGGER語(yǔ)句文本的條目。AFTER：指定觸發(fā)器只有在觸發(fā)SQL語(yǔ)句中指定的所有操作都已成功執(zhí)行后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器。如果僅指定FOR關(guān)鍵字，則AFTER是默認(rèn)設(shè)置。不能在視圖上定義AFTER觸發(fā)器。72第七十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明INSTEADOF：指定執(zhí)行觸發(fā)器而不是執(zhí)行觸發(fā)SQL語(yǔ)句，從而替代觸發(fā)SQL語(yǔ)句的操作。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。然而，可以在每個(gè)具有INSTEADOF觸發(fā)器的視圖上定義視圖。{[DELETE][,][INSERT][,][UPDATE]}：是指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語(yǔ)句時(shí)將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用以任意順序組合的這些關(guān)鍵字。如果指定的選項(xiàng)多于一個(gè)，需用逗號(hào)分隔這些選項(xiàng)。73第七十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日參數(shù)說(shuō)明AS：引入觸發(fā)器要執(zhí)行的操作。IFUPDATE(列)：用于判斷是否在指定的列上進(jìn)行了INSERT或UPDATE操作（不能用于DELETE操作），可以指定多列。因?yàn)樵贠N子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。對(duì)于INSERT操作，IFUPDATE將返回TRUE值，因?yàn)檫@些列插入了數(shù)據(jù)。SQL語(yǔ)句：當(dāng)嘗試DELETE、INSERT或UPDATE操作時(shí)要執(zhí)行的Transact-SQL語(yǔ)句。74第七十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日?qǐng)D書館管理案例讀者表讀者種類表圖書表借閱表75第七十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日案例：圖書借閱CreateTRIGGER[inserttr1]ON[dbo].[借閱表]

FORINSERT,update

AS/*自動(dòng)填充應(yīng)還書日期值*/

declare@dayint

select@day=借書期限from讀者種類信息表a,讀者表b,insertedwherea.種類編號(hào)=b.讀者種類andb.讀者編號(hào)=inserted.讀者編號(hào)update借閱表set應(yīng)還書日期=出借日期+@daywhere讀者編號(hào)=(select讀者編號(hào)frominserted)76第七十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日/*自動(dòng)更新罰金*/declare@retudatedatetimeset@retudate=''select@retudate=還書日期

from

inserteddeclare@numintif@retudate<>''ifexists(select*frominsertedwhere應(yīng)還書日期<還書日期)beginupdate借閱表set罰金=0.1*datediff(day,應(yīng)還書日期,@retudate)where讀者編號(hào)=(select讀者編號(hào)frominserted)endelsebeginupdate借閱表set罰金=0where讀者編號(hào)=(select讀者編號(hào)frominserted)end77第七十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日/*自動(dòng)更新圖書表中的已借出量*/select@num=借閱數(shù)量,@retudate=還書日期frominsertedif@retudate<>''beginupdate圖書表set已借出量=已借出量-@numwhere圖書編號(hào)=(select圖書編號(hào)frominserted)endelsebeginupdate圖書表set已借出量=已借出量+@numwhere圖書編號(hào)=(select圖書編號(hào)frominserted)end

78第七十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-13INSERT觸發(fā)器在“學(xué)生”表中創(chuàng)建一個(gè)INSERT觸發(fā)器，如果插入記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行插入操作，并提示用戶。

CREATETRIGGERinsert_studentON學(xué)生FORINSERTASDECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所屬院系FROMinserted79第七十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-13INSERT觸發(fā)器--判斷插入的院系記錄是否存在上級(jí)記錄SELECT@orgname=院系名稱FROM院系WHERE上級(jí)編號(hào)=@orgidIF@orgname<>''--如果存在上級(jí)院系BEGIN

PRINT'指定院系存在下級(jí)單位，請(qǐng)選擇具體單位！'ROLLBACKTRANSACTION ENDGO80第八十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-13INSERT觸發(fā)器

為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語(yǔ)句：INSERTINTO學(xué)生VALUES('小朱',0,'二班',500,1)

因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下：

指定院系存在下級(jí)單位，請(qǐng)選擇具體單位！81第八十一頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-14UPDATE觸發(fā)器在“學(xué)生”表中創(chuàng)建一個(gè)UPDATE觸發(fā)器，如果修改記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行修改操作，并提示用戶。

CREATETRIGGERupdate_studentON學(xué)生FORUPDATEAS--從表inserted中獲取更新后記錄的院系編號(hào)

DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所屬院系FROMinserted82第八十二頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-14UPDATE觸發(fā)器--判斷修改的院系記錄是否存在上級(jí)記錄SELECT@orgname=院系名稱FROM院系WHERE上級(jí)編號(hào)=@orgidIF@orgname<>''BEGIN

PRINT'指定院系存在下級(jí)單位，請(qǐng)選擇具體單位！'ROLLBACKTRANSACTION--回滾操作ENDGO83第八十三頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-14UPDATE觸發(fā)器

為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語(yǔ)句：UPDATE學(xué)生SET所屬院系=1WHERE所屬院系=2因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下：

指定院系存在下級(jí)單位，請(qǐng)選擇具體單位！84第八十四頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-15DELETE觸發(fā)器在“院系”表中創(chuàng)建一個(gè)DELETE觸發(fā)器，如果刪除記錄的院系編號(hào)值在“院系”表中存在下級(jí)單位（例如，計(jì)算機(jī)學(xué)院包括軟件系），則不執(zhí)行刪除操作，并提示用戶。CREATETRIGGERdelete_orgON院系FORDELETEAS

--從表deleted中獲取刪除記錄的院系編號(hào)DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=記錄編號(hào)FROMdeleted85第八十五頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-15DELETE觸發(fā)器--判斷刪除的院系記錄是否存在下級(jí)記錄SELECT@orgname=院系名稱FROM院系WHERE上級(jí)編號(hào)=@orgidIF@orgname<>''BEGIN

PRINT'指定院系存在下級(jí)單位，不允許被刪除！'

ROLLBACKTRANSACTION--回滾操作 ENDGO86第八十六頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-15DELETE觸發(fā)器

為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語(yǔ)句：

DELETEFROM院系WHERE記錄編號(hào)=1因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下：

指定院系存在下級(jí)單位，不允許被刪除！87第八十七頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-16INSTEADOF觸發(fā)器使用INSTEADOF觸發(fā)器實(shí)現(xiàn)例7-15的功能。CREATETRIGGERdelete_org1ON院系INSTEADOFDELETEAS--從表deleted中獲取刪除記錄的院系編號(hào)DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=記錄編號(hào)FROMdeleted88第八十八頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-16INSTEADOF觸發(fā)器--判斷修改的院系記錄是否存在下級(jí)記錄SELECT@orgname=院系名稱FROM院系WHERE上級(jí)編號(hào)=@orgidIF@orgname<>''

PRINT'指定院系存在下級(jí)單位，不允許被刪除！'

ELSE

DELETEFROM院系WHERE記錄編號(hào)=@orgidGO89第八十九頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-16INSTEADOF觸發(fā)器

因?yàn)镮NSTEADOF觸發(fā)器使用觸發(fā)器中定義的代碼取代原操作，所以不需要進(jìn)行回滾操作。當(dāng)然，如果原操作符合規(guī)定的條件，還需要在觸發(fā)器中重新執(zhí)行此操作。為了驗(yàn)證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語(yǔ)句：DELETEFROM院系WHERE記錄編號(hào)=1因?yàn)樵合稻幪?hào)為1的記錄存在下級(jí)單位，所以返回結(jié)果如下：指定院系存在下級(jí)單位，不允許被刪除！（所影響的行數(shù)為1行）90第九十頁(yè)，共一百零三頁(yè)，編輯于2023年，星期日例7-17對(duì)特定列進(jìn)行測(cè)試使用IFUPDATE(列名)子句實(shí)現(xiàn)例7-14的功能。CREATETRIGGERupdate_student1ON學(xué)生FORUPDATEAS--從表inserted中獲取更新后記錄的院系編號(hào)DECLARE@orgidintDECLARE@orgnamevarchar(100)IFUPDATE(所屬院系)BEGINSELECT@orgid=所屬院系FROMinserted

--判斷修改的院系記錄是否存在上級(jí)記錄SELECT@orgname=院系名稱FROM院系WHERE上級(jí)編號(hào)=@orgid