數(shù)據(jù)庫系統(tǒng)原理與設(shè)計實驗教程 第4版 課件 第5章 數(shù)據(jù)庫編程技術(shù)

第1頁第5章數(shù)據(jù)庫編程技術(shù)數(shù)據(jù)庫系統(tǒng)原理實驗教程第4版5.1相關(guān)知識5.1.1游標5.1.2存儲過程5.1.3觸發(fā)器5.2實驗十二游標與存儲過程5.2.1實驗?zāi)康呐c要求5.2.2實驗案例5.2.3實驗內(nèi)容5.3實驗十三觸發(fā)器5.3.1實驗?zāi)康呐c要求5.3.2實驗案例5.3.3實驗內(nèi)容目錄第3頁5.1相關(guān)知識5.1.1游標游標是一種允許用戶訪問單獨的數(shù)據(jù)行的數(shù)據(jù)訪問機制。游標主要用在存儲過程、觸發(fā)器和T-SQL腳本中，使用游標，可以對由SELECT語句返回的結(jié)果集記錄進行逐行處理。使用游標必須經(jīng)歷五個步驟：①定義游標：DECLARE②打開游標：OPEN③逐行提取游標集中的行：FETCH④關(guān)閉游標：CLOSE⑤釋放游標：DEALLOCATE第4頁1.定義游標語法：DECLAREcursor_nameSCROLLCURSORFORsql_staments[FOR[READONLY|UPDATE{OFcolumn_name_list[,...n]]]其中：·cursor_name：用戶定義的游標名。·sql_staments：定義游標結(jié)果集的標準SELECT語句?！OR：后面的短語定義游標屬性只讀或更新，缺省時為UPDATE?！PDATE{OFcolumn_name_list}：定義游標內(nèi)可更新的列。如果指定OFcolumn_name_list[,...n]參數(shù)，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。第5頁·READONLY：在UPDATE或DELETE語句的WHERECURRENTOF子句中不能引用游標。該選項替代要更新的游標的默認功能?！CROLL：指定所有的提取選項(FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE)均可用。如果在DECLARECURSOR中未指定SCROLL，則NEXT是唯一支持的提取選項。注意：①當(dāng)游標移至尾部，不可以再讀取游標，必須關(guān)閉游標然后重新打開游標。②可以通過檢查全局變量@@fetch_status來判斷是否已讀完游標集中所有行。第6頁2.打開游標使用OPEN語句執(zhí)行SELECT語句并生成游標。語法為：OPENcurser_name3.提取游標①逐行提取游標集中的行：FETCHcurser_name[INTO@variable_name[,...n]]②FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar

}|Relative{n|@nvar}][FROM{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]第7頁其中：·NEXT：返回緊跟當(dāng)前行之后的結(jié)果行，并且當(dāng)前行遞增為結(jié)果行。如果FETCHNEXT為對游標的第一次提取操作，則返回結(jié)果集中的第一行。NEXT為默認的游標提取選項?！RIOR：返回緊臨當(dāng)前行前面的結(jié)果行，并且當(dāng)前行遞減為結(jié)果行。如果FETCHPRIOR為對游標的第一次提取操作，則沒有行返回并且游標置于第一行之前?！IRST：返回游標中的第一行并將其作為當(dāng)前行?！AST：返回游標中的最后一行并將其作為當(dāng)前行?！BSOLUTE{n|@nvar}：如果n或@nvar為正數(shù)，返回從游標頭開始的第n行并將返回的行變成新的當(dāng)前行。如果n或@nvar為負數(shù)，返回游標尾之前的第n行并將返回的行變成新的當(dāng)前行。如果n或@nvar為0，則沒有行返回。n必須為整型常量且@nvar必須為smallint、tinyint或int。·RELATIVE{n|@nvar}：如果n或@nvar為正數(shù)，返回當(dāng)前行之后的第n行并將返回的行變成新的當(dāng)前行。如果n或@nvar為負數(shù)，返回當(dāng)前行之前的第n行并將返回的行變成新的當(dāng)前行。如果n或@nvar為0，返回當(dāng)前行。如果對游標的第一次提取操作時將FETCHRELATIVE的n或@nvar指定為負數(shù)或0，則沒有行返回。n必須為整型常量且@nvar必須為smallint、tinyint或int。·INTO@variable_name[,...n]：把每列中的數(shù)據(jù)轉(zhuǎn)移到指定的變量中。

第8頁4.關(guān)閉游標關(guān)閉游標可以釋放某些資源，如游標結(jié)果集和對當(dāng)前行的鎖定，如果重新發(fā)出一個OPEN語句，則該游標結(jié)構(gòu)仍可用于處理。語法為：CLOSEcurser_name5.釋放游標DEALLOCATE語句則完全釋放分配給游標的資源，包括游標名稱。在游標被釋放后，必須使用DECLARE語句重新生成游標。語法為：DEALLOCATEcurser_name6.刪除游標集中當(dāng)前行語法：DELETEFROMtable_nameWHERECURRENTOFcurser_name注意：從游標中刪除一行后，游標定位于被刪除的游標之后的一行，必須再用FETCH得到該行。第9頁7.更新游標集中當(dāng)前行語法：UPDATEtable_name

SETcolumn_name=expression[,column_name=expression]WHERECURRENTOFcurser_name第10頁5.1.2存儲過程SQLServer提供了一種方法，它可以將一些固定的操作集中起來由SQLServer數(shù)據(jù)庫服務(wù)器來完成，以實現(xiàn)某個任務(wù)，這種方法就是存儲過程。存儲過程是經(jīng)過編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務(wù)器中SQL語句寫的過程，使用時只要調(diào)用即可。存儲過程的優(yōu)點是：(1)提供了在服務(wù)器端快速執(zhí)行SQL語句的有效途徑。(2)降低了客戶機和服務(wù)器之間的通信量(3)方便實施企業(yè)規(guī)則。(4)業(yè)務(wù)封裝后，對數(shù)據(jù)庫系統(tǒng)提供了一定的安全保證。第11頁創(chuàng)建存儲過程時，需要確定存儲過程的3個組成部分：①所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。②被執(zhí)行的針對數(shù)據(jù)庫的操作語句，包括調(diào)用其它存儲過程的語句。③返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。第12頁1.創(chuàng)建存儲過程語法：CREATEPROCEDUREprocedure_name

[;number][{@parameterdatatype}[OUTPUT]][,...n]ASsql_statement[,...n]其中：·procedure_name：存儲過程的名稱。創(chuàng)建臨時過程，在procedure_name前面加一個編號符，即#procedure_name；創(chuàng)建全局臨時過程，在procedure_name前面加兩個編號符，即##procedure_name。完整的名稱(包括#或##)不能超過128個字符。過程所有者的名稱是可選的。第13頁·number：是可選的整數(shù)，用來對同名的過程分組，以便用一條DROPPROCEDURE語句即可將同組的過程一起除去。例如，名為orders的應(yīng)用程序使用的過程可以命名為orderproc;1、orderproc;2等。DROPPROCEDUREorderproc語句將除去整個組?！parameter：過程中的參數(shù)，最多可以有2100個參數(shù)?！atatype：參數(shù)的數(shù)據(jù)類型。所有數(shù)據(jù)類型(包括text、ntext和image)均可以用作存儲過程的參數(shù)?！UTPUT：表明參數(shù)是輸出參數(shù)，text、ntext和image參數(shù)可用作OUTPUT參數(shù)。使用OUTPUT關(guān)鍵字的輸出參數(shù)可以是游標占位符?！：表示最多可以指定2100個參數(shù)的占位符?！S：指定過程要執(zhí)行的操作。·sql_statement：過程中的Transact-SQL語句。第14頁2.執(zhí)行存儲過程語法：EXECUTE{procedure_name[;number]|@procedure_name_var}[OUTPUT][,...n]其中：·procedure_name：擬調(diào)用的存儲過程名?！procedure_name_var：局部定義的變量名?！parameter：過程參數(shù)，在CREATEPROCEDURE語句中定義。參數(shù)名稱前必須加上符號@。在以@parameter_name=value格式使用時，參數(shù)名稱和常量不一定按照CREATEPROCEDURE語句中定義的順序出現(xiàn)。但是，如果有一個參數(shù)使用@parameter_name=value格式，則其它所有參數(shù)都必須使用這種格式?！UTPUT：指定存儲過程必須返回一個參數(shù)。使用OUTPUT參數(shù)，參數(shù)值必須作為變量傳遞。在執(zhí)行過程之前，必須聲明變量的數(shù)據(jù)類型并賦值。返回參數(shù)可以是text或image數(shù)據(jù)類型以外的任意數(shù)據(jù)類型。第15頁3.重命名存儲過程語法：

Sp_rename'procedure_name1','procedure_name2'4.刪除存儲過程語法：

DROPPROCEDUREprocedure_name第16頁5.1.3觸發(fā)器觸發(fā)器是一種特殊的存儲過程，當(dāng)INSERT、DELETE或UPDATE語句修改指定表的一行或多行時，自動執(zhí)行觸發(fā)器。在觸發(fā)器的使用中，系統(tǒng)會自動產(chǎn)生兩張臨時表Deleted和Inserted。用戶不能直接修改這兩個表的內(nèi)容。①Deleted表：存儲在DELETE和UPDATE語句執(zhí)行時所影響的行的拷貝，在DELETE和UPDATE語句執(zhí)行前被作用的行轉(zhuǎn)移到Deleted表中。②Inserted表：存儲在INSTERT和UPDATE語句執(zhí)行時所影響的行的拷貝，在Insert和UPDATE語句執(zhí)行期間，新行被同時加到Inserted和觸發(fā)器表中。第17頁觸發(fā)器僅在當(dāng)前DB中生成，觸發(fā)器有3種類型，即插入、刪除和更新。(1)INSERT類型的觸發(fā)器：當(dāng)對指定表TableName執(zhí)行了插入操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。(2)UPDATE類型的觸發(fā)器：當(dāng)對指定表TableName執(zhí)行了更新操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。(3)DELETE類型的觸發(fā)器：當(dāng)對指定表TableName執(zhí)行了刪除操作時系統(tǒng)自動執(zhí)行觸發(fā)器代碼。第18頁在觸發(fā)器內(nèi)不能使用如下的SQL命令：①所有數(shù)據(jù)庫對象的生成命令，如CREATETABLE、CREATEINDEX等。②所有數(shù)據(jù)庫對象的結(jié)構(gòu)修改命令，如ALTERTABLE、ALTERDATABASE等。③創(chuàng)建臨時保存表。④所有DROP命令。⑤GRANT和REVOKE命令。⑥TRUNCATETABLE命令。⑦LOADDATABASE和LOADTRANSACTION命令。

⑧RECONFIGURE命令。第19頁1.創(chuàng)建觸發(fā)器語法：CREATETRIGGERtrigger_nameONtable_nameFOR<INSERT|UPDATE|DELETE>AS

sql_statement2.刪除觸發(fā)器語法：DROPTRIGGERtrigger_name3.修改觸發(fā)器語法：ALTERTRIGGERtriggernameONtable_nameFOR<INSERT|UPDATE|DELETE>ASsql_statement第20頁5.2實驗十二游標與存儲過程5.2.1實驗?zāi)康呐c要求(1)掌握游標的定義和使用方法。(2)掌握存儲過程的定義、執(zhí)行和調(diào)用方法。(3)掌握游標和存儲過程的綜合應(yīng)用方法。第21頁5.2.2實驗案例[例5.1]利用游標查詢業(yè)務(wù)科員工的編號、姓名、性別、部門和薪水，并逐行顯示游標中的信息。DECLAREcur_empSCROLLCURSORFORSELECTemployeeno,employeename,sex,department,salaryFROMemployeeWHEREdepartment='業(yè)務(wù)科'ORDERBYemployeeno

/*定義游標*/OPENcur_emp

/*打開游標*/SELECT'CURSOR內(nèi)數(shù)據(jù)條數(shù)'=@@cursor_rows

/*顯示游標內(nèi)記錄的個數(shù)*/FETCHNEXTFROMcur_emp

/*逐行提取游標中的記錄*/WHILE(@@FETCH_status<>-1)

/*判斷FETCH語句是否執(zhí)行成功*/BEGINSELECT'cursor讀取狀態(tài)'=@@FETCH_status/*顯示游標的讀取狀態(tài)*/FETCHNEXTFROMcur_emp

/*提取游標下一行信息*/ENDCLOSEcur_emp

/*關(guān)閉游標*/DEALLOCATEcur_emp

/*釋放游標*/第22頁

本例中，@@cursor_rows是返回連接上最后打開的游標中當(dāng)前存在的合格行的數(shù)量。具體參數(shù)信息見表5-1所示。第23頁@@FETCH_status是返回被FETCH語句執(zhí)行的最后，而不是任何當(dāng)前被連接打開的游標的狀態(tài)。具體參數(shù)見表5-2所示。第24頁[例5.2]利用游標查詢業(yè)務(wù)科員工的編號、姓名、性別、部門和薪水，并以格式化的方式輸出游標中的信息。DECLARE@emp_nochar(8),@emp_namechar(10),@sexchar(1),@deptchar(4)DECLARE@salarynumeric(8,2),@textchar(100)/*用戶自定義的幾個變量*/DECLAREemp_curSCROLLCURSORFORSELECTemployeeNo,employeeName,sex,department,salaryFROMEmployeeWHEREdepartment='業(yè)務(wù)科'ORDERBYemployeeNo/*定義游標*/SELECT@text='========業(yè)務(wù)科員工情況列表==========='PRINT@textSELECT@text='編號

姓名

性別

部門

薪水'PRINT@textSELECT@text='----------------------------------'PRINT@text/*按照用戶要求格式化輸出相關(guān)信息*/OPENemp_cur

/*打開游標*/第25頁FETCHemp_curINTO@emp_no,@emp_name,@sex,@dept,@salary/*提取游標中的信息傳遞并分別給內(nèi)存變量*/WHILE(@@FETCH_status=0)/*判斷是否提取成功*/BEGINSELECT@text=@emp_no+''+@emp_name+''+@sex+''+@dept+''+convert(char(10),@salary)/*給@text賦字符串值*/PRINT@text/*打印字符串值*//*提取游標中的信息傳遞并分別給內(nèi)存變量*/FETCHemp_curinto@emp_no,@emp_name,@sex,@dept,@salaryENDCLOSEemp_cur

/*關(guān)閉游標*/DEALLOCATEemp_cur

/*釋放游標*/本例中，主要結(jié)合SELECT和PRINT命令將創(chuàng)建游標后逐行提取游標的信息以格式化的方式輸出，提高了腳本的可讀性

第26頁[例5.3]不帶參數(shù)的存儲過程：利用存儲過程計算出’E2020002’業(yè)務(wù)員的銷售總金額。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot1ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=’E2020002’②執(zhí)行存儲過程EXECsales_tot1上述操作只能統(tǒng)計業(yè)務(wù)員’E2020002’的銷售業(yè)績，執(zhí)行此存儲過程不能統(tǒng)計任意一個業(yè)務(wù)員的銷售業(yè)績。第27頁[例5.4]帶輸入?yún)?shù)的存儲過程：統(tǒng)計某業(yè)務(wù)員的銷售總金額。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot2@e_no

char(8)ASSELECTsum(orderSum)FROMOrderMasterWHEREsalerNo=@e_no②執(zhí)行存儲過程EXECsales_tot2'E2020003'

注：

程序中使用@符號表示一個變量來指定參數(shù)名稱，且每個過程的參數(shù)僅用于該過程本身。上述操作只要在執(zhí)行存儲過程時添加輸入?yún)?shù)(即被統(tǒng)計的業(yè)務(wù)員的編號)就能統(tǒng)計任一業(yè)務(wù)員的銷售業(yè)績。問題：任意一個業(yè)務(wù)員的銷售總金額如何被其他用戶/程序方便調(diào)用呢？

第28頁[例5.5]帶輸入/輸出參數(shù)的存儲過程：統(tǒng)計某業(yè)務(wù)員的銷售總金額并返回其結(jié)果。①創(chuàng)建存儲過程CREATEPROCEDUREsales_tot3@E_nochar(8),@p_tot

intOUTPUTASSELECT@p_tot=sum(orderSum)FROMOrderMasterWHEREsalerNo=@E_no②執(zhí)行存儲過程DECLARE@tot_amt

intEXECsales_tot3'E2020003',@tot_amtOUTPUTSELECT銷售總額=@tot_amt上述操作可以統(tǒng)計任一員工的銷售業(yè)績并能實現(xiàn)其結(jié)果的調(diào)用。第29頁[例5.6]帶通配符參數(shù)的存儲過程(模糊查找)：統(tǒng)計所有姓陳的員工的銷售業(yè)績并輸出他們姓名和所在部門。①創(chuàng)建存儲過程CreateProcedureemp_name

@E_name

varchar(10)ASSELECTa.EmployeeName,a.department,ssumFROMEmployeea,(SELECTSalerNo,ssum=sum(OrderSum)FROMOrderMasterGROUPBYSalerNo)bWHEREa.EmployeeNo=b.SalerNoANDa.EmployeeNameLIKE@E_name②執(zhí)行存儲過程EXECemp_name

@E_name='陳%'第30頁[例5.7]重命名存儲過程：將存儲過程sales_tot2改名為sale_tot。

Sp_rename‘sales_tot2’,‘sale_tot’[例5.8]刪除存儲過程：將存儲過程sale_tot刪除。DROPPROCEDUREsale_tot第31頁[例5.9]游標和存儲過程的綜合應(yīng)用：請使用游標和循環(huán)語句編寫一個存儲過程emp_tot，根據(jù)業(yè)務(wù)員姓名，查詢該業(yè)務(wù)員在銷售工作中的客戶信息及每一客戶的銷售記錄，并輸出該業(yè)務(wù)員的銷售總金額。第32頁①創(chuàng)建存儲過程CREATEPROCEDUREemp_tot@v_emp_namechar(10)ASBEGINDECLARE@sv_emp_namevarchar(10),@v_custnamevarchar(10),@p_totintDECLARE@sumint,@countint,@order_novarchar(10)SELECT@sum=0,@count=0DECLAREget_totCURSORFORSELECTEmployeeName,CustomerNo,b.OrderNo,OrderSumFROMEmployeea,OrderMasterbWHEREa.EmployeeName=@v_emp_nameANDa.EmployeeNo=b.SalerNoOPENget_totFETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_tot第33頁WHILE(@@FETCH_status=0)

BEGIN

SELECT業(yè)務(wù)員=@sv_emp_name,客戶=@v_custname,訂單編號=@order_no,訂單金額=@p_totSELECT@sum=@sum+@p_totSELECT@count=@count+1FETCHget_totINTO@sv_emp_name,@v_custname,@order_no,@p_totENDCLOSEget_totDEALLOCATEget_totIF@count=0SELECT0ELSESELECT業(yè)務(wù)員銷售總金額=@sumEND第34頁②執(zhí)行存儲過程

EXECemp_tot'張小娟'本例中，先建立一個游標用于臨時儲存業(yè)務(wù)員的基本銷售信息，包括：業(yè)務(wù)員姓名、客戶編號、訂單編號、訂單銷售金額；再利用游標能逐行提取的功能，提取游標中每一記錄，同時輸出這些信息；最后統(tǒng)計其相應(yīng)定單金額的總額，并輸出訂單總額。第35頁5.2.3實驗內(nèi)容在訂單數(shù)據(jù)庫OrderDB中請完成以下實驗內(nèi)容：

(1)根據(jù)訂單明細表中的數(shù)據(jù)，利用游標修改OrderMaster表中orderSum的值。(2)創(chuàng)建存儲過程，要求：按第2章員工表定義中的CHECK約束自動產(chǎn)生員工編號。該過程的輸入?yún)?shù)為員工入職的年份，輸出參數(shù)是自動生成的員工編號，該編號滿足第2章員工表定義中的CHECK約束，且后三位流水號等于表中與入職年份相同的員工編號最大值加1，如：輸入?yún)?shù)為2020，且員工表中該年度最大的編碼是E2020005，自動產(chǎn)生的編號為E20200006；如果該入職年份沒有其他員工，則流水號為001。第36頁

(3)

創(chuàng)建存儲過程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=============大客戶中熱銷的前種商品的銷售信息===========商品編號

商品名稱

總銷售金額P20200003三星-Galaxy-A949381.00P20200001vivo-X939173.80P20200002中興AXON天機7(A2017)27891.00

第37頁(4)請使用游標和循環(huán)語句創(chuàng)建存儲過程proSearchCustomer，輸入?yún)?shù)為客戶編號，根據(jù)客戶編號查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的商品銷售信息，并按商品分組輸出，制作日期取系統(tǒng)的當(dāng)前日期，輸出格式如下：===================客戶訂單表====================---------------------------------------------------------------------------------客戶名稱：

興隆股份有限公司

客戶地址：

天津市

總金額：

29986.00--------------------------------------------------------------------------------商品編號

總數(shù)量

平均價格

P2020000142798.00P2020000322599.00P2020000543399.00--------------------------------------------------------------------------------報表制作人

張小娟

制作日期

2022-07-08

(5)請利用游標嵌套和循環(huán)語句創(chuàng)建存儲過程proInvoice，輸入?yún)?shù)有兩個，一個是定單的開始時間，一個是定單的結(jié)束時間，要求根據(jù)輸入的時間范圍，輸出每個定單的發(fā)票信息，包括：客戶名稱、定單日期、發(fā)票號碼、業(yè)務(wù)員名稱、定單總金額及定單明細信息等，發(fā)票打印日期取系統(tǒng)的當(dāng)前日期，輸出格式如下：業(yè)務(wù)員銷售時間范圍為:2020-03-01----2020-10-19

==============================通用機打發(fā)票==============================-----------------------------------------------------------------------------------------------------------------------客戶名稱：興隆股份有限公司

定購日期：2020-03-01發(fā)票號碼：I000000006-----------------------------------------------------------------------------------------------------------------------商品名稱

數(shù)量

單價

金額vivo-X942798.0011192.00TCL-D55A630U13399.003399.00----------------------------------------------------------------------------------------------------------------------商品類數(shù):2商品數(shù)量:5合計:14591.00----------------------------------------------------------------------------------------------------------------------定單銷售員：張露

發(fā)票打印日期：2022-07-11----------------------------------------------------------------------------------------------------------------------

==============================通用機打發(fā)票==============================------------------------------------------------------------------------------------------------------------------------客戶名稱：五一商廈

定購日期：2020-03-02發(fā)票號碼：I000000007------------------------------------------------------------------------------------------------------------------------商品名稱

數(shù)量

單價

金額vivo-X922798.005596.00中興AXON天機7(A13099.003099.00三星-Galaxy-A932599.007797.00-------------------------------------------------------------------------------------------------------------------------商品類數(shù):3商品數(shù)量:6合計:16492.00------------------------------------------------------------------------------------------------------------------------定單銷售員：張小娟

發(fā)票打印日期：2022-07-11------------------------------------------------------------------------------------------------------------------------第39頁5.3實驗十三觸發(fā)器5.3.1實驗?zāi)康呐c要求(1)掌握觸發(fā)器的創(chuàng)建和使用方法。(2)掌握游標和觸發(fā)器的綜合應(yīng)用方法。第40頁5.3.2實驗案例[例5.10]刪除觸發(fā)器：編寫一個允許用戶一次只刪除一條記錄的觸發(fā)器。

CREATETRIGGERTr_EmpONEmployeeFORDELETEAS/*對表Employee定義一個刪除觸發(fā)器*/DECLARE@row_cnt

int/*定義變量@row_cnt，用于跟蹤Deleted表中記錄的個數(shù)*/SELECT@Row_Cnt=Count(*)FROMDeletedIf@row_cnt>1/*判斷Deleted表中記錄的個數(shù)是否大于1*/BEGINPRINT‘此刪除操作可能會刪除多條人事表數(shù)據(jù)!!!'ROLLBACKTRANSACTION/*如果Deleted表中記錄的個數(shù)大于1，事務(wù)回滾*/END第41頁分析：本例中，觸發(fā)器約束了用戶只能對Employee這張表刪除一次刪除一條記錄。我們可驗證觸發(fā)器的作用效果。驗證過程如下：(1)DELETEFROMEmployeeWHEREsex='F'在(1)執(zhí)行后，結(jié)果可能出現(xiàn)二種情況：①系統(tǒng)提示：“外鍵約束沖突”錯誤。②系統(tǒng)提示：“此刪除操作可能會刪除多條人事表數(shù)據(jù)!!!”。第①種情況，由于Employee表與其它表建立了外鍵約束關(guān)系，在刪除表中元組時必須滿足參照完整性約束的要求。只有刪除外鍵約束，在執(zhí)行刪除操作時才能激活觸發(fā)器。第②中情況，由于解除了外鍵約束后，刪除操作激活觸發(fā)器，但由于刪除的元組多于一個，所以出現(xiàn)正確系統(tǒng)提示信息。第42頁[例5.11]更新觸發(fā)器：請使用游標和循環(huán)語句為OrderDetail表建立一個更新觸發(fā)器updateorderdetail，要求當(dāng)用戶修改定單明細表中某個商品的數(shù)量或單價時自動修改定單主表中的訂單金額。分析：本例中，Deleted和Inserted表結(jié)構(gòu)與OrderDetail表結(jié)構(gòu)相同。如果用戶修改了銷售明細表中某個貨品的數(shù)量或單價時，Deleted表記載了更新前信息，Inserted表記載了更新后信息，本例正是利用這兩張表結(jié)合游標將正確的訂單金額修改到定單主表中。用戶同樣可以用UPDATE命令修改OrderDetail從而驗證觸發(fā)器的作用。第43頁CREATETRIGGERupdatesaleitemONOrderDetailFORUPDATEAS/*對表Employee定義一個更新觸發(fā)器*/IfUPDATE(quantity)ORUPDATE(price)/*判斷對指定列quantity或price的更新*/BEGIN/*定義兩個內(nèi)存變量用于跟蹤游標中訂單編號和商品編號的值*/DECLARE@ordernoint,@productnochar(5)

/*Deleted表中數(shù)據(jù)信息存入到一個游標結(jié)果集中*/

DECLAREcur_orderdetailCURSORFORSELECTorderno,productnoFROMDeletedOPENcur_orderdetail/*打開游標*/

BEGINTRANSACTION/*事務(wù)開始*//*提取游標中信息并傳遞給變量@orderno,@productno*/FETCHcur_orderdetailINTO@orderno,@productno第44頁WHILE(@@fetch_status=0)/*判斷如果提取成功*/BEGIN/*修改ordermaster中訂單金額的值*/UPDATEordermasterSETordersum=ordersum-D.quan