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

存儲(chǔ)過(guò)程和觸發(fā)器第1頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月本章重點(diǎn)存儲(chǔ)過(guò)程的概念、分類存儲(chǔ)過(guò)程的創(chuàng)建、執(zhí)行觸發(fā)器的概念、分類觸發(fā)器的創(chuàng)建、觸發(fā)DELETED與INSERTED表第2頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/52本章難點(diǎn)有參存儲(chǔ)過(guò)程的創(chuàng)建、執(zhí)行觸發(fā)器的觸發(fā)時(shí)機(jī)DELETED與INSERTED表第3頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5310.1存儲(chǔ)過(guò)程的概念10．1．1基本概念存儲(chǔ)過(guò)程是一組編譯在單個(gè)執(zhí)行計(jì)劃中的Transact-SQL語(yǔ)句，將一些固定的操作集中起來(lái)交給SQLServer數(shù)據(jù)庫(kù)服務(wù)器完成，以實(shí)現(xiàn)某個(gè)任務(wù)。10．1．2存儲(chǔ)過(guò)程的優(yōu)點(diǎn)（1）與其他應(yīng)用程序共享應(yīng)用程序邏輯，因而確保了數(shù)據(jù)訪問(wèn)和修改的一致性。（2）防止數(shù)據(jù)庫(kù)中表的細(xì)節(jié)暴露給用戶。（3）提供安全機(jī)制。（4）改進(jìn)性能。（5）減少網(wǎng)絡(luò)流量。第4頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5410．1．3存儲(chǔ)過(guò)程的類型存儲(chǔ)過(guò)程分為三類：系統(tǒng)提供的存儲(chǔ)過(guò)程系統(tǒng)提供的存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程的名字都以“sp_”為前綴例：sp_bindefault，sp_bindrule，sp_help，sp_helpdb,sp_helpindex等用戶定義的存儲(chǔ)過(guò)程用戶定義的存儲(chǔ)過(guò)程是由用戶為完成某一特定功能而編寫的存儲(chǔ)過(guò)程。存儲(chǔ)在創(chuàng)建時(shí)的數(shù)據(jù)庫(kù)中。擴(kuò)展存儲(chǔ)過(guò)程：擴(kuò)展存儲(chǔ)過(guò)程是用來(lái)調(diào)用操作系統(tǒng)提供的功能。第5頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/55系統(tǒng)存儲(chǔ)過(guò)程說(shuō)明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫(kù)。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息sp_renamedb更改數(shù)據(jù)庫(kù)的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對(duì)象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲(chǔ)過(guò)程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲(chǔ)過(guò)程、用戶定義的存儲(chǔ)過(guò)程、觸發(fā)器或視圖的實(shí)際文本。第6頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/56EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

修改數(shù)據(jù)庫(kù)的名稱(單用戶訪問(wèn))列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫(kù)當(dāng)前數(shù)據(jù)庫(kù)中查詢的對(duì)象的列表返回某個(gè)表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語(yǔ)句文本查看當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程演示：常用的存儲(chǔ)過(guò)程第7頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5710.2建立和執(zhí)行存儲(chǔ)過(guò)程簡(jiǎn)單存儲(chǔ)過(guò)程類似于將一組SQL語(yǔ)句起個(gè)名字，然后就可以在需要時(shí)反復(fù)調(diào)用。復(fù)雜一些的則要有輸入和輸出參數(shù)。10．2．1創(chuàng)建和執(zhí)行簡(jiǎn)單存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程的基本語(yǔ)法如下：CREATEPROC存儲(chǔ)過(guò)程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語(yǔ)句其中各參數(shù)如下：[WITHENCRYPTION]：對(duì)存儲(chǔ)過(guò)程進(jìn)行加密。[WITHRECOMPILE]：對(duì)存儲(chǔ)過(guò)程重新編譯。第8頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/58【例10.1】創(chuàng)建一個(gè)無(wú)參數(shù)的存儲(chǔ)過(guò)程，在SALES數(shù)據(jù)庫(kù)中,創(chuàng)建存儲(chǔ)過(guò)程xs,查詢銷售編號(hào),商品名稱,數(shù)量。CREATEPROCEDURExsASSELECT銷售編號(hào),商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號(hào)=sell.商品編號(hào)第9頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5910．2．2存儲(chǔ)過(guò)程的執(zhí)行執(zhí)行存儲(chǔ)過(guò)程的基本語(yǔ)法如下：EXEC[UTE]存儲(chǔ)過(guò)程名同時(shí)EXECUTE命令除了可以執(zhí)行存儲(chǔ)過(guò)程外還可以執(zhí)行存放Transact-SQL語(yǔ)句的字符串變量，或直接執(zhí)行Transact-SQL語(yǔ)句字符串。此時(shí)EXECUTE語(yǔ)句的語(yǔ)法格式如下。EXECUTE({@字符串變量|[N]'SQL語(yǔ)句字符串'}[+...n]其中“@字符串變量”是局部字符串變量名，最大值為服務(wù)器的可用內(nèi)存。[N]'SQL語(yǔ)句字符串'的語(yǔ)句字符串是一個(gè)由SQL語(yǔ)句構(gòu)成的字符串常量。如果包含N，則該字符串將解釋為nvarchar數(shù)據(jù)類型。EXECxs第10頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/510【例10.2】建立一個(gè)批處理，查詢相應(yīng)表中的信息。DECLARE@tab_namevarchar(20)SET@tab_name='xs'EXECUTE('SELECT*FROM'+@tab_name)第11頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/51110．2．3帶輸入?yún)?shù)的存儲(chǔ)過(guò)程1．建立存儲(chǔ)過(guò)程一個(gè)存儲(chǔ)過(guò)程可以帶一個(gè)或多個(gè)參數(shù)，輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過(guò)程傳遞的參數(shù)，它們?cè)趧?chuàng)建存儲(chǔ)過(guò)程語(yǔ)句中被定義，在執(zhí)行存儲(chǔ)過(guò)程中給出相應(yīng)的參數(shù)值。聲明帶輸入?yún)?shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CREATEPROCEDURE存儲(chǔ)過(guò)程名@參數(shù)名數(shù)據(jù)類型[=默認(rèn)值][，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語(yǔ)句第12頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/512其中“@參數(shù)名”和定義局部變量一樣，必須以符號(hào)@為前綴，要指定數(shù)據(jù)類型，多個(gè)參數(shù)定義要用“，”隔開(kāi)。在執(zhí)行存儲(chǔ)過(guò)程時(shí)該參數(shù)將由指定的參數(shù)值來(lái)代替，如果執(zhí)行時(shí)未提供該參數(shù)的參數(shù)值，則使用時(shí)須定義默認(rèn)值（默認(rèn)值可以是常量或空值null），否則將產(chǎn)生錯(cuò)誤。第13頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/513

【例10.3】創(chuàng)建一個(gè)帶輸入?yún)?shù)的存儲(chǔ)過(guò)程PROC_GOODS,查詢指定員工所進(jìn)商品信息CREATEPROCproc_goods@員工編號(hào)char(6)=‘1001’AsSelect商品編號(hào),商品名稱,生產(chǎn)廠商,進(jìn)貨價(jià),零售價(jià),數(shù)量,進(jìn)貨時(shí)間fromgoodsWhere進(jìn)貨員工編號(hào)=@員工編號(hào)

第14頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5142．執(zhí)行存儲(chǔ)過(guò)程在執(zhí)行存儲(chǔ)過(guò)程的語(yǔ)句中，有兩種方式傳遞參數(shù)值，分別是使用參數(shù)名傳遞參數(shù)值和按參數(shù)位置傳遞參數(shù)值。使用參數(shù)名傳遞參數(shù)值，是通過(guò)語(yǔ)句“@參數(shù)名=參數(shù)值”給參數(shù)傳遞值。當(dāng)存儲(chǔ)過(guò)程含有多個(gè)輸入?yún)?shù)時(shí)，對(duì)數(shù)值可以按任意順序給出，對(duì)于允許空值和具有默認(rèn)值的輸入?yún)?shù)可以不給參數(shù)值，其語(yǔ)法格式為：EXECUTE存儲(chǔ)過(guò)程名[@參數(shù)名=參數(shù)值][，…n]按參數(shù)位置傳遞參數(shù)值，不顯式地給出“@參數(shù)名”，而是按照參數(shù)定義的順序給出參數(shù)值。按位置傳遞參數(shù)時(shí)，也可以忽略允許為空值和有默認(rèn)值的參數(shù)，但不能因此破壞輸入?yún)?shù)的指定順序。必要時(shí)使用關(guān)鍵字“DEFAULT”作為參數(shù)值的占位。EXECproc_goods@員工編號(hào)=‘1002’或EXECproc_goods‘1002’第15頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/51510．2．4帶輸出參數(shù)的存儲(chǔ)過(guò)程如果我們需要從存儲(chǔ)過(guò)程中返回一個(gè)或多個(gè)值，可以通過(guò)在創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句中定義輸出參數(shù)來(lái)實(shí)現(xiàn)，為了使用輸出參數(shù)，需要在創(chuàng)建存儲(chǔ)過(guò)程的命令中使用OUTPUT關(guān)鍵字。聲明帶輸出參數(shù)的存儲(chǔ)過(guò)程的語(yǔ)法格式如下：CREATEPROCEDURE存儲(chǔ)過(guò)程名@參數(shù)名數(shù)據(jù)類型[VARYING][=默認(rèn)值]OUTPUT[，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語(yǔ)句第16頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/516注意：OUTPUT變量必須在定義存儲(chǔ)過(guò)程和使用該變量時(shí)都進(jìn)行定義。定義時(shí)的參數(shù)名和調(diào)用時(shí)的變量名不一定相同，不過(guò)數(shù)據(jù)類型和參數(shù)的位置必須匹配。第17頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/517【例10.5】創(chuàng)建一個(gè)帶有輸入和輸出函數(shù)的存儲(chǔ)過(guò)程proc_gno,查詢指定廠商指定名稱的商品對(duì)應(yīng)的商品編號(hào)CREATEPROCproc_gno@商品名稱varchar(20),@生產(chǎn)廠商varchar(30),@商品編號(hào)intoutputAsSelect@商品編號(hào)=商品編號(hào)FromgoodsWhere商品名稱=@商品名稱and生產(chǎn)廠商=@生產(chǎn)廠商

第18頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/518執(zhí)行存儲(chǔ)過(guò)程Declare@商品編號(hào)intExecproc_gno‘打印機(jī)’,’惠普公司’,@商品編號(hào)outputPrint‘該商品編號(hào)為:’+cast(@商品編號(hào)aschar(6))第19頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/51910.3存儲(chǔ)過(guò)程的管理與維護(hù)10．3．1查看存儲(chǔ)過(guò)程的定義信息在SQLServerManagementStudio的“對(duì)象資源管理器”中，可以在要查看信息的存儲(chǔ)過(guò)程上單擊鼠標(biāo)右鍵，在快捷菜單中選擇“屬性”，彈出“存儲(chǔ)過(guò)程屬性”窗口.也可以通過(guò)系統(tǒng)存儲(chǔ)過(guò)程sp_helptext查看存儲(chǔ)過(guò)程的定義；通過(guò)sp_help查看存儲(chǔ)過(guò)程的參數(shù)；通過(guò)sp_depends查看存儲(chǔ)過(guò)程的相關(guān)性。第20頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/520【例10.7】在SQLServerManagementStudio服務(wù)器中新建查詢，使用系統(tǒng)存儲(chǔ)過(guò)程，查看例10.5中所創(chuàng)建存儲(chǔ)過(guò)程的定義、參數(shù)和相關(guān)性。EXECUTEsp_helptextproc_gnoEXECUTEsp_helpproc_gnoEXECUTEsp_dependsproc_gno第21頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/52110．3．2存儲(chǔ)過(guò)程的重編譯存儲(chǔ)過(guò)程所采用的執(zhí)行計(jì)劃，只在編譯時(shí)優(yōu)化生成，以后便駐留在高速緩存中。當(dāng)用戶對(duì)數(shù)據(jù)庫(kù)新增了索引或其他影響數(shù)據(jù)庫(kù)邏輯結(jié)構(gòu)的更改后，已編譯的存儲(chǔ)過(guò)程執(zhí)行計(jì)劃可能會(huì)失去效率。通過(guò)對(duì)存儲(chǔ)過(guò)程進(jìn)行重新編譯，可以重新優(yōu)化存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃。SQLServer為用戶提供了3種重新編譯的方法。第22頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5221．在創(chuàng)建存儲(chǔ)過(guò)程時(shí)設(shè)定在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，使用WITHRECOMPILE子句時(shí)SQLServer不將該存儲(chǔ)過(guò)程的查詢計(jì)劃保存在緩存中，而是在每次運(yùn)行時(shí)重新編譯和優(yōu)化，并創(chuàng)建新的執(zhí)行計(jì)劃。2．在執(zhí)行存儲(chǔ)過(guò)程時(shí)設(shè)定通過(guò)在執(zhí)行存儲(chǔ)過(guò)程時(shí)設(shè)定重新編譯，可以讓SQLServer在執(zhí)行存儲(chǔ)過(guò)程時(shí)重新編譯該存儲(chǔ)過(guò)程，這一次執(zhí)行完成后，新的執(zhí)行計(jì)劃又被保存在緩存中。這樣用戶就可以根據(jù)需要進(jìn)行重新編譯。EXEC存儲(chǔ)過(guò)程名RECOMPILE3．通過(guò)系統(tǒng)存儲(chǔ)過(guò)程設(shè)定重編譯通過(guò)系統(tǒng)存儲(chǔ)過(guò)程sp_recompile設(shè)定重新編譯標(biāo)記，使存儲(chǔ)過(guò)程在下次運(yùn)行時(shí)重新編譯。其語(yǔ)法格式如下：EXECsp_recompile數(shù)據(jù)庫(kù)對(duì)象第23頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/52310．3．3修改和刪除存儲(chǔ)過(guò)程1．修改存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的修改是由ALTER語(yǔ)句來(lái)完成的，基本語(yǔ)法如下：ALTERPROCEDURE存儲(chǔ)過(guò)程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語(yǔ)句第24頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/524【例10.7】修改例10.1的存儲(chǔ)過(guò)程,對(duì)其進(jìn)行加密alterPROCEDURExsWITHENCRYPTIONASSELECT銷售編號(hào),商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號(hào)=sell.商品編號(hào)第25頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5252．刪除存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程的刪除是通過(guò)DROP語(yǔ)句來(lái)實(shí)現(xiàn)的，在SQLServerManagementStudio的“對(duì)象資源管理器”中也同樣可以進(jìn)行刪除。命令方式刪除存儲(chǔ)過(guò)程的方法也很簡(jiǎn)單.語(yǔ)法格式:DROPPROCEDURE存儲(chǔ)過(guò)程名【例10.8】刪除例10.1中創(chuàng)建的存儲(chǔ)過(guò)程alterPROCEDURExs第26頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/526張三李四王五趙二王三宋二劉五插入刪除觸發(fā)器觸發(fā)趙二退休

趙二員工表退休員工表觸發(fā)器是在對(duì)表進(jìn)行插入、更新或刪除操作時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程觸發(fā)器通常用于強(qiáng)制業(yè)務(wù)規(guī)則觸發(fā)器是一種高級(jí)約束，可以定義比用CHECK約束更為復(fù)雜的約束可執(zhí)行復(fù)雜的SQL語(yǔ)句（if/while/case）可引用其它表中的列觸發(fā)器定義在特定的表上，與表相關(guān)自動(dòng)觸發(fā)執(zhí)行不能直接調(diào)用是一個(gè)事務(wù)（可回滾）第27頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/527張三開(kāi)戶1000元,李四開(kāi)戶1元

10.4觸發(fā)器的概念10．4．1基本概念2、為什么需要觸發(fā)器為什么需要觸發(fā)器(TRIGGER)呢？典型的應(yīng)用就是銀行的取款機(jī)系統(tǒng)帳戶信息表bank交易信息表transInfo張三取錢200問(wèn)題：沒(méi)有自動(dòng)修改張三的余額最優(yōu)的解決方案就是采用觸發(fā)器：它是一種特殊的存儲(chǔ)過(guò)程也具備事務(wù)的功能它能在多表之間執(zhí)行特殊的業(yè)務(wù)規(guī)則第28頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/52810．4．2觸發(fā)器的功能SQLServer2005提供了兩種方法來(lái)保證數(shù)據(jù)的有效性和完整性：約束和觸發(fā)器。觸發(fā)器的常用功能如下。（1）完成更復(fù)雜的數(shù)據(jù)約束：觸發(fā)器可以實(shí)現(xiàn)比約束更為復(fù)雜的數(shù)據(jù)約束。（2）檢查SQL所做的操作是否允許：觸發(fā)器可以檢查SQL所做的操作是否被允許。第29頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/529（3）修改其他數(shù)據(jù)表里的數(shù)據(jù)：當(dāng)一個(gè)SQL語(yǔ)句對(duì)數(shù)據(jù)表進(jìn)行操作的時(shí)候，觸發(fā)器可以根據(jù)SQL語(yǔ)句的操作情況來(lái)對(duì)另一個(gè)數(shù)據(jù)表進(jìn)行操作。（4）調(diào)用更多的存儲(chǔ)過(guò)程：約束是不能調(diào)用存儲(chǔ)過(guò)程的，但觸發(fā)器本身就是一種存儲(chǔ)過(guò)程，而存儲(chǔ)過(guò)程是可以嵌套調(diào)用的，所以觸發(fā)器也可能調(diào)用一個(gè)或多個(gè)存儲(chǔ)過(guò)程。（5）返回自定義的錯(cuò)誤信息：約束只能通過(guò)標(biāo)準(zhǔn)的系統(tǒng)錯(cuò)誤信息來(lái)傳遞錯(cuò)誤信息，如果應(yīng)用程序要求使用自定義信息和較為復(fù)雜的錯(cuò)誤處理，則必須使用觸發(fā)器。（6）更改原本要操作的SQL語(yǔ)句：觸發(fā)器可以修改原本要操作的SQL語(yǔ)句。（7）防止數(shù)據(jù)表結(jié)構(gòu)更改或數(shù)據(jù)表被刪除：為了保護(hù)已經(jīng)建立好的數(shù)據(jù)表，觸發(fā)器可以在接收到以DROP或ALTER開(kāi)頭的語(yǔ)句后，不對(duì)數(shù)據(jù)表的結(jié)構(gòu)做任何操作。第30頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/53010．4．3觸發(fā)器的類型1.DML觸發(fā)器DML觸發(fā)器是當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)操縱語(yǔ)言DML事件時(shí)執(zhí)行的特殊存儲(chǔ)過(guò)程，如INSERT、UPDATE等。2.DDL觸發(fā)器DDL觸發(fā)器是當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)定義語(yǔ)言DDL事件時(shí)執(zhí)行的特殊存儲(chǔ)過(guò)程，如CREATE、ALTER等。第31頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/53110.5創(chuàng)建和應(yīng)用DML觸發(fā)器10．5．1DML觸發(fā)器的類型1．AFTER觸發(fā)器這類觸發(fā)器是在記錄已經(jīng)改變之后，才會(huì)被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查，一旦發(fā)現(xiàn)錯(cuò)誤，也可以用ROLLBACKTRANSACTION語(yǔ)句來(lái)回滾本次的操作。2．INSTEADOF觸發(fā)器這類觸發(fā)器一般是用來(lái)取代原本要進(jìn)行的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來(lái)的SQL語(yǔ)句里的操作，而是去執(zhí)行觸發(fā)器本身所定義的操作。第32頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/53210．5．2DML觸發(fā)器的工作原理在SQLServer2005中，為每個(gè)DML觸發(fā)器定義了兩個(gè)特殊的表，一個(gè)是Inserted表，一個(gè)是Deleted表。

修改操作inserted表deleted表增加(INSERT)記錄存放新增的記錄------刪除(DELETE)記錄-----存放被刪除的記錄修改(UPDATE)記錄存放更新后的記錄存放更新前的記錄第33頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/533Inserted表里存放的是更新前的記錄：對(duì)于插入記錄操作來(lái)說(shuō)，Inserted表里存儲(chǔ)的是要插入的數(shù)據(jù)；對(duì)于更新記錄的操作來(lái)說(shuō)，Inserted表里存放的是要更新的記錄。Deleted表里存放的是更新后的記錄：對(duì)于更新記錄操作來(lái)說(shuō)，Deleted表里存放的是更新前的記錄；對(duì)于刪除記錄操作來(lái)說(shuō)，Deleted表里存儲(chǔ)的是被刪除的舊記錄。第34頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/53410．5．3創(chuàng)建DML觸發(fā)器的注意事項(xiàng)（1）CREATETRIGGER語(yǔ)句必須是批處理中的第一個(gè)語(yǔ)句，該語(yǔ)句后面的所有語(yǔ)句都被解釋為CREATETRIGGER語(yǔ)句定義的一部分。（2）創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者，且不能將該權(quán)限轉(zhuǎn)授給其他用戶。（3）DML可以引用當(dāng)前數(shù)據(jù)庫(kù)以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建DML觸發(fā)器。（4）不能對(duì)系統(tǒng)表或臨時(shí)表創(chuàng)建DML觸發(fā)器（5）對(duì)于含有DELETE或UPDATE操作定義的外鍵表，不能定義INSTEADOF觸發(fā)器。第35頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/53510．5．3創(chuàng)建觸發(fā)器CREATETRIGGER觸發(fā)器名ON{表|視圖}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][…n]}]SQL語(yǔ)句第36頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/536（1）WITHENCRYPTION。加密CREATETRIGGER語(yǔ)句文本的條目。（2）FOR|AFTER。FOR與AFTER同義，指定觸發(fā)器只有在觸發(fā)器SQL語(yǔ)句中指定的所有操作都已成功后才激發(fā)。所有的引用級(jí)聯(lián)操作和約束檢查也必須成功完成后，才能執(zhí)行此觸發(fā)器，即為后觸發(fā)。（3）INSTEADOF。指定執(zhí)行觸發(fā)器而不執(zhí)行造成觸發(fā)的SQL語(yǔ)句，從而替代造成觸發(fā)的語(yǔ)句。在表或視圖上，每個(gè)INSERT、UPDATE或DELETE語(yǔ)句只能定義一個(gè)INSTEADOF觸發(fā)器，即替代觸發(fā)。（4）[INSERT][,][UPDATE][,][DELETE]。是指定在表上執(zhí)行哪些數(shù)據(jù)修改語(yǔ)句時(shí)將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用任意順序組合的這些關(guān)鍵字。當(dāng)進(jìn)行觸發(fā)條件的操作時(shí)（INSERT、UPDATE或DELETE），將執(zhí)行SQL語(yǔ)句中指定的觸發(fā)器操作。第37頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/537（5）NOTFORREPLICATION。表示當(dāng)復(fù)制進(jìn)程更改觸發(fā)器所涉及的表時(shí)，不要執(zhí)行該觸發(fā)器。（6）IFUPDATE(列名)。測(cè)試在指定的列上進(jìn)行的INSERT或UPDATE操作，不能用于DELETE操作，可以指定多列。因?yàn)橐呀?jīng)在ON子句中指定了表名，所以在IFUPDATE子句中的列名前不要包含表名。若要測(cè)試在多個(gè)列上進(jìn)行的INSERT或UPDATE操作，要分別單獨(dú)地指定UPDATE(列名)子句。在INSERT操作中IFUPDATE將返回TRUE值。第38頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5381．UPDATE觸發(fā)器【例10.12】創(chuàng)建一個(gè)觸發(fā)器,當(dāng)有人試圖更新SALES數(shù)據(jù)庫(kù)中g(shù)oods表的商品編號(hào)或進(jìn)貨員工編號(hào)時(shí),利用觸發(fā)器產(chǎn)生提示信息.并取消修改操作.--創(chuàng)建觸發(fā)器CREATETRIGGERupdate_trigONgoodsFORupdateASIFUPDATE(商品編號(hào))ORUPDATE(進(jìn)貨員工編號(hào))BEGINRAISERROR('商品編號(hào)或進(jìn)貨員工編號(hào)不能進(jìn)行修改!',7,2)ROLLBACKTRANSACTIONEND第39頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/539--測(cè)試觸發(fā)器UpdategoodsSet商品編號(hào)=120Where數(shù)量=8第40頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5402．INSERT觸發(fā)器【例10.11】創(chuàng)建一個(gè)AFTERINSERT觸發(fā)器,當(dāng)在SALES數(shù)據(jù)庫(kù)的employees表中插入一條新員工記錄時(shí),如果不是”采購(gòu)部”,”財(cái)務(wù)部”,”銷售部”或”庫(kù)存部”的員工,則撤消該插入操作,并返回出錯(cuò)消息.--創(chuàng)建觸發(fā)器第41頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/541CREATETRIGGERinsert_trigONemployeesFORinsertASDeclare@departvarchar(16)Select@depart=employees.部門fromemployees,insertedWhereemployees.編號(hào)=inserted.編號(hào)If@depart<>'采購(gòu)部'or@depart<>'財(cái)務(wù)部'or@depart<>'銷售部'or@depart<>'庫(kù)存部'BEGINROLLBACKTRANSACTIONRAISERROR('不能插入非本公司設(shè)定部門的員工信息!',16,10)END第42頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/542--測(cè)試觸發(fā)器Insertemployess(編號(hào),姓名,性別,部門,電話,地址)Values(‘1511’,’楊過(guò)’,’人事部’,’1201200’,’.南昌市廣場(chǎng)南路156號(hào)’)第43頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5433．DELETE觸發(fā)器【例10.13】在sales數(shù)據(jù)庫(kù)的emplyoees表和sell表之間具有邏輯上的主外鍵關(guān)系,要求當(dāng)刪除或更新員工記錄的時(shí)候,要觸發(fā)觸發(fā)器update_delete_trig,在sell表中也刪除或更新相對(duì)應(yīng)的記錄行。--創(chuàng)建觸發(fā)器第44頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/544CREATETRIGGERupdate_delete_trigONemployeesFORupdate,deleteASDeclare@delcountintDeclare@empidchar(6)--更新Ifupdate(編號(hào))BeginUpdatesellSet售貨員工編號(hào)=(select編號(hào)frominserted)Where售貨員工編號(hào)in(select編號(hào)fromdeleted)End第45頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/545--刪除Select@delcount=count(*)fromdeletedIf@delcount>0BeginSelect@empid=編號(hào)fromdeletedDeletefromsellwhere售貨員工編號(hào)=@empidEND第46頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5466．觸發(fā)器案例分析1(1)問(wèn)題：解決上述的銀行取款問(wèn)題：當(dāng)向交易信息表(transInfo)中插入一條交易信息時(shí)，我們應(yīng)自動(dòng)更新對(duì)應(yīng)帳戶的余額。(2)分析：在交易信息表上創(chuàng)建INSERT觸發(fā)器從inserted臨時(shí)表中獲取插入的數(shù)據(jù)行根據(jù)交易類型(transType)字段的值是存入/支取，增加/減少對(duì)應(yīng)帳戶的余額。第47頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/547CREATETRIGGERtrig_transInfoONtransInfo

FORINSERT

ASDECLARE@typechar(4),@outMoneyMONEYDECLARE@myCardIDchar(10),@balanceMONEYSELECT@type=transType,@outMoney=transMoney，@myCardID=cardIDFROMinsertedIF(@type='支取')UPDATEbankSETcurrentMoney=currentMoney-@outMoneyWHEREcardID=@myCardIDELSEUPDATEbankSETcurrentMoney=currentMoney+@outMoneyWHEREcardID=@myCardID…..GO從inserted表中獲取交易類型、交易金額等根據(jù)交易類型，減少或增加對(duì)應(yīng)卡號(hào)的余額(3)實(shí)現(xiàn)方法第48頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5486．觸發(fā)器案例分析2如果你是圖書管數(shù)據(jù)庫(kù)管理員,現(xiàn)在有Student學(xué)生表和BorrowRecord學(xué)生借書記錄表,現(xiàn)有兩需求要你去實(shí)現(xiàn)其功能:(1)、如果我更改了學(xué)生的學(xué)號(hào),我希望他的借書記錄仍然與這個(gè)學(xué)生相關(guān)(也就是同時(shí)更改借書記錄表的學(xué)號(hào))(2)、如果該學(xué)生已經(jīng)畢業(yè)，我希望刪除他的學(xué)號(hào)的同時(shí)，也刪除它的借書記錄。

第49頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/549分析如何實(shí)現(xiàn)功能1:A在哪張表中創(chuàng)建觸發(fā)器?STUDENTB通過(guò)什么事件觸發(fā)?UPDATEC事件觸發(fā)后所要做的事情?ifUpdate(StudentID)BEIGINUpdateBorrowRecord

SetStudentID=i.StudentID

FromBorrowRecordbr,Deleted

d,Insertedi

END第50頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/550分析如何實(shí)現(xiàn)功能2:A在哪張表中創(chuàng)建觸發(fā)器?STUDENTB通過(guò)什么事件觸發(fā)?deleteC事件觸發(fā)后所要做的事情?

DeleteBorrowRecord

FromBorrowRecordbr,Deltedd

Wherebr.StudentID=d.StudentID

第51頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5515．INSTEADOF觸發(fā)器如果視圖的數(shù)據(jù)來(lái)自于多個(gè)基表，則必須使用INSTAEDOF觸發(fā)器支持引用表中的數(shù)據(jù)的插入、更新和刪除操作。如果視圖的列為以下幾種情況之一：基表中的計(jì)算列基表中的標(biāo)識(shí)列具有timestamp數(shù)據(jù)類型的基表列該視圖的INSERT語(yǔ)句必須為這些列指定值，INSTEADOF觸發(fā)器在構(gòu)成將值插入基表的INSERT語(yǔ)句時(shí)，會(huì)忽略指定的值。第52頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/55210.6觸發(fā)器的管理與維護(hù)10．6．1查看觸發(fā)器的定義信息10．6．2修改和刪除觸發(fā)器1．修改觸發(fā)器ALTERTRIGGER觸發(fā)器名ON{表|視圖}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[NOTFORREPLICATION]AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][…n]}]SQL語(yǔ)句第53頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/553【例10.13】修改例10.12的觸發(fā)器,對(duì)其進(jìn)行加密.ALTERTRIGGERupdate_trigONgoodsWITHENCRYPTIONFORupdateASIFUPDATE(商品編號(hào))ORUPDATE(進(jìn)貨員工編號(hào))BEGINRAISERROR('商品編號(hào)或進(jìn)貨員工編號(hào)不能進(jìn)行修改!',7,2)ROLLBACKTRANSACTIONEND第54頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/5542．刪除觸發(fā)器使用DROPTRIGGER<觸發(fā)器名>命令，即可刪除觸發(fā)器?！纠?0.13】刪除例10.12創(chuàng)建的觸發(fā)器DROPTRIGGERupdate_trig第55頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/55510．6．3禁止或啟用觸發(fā)器使用以下語(yǔ)句可以禁用或啟用指定表上的某些觸發(fā)器或所有觸發(fā)器。禁止和啟用觸發(fā)器的語(yǔ)法格式如下。ALTERTABLE表名{ENABLE|DISABLE}TRIGGER{ALL|觸發(fā)器名[,…n]}第56頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/556【例10.14】禁用或啟用例10.11創(chuàng)建的觸發(fā)器--禁用觸發(fā)器ALTERTABLEemployeesDISABLETRIGGERinsert_trig--啟用觸發(fā)器ALTERTABLEemployeesENABLETRIGGERinsert_trig第57頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/55710.7DDL觸發(fā)器一般來(lái)說(shuō)，在以下幾種情況下可以使用DDL觸發(fā)器。（1）防止數(shù)據(jù)庫(kù)架構(gòu)進(jìn)行某些修改。（2）防止數(shù)據(jù)庫(kù)或數(shù)據(jù)表被誤操作而刪除。（3）希望數(shù)據(jù)庫(kù)發(fā)生某種情況以響應(yīng)數(shù)據(jù)庫(kù)架構(gòu)中的更改。（4）要記錄數(shù)據(jù)庫(kù)架構(gòu)的更改或事件。僅在運(yùn)行DDL觸發(fā)器的DDL語(yǔ)句后，DDL觸發(fā)器才會(huì)激發(fā)。DDL觸發(fā)器無(wú)法作為INSTEADOF觸發(fā)器而使用。第58頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/55810．7．1創(chuàng)建DDL觸發(fā)器創(chuàng)建DDL觸發(fā)器的語(yǔ)法格式如下：CREATETRIGGER觸發(fā)器名ON{服務(wù)器|數(shù)據(jù)庫(kù)}[WITHENCRYPTION]{FOR|AFTER}{DDL語(yǔ)句名稱}ASSQL語(yǔ)句第59頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/559【例10.16】建立用于保護(hù)數(shù)據(jù)庫(kù)sales中的數(shù)據(jù)表不被刪除的觸發(fā)器。CREATETRIGGERdis_drop_tableONsalesFORDROP_TABLEASBEGINRAISERROR('對(duì)不起，sales數(shù)據(jù)庫(kù)中的表不能刪除',16,10)END第60頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/56010．7．2查看和修改DDL觸發(fā)器（1）作用在當(dāng)前SQLServer服務(wù)器上的DDL觸發(fā)器所在的位置,選擇所在的SQLServer服務(wù)器上，定位到“服務(wù)器對(duì)象”中的“觸發(fā)器”，在“摘要”對(duì)話框中就可以看到所有作用在當(dāng)前SQLServer服務(wù)器上的DDL觸發(fā)器。（2）作用在當(dāng)前數(shù)據(jù)庫(kù)中的DDL觸發(fā)器所在位置在SQLServer服務(wù)器上，通過(guò)“數(shù)據(jù)庫(kù)”選擇所在的數(shù)據(jù)庫(kù)，然后定位到“可編程性”中的“數(shù)據(jù)庫(kù)觸發(fā)器”，在摘要對(duì)話框中就可以看到所有的當(dāng)前數(shù)據(jù)庫(kù)中的DDL觸發(fā)器。第61頁(yè)，課件共69頁(yè)，創(chuàng)作于2023年2月2023/8/56110.8事務(wù)10．8．1基本概念事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。這一