存儲過程與觸發(fā)器ppt

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

select*from成績表where成績>=60執(zhí)行存儲過程：execseleproc結(jié)果如圖：17第17頁，課件共103頁，創(chuàng)作于2023年2月例2：創(chuàng)建一個向成績表添加記錄的存儲過程。（有參數(shù)存儲過程）createprocinsertproc@snochar(10),@cnochar(3),@gradefloatasinsertinto成績表values(@sno,@cno,@grade)執(zhí)行存儲過程：execinsertproc'0009','03',9818第18頁，課件共103頁，創(chuàng)作于2023年2月例3：創(chuàng)建一個存儲過程，要求根據(jù)輸入的課程號統(tǒng)計該課程平均分。createproccnoavgscore@cnochar(2),@avgscorefloatoutputasdeclare@cnonchar(2)

set@cnon=''

select@cnon=課程號from成績表

where課程號=@cno

if@cnon<>''

begin

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

where課程號=@cno

end

else

print'此課程號不存在'

go19第19頁，課件共103頁，創(chuàng)作于2023年2月運行該存儲過程：declare@ifloatexeccnoavgscore'02',@ioutputprint@i20第20頁，課件共103頁，創(chuàng)作于2023年2月說明：Withencryption子句的作用是將存儲過程的原代碼加密；Withrecompile子句的作用是對該存儲過程重編譯。21第21頁，課件共103頁，創(chuàng)作于2023年2月例4創(chuàng)建存儲過程“增加成績”，將表“成績表”中所有學(xué)生的成績增加10%。USE學(xué)生管理GOCREATEPROCEDURE增加成績ASUPDATE成績表SET成績=成績*1.1GO執(zhí)行存儲過程：exec增加成績

22第22頁，課件共103頁，創(chuàng)作于2023年2月例5（細(xì)節(jié)考慮）在創(chuàng)建一個存儲過程時，如果已經(jīng)存在同名的存儲過程，則不允許創(chuàng)建新的存儲過程?？梢詫⒁陨洗a改寫為：USE學(xué)生管理--如果存在名稱為“增加成績”的存儲過程，則將其刪除IFEXISTS(SELECTnameFROM

sysobjects

WHEREname='增加成績'ANDtype='P')DROPPROCEDURE增加成績GOCREATEPROCEDURE增加成績ASUPDATE成績表SET成績=成績*1.1GO23第23頁，課件共103頁，創(chuàng)作于2023年2月使用企業(yè)管理器創(chuàng)建存儲過程在企業(yè)管理器中，展開要創(chuàng)建存儲過程的數(shù)據(jù)庫,用鼠標(biāo)右擊“存儲過程”，在彈出菜單中單擊“新建存儲過程”命令，打開新建存儲過程對話框，如下圖：24第24頁，課件共103頁，創(chuàng)作于2023年2月使用企業(yè)管理器創(chuàng)建存儲過程在該對話框的文本框中有以下的默認(rèn)語句：CREATEPROCEDURE[OWNER].[PROCEDURENAME]AS這是CREATEPROCEDURE語句的開始部分，存儲過程的具體文本需要用戶自己輸入。例如，將以上默認(rèn)語句修改成：

CREATEPROCEDURE增加成績ASUPDATE成績表SET成績=成績*1.1GO25第25頁，課件共103頁，創(chuàng)作于2023年2月7.1.3執(zhí)行存儲過程[[EXEC[UTE]]{[@返回狀態(tài)=]{存儲過程名|@存儲過程名變量}}[[@參數(shù)名稱=]{值|@變量[OUTPUT]|[DEFAULT]}]

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

@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][OUTPUT][,...n]如果參數(shù)后面使用OUTPUT關(guān)鍵字，則表明它是輸出參數(shù)。30第30頁，課件共103頁，創(chuàng)作于2023年2月例6創(chuàng)建存儲過程add_proc，用于計算兩個參數(shù)之和并將其輸出。

CREATEPROCEDUREadd_proc@num1INT=0,@num2INT=0ASDECLARE@num3INTSET@num3=@num1+@num2PRINT@num3該存儲過程定義了兩個參數(shù)@num1和@num2，它們都是輸入?yún)?shù),參數(shù)類型為INT,默認(rèn)值為0。31第31頁，課件共103頁，創(chuàng)作于2023年2月執(zhí)行例6不帶參數(shù)執(zhí)行：EXECadd_proc運行結(jié)果為：

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

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

32第32頁，課件共103頁，創(chuàng)作于2023年2月例7創(chuàng)建存儲過程add_proc1，用于計算兩個參數(shù)之和，并使用輸出參數(shù)返回結(jié)果。

CREATEPROCEDUREadd_proc1@num1INT=0,@num2INT=0,@num3INTOUTPUT--@num3為輸出參數(shù)ASSET@num3=@num1+@num233第33頁，課件共103頁，創(chuàng)作于2023年2月執(zhí)行例7DECLARE@numASINTEXECadd_proc112,23,@numOUTPUTPRINT@num運行結(jié)果為：3834第34頁，課件共103頁，創(chuàng)作于2023年2月例題8：編輯一個可自由運算的存儲過程createproccalculate@num1asfloat,@num2float,@schar(2),@num3floatoutputasset@num3=case@swhen'+'then@num1+@num2when'-'then@num1-@num2when'*'then@num1*@num2when'/'then@num1/@num2end35第35頁，課件共103頁，創(chuàng)作于2023年2月運行該存儲過程declare@num3floatexeccalculate2,4,'/',@num3outputselect@num336第36頁，課件共103頁，創(chuàng)作于2023年2月7.1.6查看和修改存儲過程使用企業(yè)管理器查看和修改存儲過程使用查詢分析器查看和修改存儲過程使用企業(yè)管理器重命名存儲過程使用ALTERPROCEDURE語句修改存儲過程使用sp_rename重命名存儲過程37第37頁，課件共103頁，創(chuàng)作于2023年2月1.使用企業(yè)管理器查看和修改存儲過程在企業(yè)管理器中展開指定的數(shù)據(jù)庫，選中“存儲過程”項，可以查看指定數(shù)據(jù)庫中定義的所有存儲過程。38第38頁，課件共103頁，創(chuàng)作于2023年2月1.使用企業(yè)管理器查看和修改存儲過程雙擊指定的存儲過程，可以打開存儲過程屬性對話框，查看存儲過程的定義情況。用戶可以在存儲過程屬性對話框中修改存儲過程的代碼。

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

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

44第44頁，課件共103頁，創(chuàng)作于2023年2月例10將存儲過程add_proc重命名為add_proc2，則可以使用以下命令：

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

注意:更改對象名的任一部分都可能破壞腳本和存儲過程。object已重命名為'add_proc2'。45第45頁，課件共103頁，創(chuàng)作于2023年2月7.1.7刪除存儲過程使用企業(yè)管理器使用DROPPROCEDURE語句46第46頁，課件共103頁，創(chuàng)作于2023年2月使用企業(yè)管理器在企業(yè)管理器中，右擊要刪除的存儲過程，選擇“刪除”命令，并確認(rèn)刪除。47第47頁，課件共103頁，創(chuàng)作于2023年2月使用DROPPROCEDURE語句DROPPROCEDURE{存儲過程名}[,...n]48第48頁，課件共103頁，創(chuàng)作于2023年2月例11刪除存儲過程add_proc，可以使用以下命令：

DROPPROCEDUREadd_proc49第49頁，課件共103頁，創(chuàng)作于2023年2月例12刪除一組存儲過程設(shè)某“職工”數(shù)據(jù)庫中有一個“職工工資”表，該表包含有“基本工資”、“獎金”和“實發(fā)工資”等列。創(chuàng)建一組存儲過程，求“職工工資”表的平均基本工資、平均獎金和平均實發(fā)工資。USE職工GOCREATEPROCAveSalary;1ASSELECTAVG(基本工資)FROM職工工資GOCREATEPROCAveSalary;2ASSELECTAVG(獎金)FROM職工工資GOCREATEPROCAveSalary;3ASSELECTAVG(實發(fā)工資)FROM職工工資GO50第50頁，課件共103頁，創(chuàng)作于2023年2月例13刪除一組存儲過程使用以下語句執(zhí)行以上存儲過程。EXECAveSalary;1EXECAveSalary;2EXECAveSalary;3要刪除以上創(chuàng)建的三個存儲過程，可以使用語句：

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

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

COMMIT[TRANSACTION][事務(wù)名]注意，不能在發(fā)出COMMITTRANSACTION語句之后回滾事務(wù)，因為數(shù)據(jù)修改已經(jīng)成為數(shù)據(jù)庫的永久部分。63第63頁，課件共103頁，創(chuàng)作于2023年2月例7-11定義一個事務(wù)，向“學(xué)生”表中插入兩條記錄。其中，第1條INSERT語句是正確的，而第2條INSERT語句是錯誤的。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)第1條語句沒有被執(zhí)行。

USE學(xué)生管理GOBEGINTRANSACTIONINSERTINTO學(xué)生VALUES('test1',1,'一班',500,2)INSERTINTO學(xué)生VALUES(10,'test2',1,'二班',500,2)COMMITTRANSACTIONGO64第64頁，課件共103頁，創(chuàng)作于2023年2月例7-11第2條插入語句因為指定了標(biāo)識列的值，所以產(chǎn)生錯誤。執(zhí)行此事務(wù)語句的結(jié)果如下：服務(wù)器:消息8101，級別16，狀態(tài)1，行1在查詢分析器中執(zhí)行如下語句：

SELECT*FROM學(xué)生可以看到，因為第2條INSERT語句出現(xiàn)錯誤，導(dǎo)致事務(wù)回滾，所以第1條語句所插入的記錄也沒有出現(xiàn)在結(jié)果集中。65第65頁，課件共103頁，創(chuàng)作于2023年2月例7-12定義一個事務(wù)，向“學(xué)生”表中插入一條記錄，然后將事務(wù)回滾。執(zhí)行此事務(wù)語句后，查看“學(xué)生”表中的數(shù)據(jù)，確認(rèn)INSERT語句插入的數(shù)據(jù)不在結(jié)果集中。

USE學(xué)生管理GOBEGINTRANSACTION

INSERTINTO學(xué)生VALUES('test1',1,'一班',500,2)ROLLBACKTRANSACTIONGO66第66頁，課件共103頁，創(chuàng)作于2023年2月例7-12執(zhí)行此事務(wù)語句的結(jié)果如下：（所影響的行數(shù)為1行）證明INSERT語句已經(jīng)被執(zhí)行。在查詢分析器中執(zhí)行如下語句：

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

FORINSERT,update

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

declare@dayint

select@day=借書期限from讀者種類信息表a,讀者表b,insertedwherea.種類編號=b.讀者種類andb.讀者編號=inserted.讀者編號update借閱表set應(yīng)還書日期=出借日期+@daywhere讀者編號=(select讀者編號frominserted)76第76頁，課件共103頁，創(chuàng)作于2023年2月/*自動更新罰金*/declare@retudatedatetimeset@retudate=''select@retudate=還書日期

from

inserteddeclare@numintif@retudate<>''ifexists(select*frominsertedwhere應(yīng)還書日期<還書日期)beginupdate借閱表set罰金=0.1*datediff(day,應(yīng)還書日期,@retudate)where讀者編號=(select讀者編號frominserted)endelsebeginupdate借閱表set罰金=0where讀者編號=(select讀者編號frominserted)end77第77頁，課件共103頁，創(chuàng)作于2023年2月/*自動更新圖書表中的已借出量*/select@num=借閱數(shù)量,@retudate=還書日期frominsertedif@retudate<>''beginupdate圖書表set已借出量=已借出量-@numwhere圖書編號=(select圖書編號frominserted)endelsebeginupdate圖書表set已借出量=已借出量+@numwhere圖書編號=(select圖書編號frominserted)end

78第78頁，課件共103頁，創(chuàng)作于2023年2月例7-13INSERT觸發(fā)器在“學(xué)生”表中創(chuàng)建一個INSERT觸發(fā)器，如果插入記錄的院系編號值在“院系”表中存在下級單位（例如，計算機(jī)學(xué)院包括軟件系），則不執(zhí)行插入操作，并提示用戶。

CREATETRIGGERinsert_studentON學(xué)生FORINSERTASDECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所屬院系FROMinserted79第79頁，課件共103頁，創(chuàng)作于2023年2月例7-13INSERT觸發(fā)器--判斷插入的院系記錄是否存在上級記錄SELECT@orgname=院系名稱FROM院系WHERE上級編號=@orgidIF@orgname<>''--如果存在上級院系BEGIN

PRINT'指定院系存在下級單位，請選擇具體單位！'ROLLBACKTRANSACTION ENDGO80第80頁，課件共103頁，創(chuàng)作于2023年2月例7-13INSERT觸發(fā)器

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

因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下：

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

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

DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=所屬院系FROMinserted82第82頁，課件共103頁，創(chuàng)作于2023年2月例7-14UPDATE觸發(fā)器--判斷修改的院系記錄是否存在上級記錄SELECT@orgname=院系名稱FROM院系WHERE上級編號=@orgidIF@orgname<>''BEGIN

PRINT'指定院系存在下級單位，請選擇具體單位！'ROLLBACKTRANSACTION--回滾操作ENDGO83第83頁，課件共103頁，創(chuàng)作于2023年2月例7-14UPDATE觸發(fā)器

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

指定院系存在下級單位，請選擇具體單位！84第84頁，課件共103頁，創(chuàng)作于2023年2月例7-15DELETE觸發(fā)器在“院系”表中創(chuàng)建一個DELETE觸發(fā)器，如果刪除記錄的院系編號值在“院系”表中存在下級單位（例如，計算機(jī)學(xué)院包括軟件系），則不執(zhí)行刪除操作，并提示用戶。CREATETRIGGERdelete_orgON院系FORDELETEAS

--從表deleted中獲取刪除記錄的院系編號DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=記錄編號FROMdeleted85第85頁，課件共103頁，創(chuàng)作于2023年2月例7-15DELETE觸發(fā)器--判斷刪除的院系記錄是否存在下級記錄SELECT@orgname=院系名稱FROM院系WHERE上級編號=@orgidIF@orgname<>''BEGIN

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

ROLLBACKTRANSACTION--回滾操作 ENDGO86第86頁，課件共103頁，創(chuàng)作于2023年2月例7-15DELETE觸發(fā)器

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

DELETEFROM院系WHERE記錄編號=1因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下：

指定院系存在下級單位，不允許被刪除！87第87頁，課件共103頁，創(chuàng)作于2023年2月例7-16INSTEADOF觸發(fā)器使用INSTEADOF觸發(fā)器實現(xiàn)例7-15的功能。CREATETRIGGERdelete_org1ON院系INSTEADOFDELETEAS--從表deleted中獲取刪除記錄的院系編號DECLARE@orgidintDECLARE@orgnamevarchar(100)SELECT@orgid=記錄編號FROMdeleted88第88頁，課件共103頁，創(chuàng)作于2023年2月例7-16INSTEADOF觸發(fā)器--判斷修改的院系記錄是否存在下級記錄SELECT@orgname=院系名稱FROM院系WHERE上級編號=@orgidIF@orgname<>''

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

ELSE

DELETEFROM院系WHERE記錄編號=@orgidGO89第89頁，課件共103頁，創(chuàng)作于2023年2月例7-16INSTEADOF觸發(fā)器

因為INSTEADOF觸發(fā)器使用觸發(fā)器中定義的代碼取代原操作，所以不需要進(jìn)行回滾操作。當(dāng)然，如果原操作符合規(guī)定的條件，還需要在觸發(fā)器中重新執(zhí)行此操作。為了驗證觸發(fā)器是否正常工作，在查詢分析器中執(zhí)行如下語句：DELETEFROM院系WHERE記錄編號=1因為院系編號為1的記錄存在下級單位，所以返回結(jié)果如下：指定院系存在下級單位，不允許被刪除?。ㄋ绊懙男袛?shù)為1行）90第90頁，課件共103頁，創(chuàng)作于2023年2月例7-17對特定列進(jìn)行測試使用IFUPDATE(列名)子句實現(xiàn)例7-14的功能。CREATETRIGGERupdate_student1ON學(xué)生FORUPDATEAS--從表inserted中獲取更新后記錄的院系編號DECLARE@orgidintDECLARE@orgnamevarchar(100)IFUPDATE(所屬院系)BEGINSELECT@orgid=所屬院系FROMinserted

--判斷修改的院系記錄是否存在上級記錄SELECT@orgname=院系名稱FROM院系WHERE上級編號=