存儲過程和觸發(fā)器

存儲過程和觸發(fā)器第1頁，課件共69頁，創(chuàng)作于2023年2月本章重點存儲過程的概念、分類存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的概念、分類觸發(fā)器的創(chuàng)建、觸發(fā)DELETED與INSERTED表第2頁，課件共69頁，創(chuàng)作于2023年2月2023/8/52本章難點有參存儲過程的創(chuàng)建、執(zhí)行觸發(fā)器的觸發(fā)時機DELETED與INSERTED表第3頁，課件共69頁，創(chuàng)作于2023年2月2023/8/5310.1存儲過程的概念10．1．1基本概念存儲過程是一組編譯在單個執(zhí)行計劃中的Transact-SQL語句，將一些固定的操作集中起來交給SQLServer數(shù)據(jù)庫服務器完成，以實現(xiàn)某個任務。10．1．2存儲過程的優(yōu)點（1）與其他應用程序共享應用程序邏輯，因而確保了數(shù)據(jù)訪問和修改的一致性。（2）防止數(shù)據(jù)庫中表的細節(jié)暴露給用戶。（3）提供安全機制。（4）改進性能。（5）減少網(wǎng)絡流量。第4頁，課件共69頁，創(chuàng)作于2023年2月2023/8/5410．1．3存儲過程的類型存儲過程分為三類：系統(tǒng)提供的存儲過程系統(tǒng)提供的存儲過程系統(tǒng)存儲過程的名字都以“sp_”為前綴例：sp_bindefault，sp_bindrule，sp_help，sp_helpdb,sp_helpindex等用戶定義的存儲過程用戶定義的存儲過程是由用戶為完成某一特定功能而編寫的存儲過程。存儲在創(chuàng)建時的數(shù)據(jù)庫中。擴展存儲過程：擴展存儲過程是用來調(diào)用操作系統(tǒng)提供的功能。第5頁，課件共69頁，創(chuàng)作于2023年2月2023/8/55系統(tǒng)存儲過程說明sp_databases列出服務器上的所有數(shù)據(jù)庫。sp_helpdb報告有關指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當前環(huán)境下可查詢的對象的列表sp_columns回某個表列的信息sp_help查看某個表的所有信息sp_helpconstraint查看某個表的約束sp_helpindex查看某個表的索引sp_stored_procedures列出當前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實際文本。第6頁，課件共69頁，創(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ù)庫的名稱(單用戶訪問)列出當前系統(tǒng)中的數(shù)據(jù)庫當前數(shù)據(jù)庫中查詢的對象的列表返回某個表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當前數(shù)據(jù)庫中的存儲過程演示：常用的存儲過程第7頁，課件共69頁，創(chuàng)作于2023年2月2023/8/5710.2建立和執(zhí)行存儲過程簡單存儲過程類似于將一組SQL語句起個名字，然后就可以在需要時反復調(diào)用。復雜一些的則要有輸入和輸出參數(shù)。10．2．1創(chuàng)建和執(zhí)行簡單存儲過程創(chuàng)建存儲過程的基本語法如下：CREATEPROC存儲過程名[WITHENCRYPTION][WITHRECOMPILE]ASSQL語句其中各參數(shù)如下：[WITHENCRYPTION]：對存儲過程進行加密。[WITHRECOMPILE]：對存儲過程重新編譯。第8頁，課件共69頁，創(chuàng)作于2023年2月2023/8/58【例10.1】創(chuàng)建一個無參數(shù)的存儲過程，在SALES數(shù)據(jù)庫中,創(chuàng)建存儲過程xs,查詢銷售編號,商品名稱,數(shù)量。CREATEPROCEDURExsASSELECT銷售編號,商品名稱,sell.數(shù)量as銷售數(shù)量FROMgoods,sellWHEREgoods.商品編號=sell.商品編號第9頁，課件共69頁，創(chuàng)作于2023年2月2023/8/5910．2．2存儲過程的執(zhí)行執(zhí)行存儲過程的基本語法如下：EXEC[UTE]存儲過程名同時EXECUTE命令除了可以執(zhí)行存儲過程外還可以執(zhí)行存放Transact-SQL語句的字符串變量，或直接執(zhí)行Transact-SQL語句字符串。此時EXECUTE語句的語法格式如下。EXECUTE({@字符串變量|[N]'SQL語句字符串'}[+...n]其中“@字符串變量”是局部字符串變量名，最大值為服務器的可用內(nèi)存。[N]'SQL語句字符串'的語句字符串是一個由SQL語句構(gòu)成的字符串常量。如果包含N，則該字符串將解釋為nvarchar數(shù)據(jù)類型。EXECxs第10頁，課件共69頁，創(chuàng)作于2023年2月2023/8/510【例10.2】建立一個批處理，查詢相應表中的信息。DECLARE@tab_namevarchar(20)SET@tab_name='xs'EXECUTE('SELECT*FROM'+@tab_name)第11頁，課件共69頁，創(chuàng)作于2023年2月2023/8/51110．2．3帶輸入?yún)?shù)的存儲過程1．建立存儲過程一個存儲過程可以帶一個或多個參數(shù)，輸入?yún)?shù)是指由調(diào)用程序向存儲過程傳遞的參數(shù)，它們在創(chuàng)建存儲過程語句中被定義，在執(zhí)行存儲過程中給出相應的參數(shù)值。聲明帶輸入?yún)?shù)的存儲過程的語法格式如下：CREATEPROCEDURE存儲過程名@參數(shù)名數(shù)據(jù)類型[=默認值][，…n][WITHENCRYPTION][WITHRECOMPILE]ASSQL語句第12頁，課件共69頁，創(chuàng)作于2023年2月2023/8/512其中“@參數(shù)名”和定義局部變量一樣，必須以符號@為前綴，要指定數(shù)據(jù)類型，多個參數(shù)定義要用“，”隔開。在執(zhí)行存儲過程時該參數(shù)將由指定的參數(shù)值來代替，如果執(zhí)行時未提供該參數(shù)的參數(shù)值，則使用時須定義默認值（默認值可以是常量或空值null），否則將產(chǎn)生錯誤。第13頁，課件共69頁，創(chuàng)作于2023年2月2023/8/513

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

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

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

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

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

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

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

SetStudentID=i.StudentID

FromBorrowRecordbr,Deleted

d,Insertedi

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

DeleteBorrowRecord

FromBorrowRecordbr,Deltedd

Wherebr.StudentID=d.StudentID

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