數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（SQL Server ）2.8

數(shù)據(jù)庫(kù)技術(shù)及應(yīng)用（SQLServer）教學(xué)單元2.8第10章T-SQL程序設(shè)計(jì)案例2-10-1圖書(shū)管理存儲(chǔ)過(guò)程的創(chuàng)建與管理案例2-10-2圖書(shū)管理觸發(fā)器的創(chuàng)建與管理案例2-10-3圖書(shū)管理用戶定義函數(shù)的創(chuàng)建與管理數(shù)據(jù)庫(kù)服務(wù)器編程學(xué)習(xí)導(dǎo)航2T-SQL程序設(shè)計(jì)知識(shí)框架3單元2.8T-SQL程序設(shè)計(jì)能力目標(biāo)能夠根據(jù)數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)的功能需求和完整性需求設(shè)計(jì)存儲(chǔ)過(guò)程設(shè)計(jì)觸發(fā)器設(shè)計(jì)用戶定義函數(shù)能夠閱讀并熟練書(shū)寫(xiě)存儲(chǔ)過(guò)程、觸發(fā)器、用戶定義函數(shù)有關(guān)的T-SQL命令（英文）知識(shí)目標(biāo)存儲(chǔ)過(guò)程的概念與編程方法觸發(fā)器的概念與編程方法用戶定義函數(shù)的概念與編程方法存儲(chǔ)過(guò)程、觸發(fā)器和用戶定義函數(shù)有關(guān)的英文4單元2.8T-SQL程序設(shè)計(jì)素質(zhì)目標(biāo)遵循《軟件生存周期過(guò)程》《數(shù)據(jù)庫(kù)語(yǔ)言SQL》等軟件開(kāi)發(fā)國(guó)家標(biāo)準(zhǔn)，培養(yǎng)嚴(yán)謹(jǐn)、嚴(yán)格和規(guī)范的軟件開(kāi)發(fā)職業(yè)素養(yǎng)通過(guò)介紹學(xué)校實(shí)際應(yīng)用的數(shù)據(jù)庫(kù)應(yīng)用軟件“圖書(shū)館集成管理系統(tǒng)”的流通外借出納管理界面，加強(qiáng)對(duì)“案例2-10-2圖書(shū)管理觸發(fā)器的創(chuàng)建與管理”中借書(shū)與還書(shū)觸發(fā)器T-SQL程序設(shè)計(jì)的理解*拓展閱讀10-1圖書(shū)館集成管理系統(tǒng)—應(yīng)用界面35案例2圖書(shū)管理系統(tǒng)案例2-10-1圖書(shū)管理存儲(chǔ)過(guò)程的創(chuàng)建與管理案例2-10-2圖書(shū)管理觸發(fā)器的創(chuàng)建與管理案例2-10-3圖書(shū)管理用戶定義函數(shù)的創(chuàng)建與管理工作任務(wù)單元2.8T-SQL程序設(shè)計(jì)6

單元2.8T-SQL程序設(shè)計(jì)7創(chuàng)建與管理存儲(chǔ)過(guò)程一創(chuàng)建與管理觸發(fā)器二創(chuàng)建與管理用戶定義函數(shù)三一、創(chuàng)建與管理存儲(chǔ)過(guò)程8案例2-10-1圖書(shū)管理存儲(chǔ)過(guò)程的創(chuàng)建與管理根據(jù)圖書(shū)管理系統(tǒng)的功能需求和完整性需求：創(chuàng)建與管理數(shù)據(jù)庫(kù)“Library”的存儲(chǔ)過(guò)程，實(shí)現(xiàn)數(shù)據(jù)操作以及完整性控制。工作任務(wù)一、創(chuàng)建與管理存儲(chǔ)過(guò)程9存儲(chǔ)過(guò)程概述1創(chuàng)建存儲(chǔ)過(guò)程2管理存儲(chǔ)過(guò)程3（一）存儲(chǔ)過(guò)程概述10存儲(chǔ)過(guò)程（StoredProcedure）一組編譯好的、存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器上的和完成特定功能的T-SQL程序，是某數(shù)據(jù)庫(kù)的對(duì)象?？蛻舳藨?yīng)用程序可以通過(guò)調(diào)用（指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)））來(lái)執(zhí)行存儲(chǔ)過(guò)程。1.存儲(chǔ)過(guò)程的概念（一）存儲(chǔ)過(guò)程概述11可以重復(fù)調(diào)用。提高執(zhí)行速度。減少網(wǎng)絡(luò)流量。提供安全機(jī)制。2.使用存儲(chǔ)過(guò)程的優(yōu)點(diǎn)（一）存儲(chǔ)過(guò)程概述12系統(tǒng)存儲(chǔ)過(guò)程（存儲(chǔ)在master數(shù)據(jù)庫(kù)中）前綴：sp_例如sp_rename、sp_help等用戶定義存儲(chǔ)過(guò)程T-SQL存儲(chǔ)過(guò)程：指保存的T-SQL程序，可以接受和返回用戶提供的參數(shù)。存儲(chǔ)過(guò)程也可能從數(shù)據(jù)庫(kù)向客戶端應(yīng)用程序返回?cái)?shù)據(jù)。CLR存儲(chǔ)過(guò)程：指對(duì)Microsoft.NETFramework公共語(yǔ)言運(yùn)行時(shí)方法的引用，可以接受和返回用戶提供的參數(shù)，它們?cè)?NETFramework程序集中是作為類的公共靜態(tài)方法實(shí)現(xiàn)的。3.存儲(chǔ)過(guò)程的分類（一）存儲(chǔ)過(guò)程概述13擴(kuò)展存儲(chǔ)過(guò)程擴(kuò)展存儲(chǔ)過(guò)程是以在SQLServer環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)（DynamicLinkLibraries，DLL）來(lái)實(shí)現(xiàn)的，通常以前綴xp_開(kāi)頭。擴(kuò)展存儲(chǔ)過(guò)程用與存儲(chǔ)過(guò)程相似的方式來(lái)執(zhí)行。3.存儲(chǔ)過(guò)程的分類（二）創(chuàng)建存儲(chǔ)過(guò)程14CREATEPROC[EDURE]過(guò)程名 --創(chuàng)建存儲(chǔ)過(guò)程

[[@形參數(shù)據(jù)類型] --輸入?yún)?shù)

|[@形參數(shù)據(jù)類型=默認(rèn)值] --默認(rèn)值參數(shù)

|[@變參數(shù)據(jù)類型OUTPUT] --輸出參數(shù)（返回值）

][,...n]AS[BEGIN]T-SQL語(yǔ)句 --過(guò)程體（T-SQL程序，返回結(jié)果集或值）[END]使用T-SQL創(chuàng)建存儲(chǔ)過(guò)程的基本語(yǔ)法（二）創(chuàng)建存儲(chǔ)過(guò)程15【例10-1】為數(shù)據(jù)庫(kù)“Library”創(chuàng)建一個(gè)多表查詢的存儲(chǔ)過(guò)程，查詢出讀者為“程鵬”的借閱信息。代碼USELibraryGOCREATEPROCEDUREborrowed_book1ASBEGINSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname='程鵬'END1.無(wú)參存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程16執(zhí)行結(jié)果在數(shù)據(jù)庫(kù)“Library”中創(chuàng)建了存儲(chǔ)過(guò)程“borrowed_book1”調(diào)用borrowed_book1或EXECborrowed_book1返回結(jié)果1.無(wú)參存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程17【例10-2】為數(shù)據(jù)庫(kù)“Library”創(chuàng)建一個(gè)查詢某讀者（姓名在執(zhí)行存儲(chǔ)過(guò)程時(shí)給出）借閱情況的存儲(chǔ)過(guò)程。代碼USELibraryGO--輸入形式參數(shù)@nameCREATEPROCEDUREborrowed_book2@namechar(8)ASBEGINSELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname=@nameEND2.帶輸入?yún)?shù)的存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程18調(diào)用（常量傳值）EXECborrowed_book2'楊淑華'--實(shí)參表'楊淑華'或DECLARE@temp1char(8)SET@temp1='楊淑華'EXECborrowed_book2@temp1--實(shí)參表@temp1返回結(jié)果2.帶輸入?yún)?shù)的存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程19【例10-3】為數(shù)據(jù)庫(kù)“Library”創(chuàng)建使用默認(rèn)值參數(shù)查詢某讀者（姓名在執(zhí)行存儲(chǔ)過(guò)程時(shí)給出）借閱情況的存儲(chǔ)過(guò)程。代碼CREATEPROCEDUREborrowed_book3@namechar(8)=NULL--默認(rèn)參數(shù)ASBEGINIF@nameISNULL--@name為默認(rèn)值NULL，IF條件成立SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDELSESELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDateFROMReaderrINNERJOINBorrowbONr.RID=b.RIDINNERJOINBookkONb.BID=k.BIDWHERERname=@nameEND3.帶默認(rèn)值參數(shù)的存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程20調(diào)用EXECborrowed_book3返回結(jié)果2.使用T-SQL創(chuàng)建存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程21【例10-4】為數(shù)據(jù)庫(kù)“Library”創(chuàng)建一個(gè)查詢某出版社圖書(shū)總價(jià)值和平均價(jià)值的存儲(chǔ)過(guò)程。代碼USELibraryGOCREATEPROCEDUREbook_price--創(chuàng)建存儲(chǔ)過(guò)程@Publishervarchar(30),--輸入形式參數(shù)@SUMPricedecimal(9,2)OUTPUT,--輸出形式參數(shù)1@AVGPricedecimal(9,2)OUTPUT--輸出形式參數(shù)2AS4．帶輸出參數(shù)的存儲(chǔ)過(guò)程（二）創(chuàng)建存儲(chǔ)過(guò)程22【例10-4】為數(shù)據(jù)庫(kù)“Library”創(chuàng)建一個(gè)查詢某出版社圖書(shū)總價(jià)值和平均價(jià)值的存儲(chǔ)過(guò)程。代碼續(xù)BEGIN

--輸出參數(shù)1得到圖書(shū)總價(jià)值，賦值語(yǔ)句SELECT@SUMPrice=SUM(price)FROMBookWHEREPublisher=@Publisher

--輸出參數(shù)2得到圖書(shū)平均價(jià)值，賦值語(yǔ)句SELECT@AVGPrice=AVG(price)FROMBookWHEREPublisher=@PublisherEND（二）創(chuàng)建存儲(chǔ)過(guò)程23調(diào)用DECLARE@chvarchar(30),@ou1decimal(9,2),@ou2decimal(9,2)SET@ch='人民郵電出版社‘GOEXECbook_price--調(diào)用存儲(chǔ)過(guò)程@ch,@ou1Output,@ou2Output--輸入實(shí)參，輸出實(shí)參1，2GOSELECT@chAS書(shū)名,@ou1AS總價(jià)值,@ou2AS平均價(jià)值

--輸出變量（實(shí)參）值返回結(jié)果4．帶輸出參數(shù)的存儲(chǔ)過(guò)程（三）管理存儲(chǔ)過(guò)程24修改ALTERPROC[EDURE]過(guò)程名 --修改存儲(chǔ)過(guò)程

[[@形參數(shù)據(jù)類型] --輸入?yún)?shù)

|[@形參數(shù)據(jù)類型=默認(rèn)值] --默認(rèn)值參數(shù)

|[@變參數(shù)據(jù)類型OUTPUT] --輸出參數(shù)

][,...n]AS[BEGIN]T-SQL語(yǔ)句 --過(guò)程主體[END]1．修改存儲(chǔ)過(guò)程（三）管理存儲(chǔ)過(guò)程25基本語(yǔ)法DROPPROC[EDURE]存儲(chǔ)過(guò)程名[,...n]例：刪除數(shù)據(jù)庫(kù)“Library”的存儲(chǔ)過(guò)程“borrowed”。代碼：USELibraryDROPPROCborrowed2．刪除存儲(chǔ)過(guò)程單元2.8T-SQL程序設(shè)計(jì)26創(chuàng)建與管理存儲(chǔ)過(guò)程一創(chuàng)建與管理觸發(fā)器二創(chuàng)建與管理用戶定義函數(shù)三案例2-10-2圖書(shū)管理觸發(fā)器的創(chuàng)建與管理根據(jù)圖書(shū)管理系統(tǒng)的功能需求和完整性需求：創(chuàng)建與管理服務(wù)器、數(shù)據(jù)庫(kù)“Library”及其表或視圖的觸發(fā)器，實(shí)現(xiàn)數(shù)據(jù)操作以及完整性控制。工作任務(wù)二、創(chuàng)建與管理觸發(fā)器27二、創(chuàng)建與管理觸發(fā)器28觸發(fā)器概述1創(chuàng)建DML觸發(fā)器2創(chuàng)建DDL觸發(fā)器3管理觸發(fā)器4（一）觸發(fā)器概述29特殊的存儲(chǔ)過(guò)程，是基于表/視圖/服務(wù)器/數(shù)據(jù)庫(kù)創(chuàng)建的。觸發(fā)器里也包含一系列的T-SQL語(yǔ)句，但它的執(zhí)行不是用EXEC主動(dòng)調(diào)用的，而是在滿足一定條件下自動(dòng)執(zhí)行的。當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)庫(kù)中的數(shù)據(jù)經(jīng)過(guò)操作發(fā)生變化或者當(dāng)服務(wù)器、數(shù)據(jù)庫(kù)中發(fā)生數(shù)據(jù)定義事件時(shí)，系統(tǒng)將自動(dòng)運(yùn)行觸發(fā)器中的程序以保證數(shù)據(jù)庫(kù)的完整性、正確性和安全性。通俗地說(shuō)，觸發(fā)器是基于一個(gè)基表/視圖/服務(wù)器/數(shù)據(jù)庫(kù)，并通過(guò)一個(gè)事件被調(diào)用的存儲(chǔ)過(guò)程。1.觸發(fā)器的概念（一）觸發(fā)器概述30DML觸發(fā)器基于表或視圖設(shè)計(jì)的T-SQL程序DML（INSERT、UPDATE、DELETE語(yǔ)句）觸發(fā)事件調(diào)用自動(dòng)創(chuàng)建臨時(shí)表“inserted”“deleted”作用可以實(shí)現(xiàn)回滾（撤銷）觸發(fā)事件。實(shí)現(xiàn)較為復(fù)雜的數(shù)據(jù)完整性控制。2.觸發(fā)器的分類（一）觸發(fā)器概述31DDL觸發(fā)器DDL（CREATE、ALTER、DROP語(yǔ)句）觸發(fā)事件調(diào)用作用用于管理任務(wù)，例如審核和控制數(shù)據(jù)庫(kù)操作。2.觸發(fā)器的分類（一）觸發(fā)器概述32登錄觸發(fā)器LOGON事件觸發(fā)作用用于控制數(shù)據(jù)庫(kù)服務(wù)器的安全，例如拒絕某登錄名啟動(dòng)的SQLServer登錄嘗試。2.觸發(fā)器的分類（二）創(chuàng)建DML觸發(fā)器33語(yǔ)法CREATETRIGGER<觸發(fā)器名>ON<表名|視圖名>{AFTER--DML語(yǔ)句完成后調(diào)用觸發(fā)器|INSTEADOF}--DML語(yǔ)句執(zhí)行時(shí)被觸發(fā)器所替代[UPDATE][,][INSERT][,][DELETE]--DML觸發(fā)事件AS[BEGIN]T-SQL語(yǔ)句--過(guò)程體（T-SQL程序）[END]使用T-SQL創(chuàng)建DML觸發(fā)器（二）創(chuàng)建DML觸發(fā)器34參數(shù)說(shuō)明AFTER：僅在觸發(fā)T-SQL語(yǔ)句中指定的

INSERT/UPDATE/DELETE語(yǔ)句操作都成功執(zhí)行之后才被執(zhí)行。所有的引用級(jí)聯(lián)操作和約束檢查也必須在執(zhí)行此觸發(fā)器之前成功完成。不能對(duì)視圖定義AFTER觸發(fā)器。INSTEADOF：為表和視圖指定的DML觸發(fā)器用于“替代”引起觸發(fā)器執(zhí)行的T-SQL語(yǔ)句，因此其優(yōu)先級(jí)高于觸發(fā)語(yǔ)句的操作。在表或視圖上，每個(gè)INSERT、UPDATE和DELETE語(yǔ)句最多可以定義一個(gè)INSTEADOF觸發(fā)器。使用T-SQL創(chuàng)建DML觸發(fā)器（二）創(chuàng)建DML觸發(fā)器35臨時(shí)表inserted和deleted說(shuō)明DML觸發(fā)器執(zhí)行時(shí)自動(dòng)創(chuàng)建兩個(gè)臨時(shí)表inserted表和deleted表，觸發(fā)器工作完成后即被刪除。inserted表用于臨時(shí)保存被插入或被更新后的數(shù)據(jù)行副本。在執(zhí)行INSERT或UPDATE語(yǔ)句時(shí)，新的數(shù)據(jù)行被插入到觸發(fā)器表中，同時(shí)也被插入到inserted表中?？梢詮膇nserted表中讀取所插入的數(shù)據(jù)，進(jìn)一步進(jìn)行對(duì)其他表的操作。也可以判斷所插入的數(shù)據(jù)是否滿足完整性規(guī)則，如不滿足則可以回滾（撤消）此操作。使用T-SQL創(chuàng)建DML觸發(fā)器（二）創(chuàng)建DML觸發(fā)器36deleted表用于臨時(shí)保存被刪除或被更新后的數(shù)據(jù)行副本。在執(zhí)行DELETE或UPDATE語(yǔ)句時(shí)，從觸發(fā)器表中刪除數(shù)據(jù)行并傳輸?shù)絛eleted表中?？梢詮膁eleted表中檢查所刪除的數(shù)據(jù)行是否滿足刪除條件，如不滿足則可以回滾（撤消）此操作。執(zhí)行UPDATE語(yǔ)句更新數(shù)據(jù)時(shí)，類似于在刪除之后執(zhí)行插入；首先被刪除的數(shù)據(jù)行被傳輸?shù)絛eleted表中，然后新的數(shù)據(jù)行被插入到inserted表中。使用T-SQL創(chuàng)建DML觸發(fā)器（二）創(chuàng)建DML觸發(fā)器37INSERT事件的AFTER觸發(fā)器是對(duì)指定的表執(zhí)行插入數(shù)據(jù)行語(yǔ)句INSERTINTO...VALUES事件之后被激發(fā)執(zhí)行的一段程序代碼。1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器38【例10-5】讀者借書(shū)時(shí)，對(duì)數(shù)據(jù)庫(kù)“Library”完成以下處理。（1）使用INSERT語(yǔ)句完成借閱表“Borrow”添加借書(shū)信息的操作。添加讀者編號(hào)和圖書(shū)編號(hào)。借期“LendDate”為當(dāng)前系統(tǒng)日期（定義表時(shí)已經(jīng)設(shè)置為默認(rèn)值）。（2）調(diào)用觸發(fā)器T-SQL程序，判斷所借圖書(shū)是否已經(jīng)借出。尚未借出，則：①計(jì)算該書(shū)的應(yīng)還日期“SReturnDate”為借期加限借天數(shù)；②將讀者表“Reader”該讀者的借閱數(shù)量“Lendnum”增加1本；③將圖書(shū)表“Book”該書(shū)是否借出“LentOut”置為真。已經(jīng)借出，則：①撤銷所添加的讀者借書(shū)數(shù)據(jù)；②提示該書(shū)已借出。1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器39CREATETRIGGERT_Borrow--創(chuàng)建觸發(fā)器ONBorrow--基于表“Borrow”AFTERINSERTAS--INSERT操作之后激發(fā)BEGINDECLARE@dzbhchar(10),@tsbhchar(15),@dzlxint,@xjtsint--從添加行副本inserted中查詢出RID并賦值給變量@dzbh--從添加行副本inserted中查詢出BID并賦值給變量@tsbh--從表“Reader”中查詢出添加行讀者的TypeID賦值給變量@dzlxSET@dzbh=(SELECTRIDFROMinserted)SET@tsbh=(SELECTBIDFROMinserted)SET@dzlx=(SELECTTypeIDFROMReaderWHERERID=@dzbh)1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器40--判斷所借圖書(shū)是否已經(jīng)借出IF

EXISTS(SELECT*FROMBookWHEREBID=@tsbhANDLentOut=0)BEGIN--①應(yīng)還日期為借期加限借天數(shù)UPDATEBorrowSETSReturnDate=DATEADD(dd,(SELECTLimitDaysFROMReaderTypeWHERETypeID=@dzlx),LendDate)WHERERID=@dzbhANDBID=@tsbhANDReturnDateISNULL1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器41--②將讀者表Reader中該讀者的借閱數(shù)量Lendnum加1UPDATEReaderSETLendnum=Lendnum+1WHERERID=@dzbh--③將圖書(shū)表Book中該書(shū)是否借出LentOut置為1UPDATEBookSETLentOut=1WHEREBID=@tsbhENDELSE--該圖書(shū)已借出BEGINROLLBACK--回滾數(shù)據(jù)行添加操作PRINT‘該書(shū)已借出’ENDEND1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器42假設(shè)1：執(zhí)行以下INSERT語(yǔ)句完成圖書(shū)借閱的操作INSERTINTOBorrow(RID,BID)VALUES(‘2000186010’,‘TP312/429’)INSERT語(yǔ)句對(duì)借閱表“Borrow”的操作如下讀者編號(hào)列“RID”得到“2000186010”圖書(shū)編號(hào)列“BID”得到“TP312/429”借期列“LendDate”得到定義表時(shí)設(shè)置的系統(tǒng)默認(rèn)值GETDATE()，假設(shè)系統(tǒng)日期為“2023-11-30”1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器43調(diào)用觸發(fā)器“T_Borrow”，判斷結(jié)果為所借書(shū)尚未借出借閱表“Borrow”，應(yīng)還日期列“SReturnDate”得到’2022-02-28'在觸發(fā)器中從表“Reader”中查詢出該讀者的讀者類型（教師），再?gòu)谋怼癛eaderType”中查詢出該讀者類型的限借天數(shù)90，由借閱日期’2023-11-30'加90天得到應(yīng)還日期’2024-02-28'1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器44讀者表“Reader”，讀者編號(hào)為'2000186010'的借閱數(shù)量“Lendnum”增加到11.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器45圖書(shū)表“Book”，圖書(shū)編號(hào)為'TP312/429'的圖書(shū)是否借出列“LendOut”置為1（True）1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器46假設(shè)2：有讀者也要借閱圖書(shū)編號(hào)為TP312/429的圖書(shū)。INSERTINTOBorrow(RID,BID)VALUES(‘2022216117','TP312/429‘)調(diào)用觸發(fā)器“T_Borrow”，判斷結(jié)果為所借書(shū)已經(jīng)借出對(duì)“Borrow”表的INSERT語(yǔ)句回滾，添加信息撤銷。返回執(zhí)行顯示結(jié)果該書(shū)已借出消息3609，級(jí)別16，狀態(tài)1，第1行事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。1.創(chuàng)建AFTERINSERT觸發(fā)器（二）創(chuàng)建DML觸發(fā)器47【例10-6】讀者還書(shū)，對(duì)數(shù)據(jù)庫(kù)“Library”完成以下處理。使用UPDATE語(yǔ)句完成借閱表“Borrow”更新還書(shū)信息操作，還期為當(dāng)前系統(tǒng)日期。使用觸發(fā)器編程判斷還書(shū)是否過(guò)期及以下處理：①如果過(guò)期，計(jì)算過(guò)期天數(shù)；②將讀者表“Reader”中該讀者的借閱數(shù)量“Lendnum”減少1本；③將圖書(shū)表“Book”該書(shū)是否借出“LentOut”置為假。

2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器48USELibraryGOCREATETRIGGERT_Return--創(chuàng)建觸發(fā)器ONBorrow--基于表“Borrow”AFTERUPDATE--在更新操作之后激發(fā)執(zhí)以下語(yǔ)句ASBEGINDECLARE@daysint,@dzbhchar(10),@tsbhchar(9),@hsrqdate

2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器49--從更新行副本inserted中查詢出RID并賦值給變量@dzbh--從更新行副本inserted中查詢出BID并賦值給變量@tsbh--從更新行副本inserted中查詢出ReturnDate并賦值給局部變量@hsrqSET@dzbh=(SELECTRIDFROMinserted)--讀者編號(hào)SET@tsbh=(SELECTBIDFROMinserted) --圖書(shū)編號(hào)SET@hsrq=(SELECTReturnDateFROMinserted)--還書(shū)日期2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器50/*從借閱表中查詢出該讀者的本次還書(shū)日期和應(yīng)還日期之差,DATEDIFF函數(shù)返回ReturnDate?SreturnDate的值，單位為DAY，并賦值給@days*/SELECT@days=DATEDIFF(day,SReturnDate,ReturnDate)FROMBorrowWHERERID=@dzbhANDBID=@tsbhANDReturnDate=@hsrq2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器51/*如果@days小于等于0，即ReturnDate<=SReturnDate，沒(méi)過(guò)期，如果@days大于0，即ReturnDate>SReturnDate，過(guò)期@days天*/IF@days<=0PRINT'沒(méi)有過(guò)期！'ELSEPRINT'過(guò)期'+convert(char(6),@days)+'天'2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器52--將讀者表中該讀者的借閱數(shù)量減1UPDATEReaderSETLendnum=Lendnum-1WHERERID=@dzbh--將圖書(shū)表“Book”中該書(shū)是否借出置為0UPDATEBookSETLentOut=0WHEREBID=@tsbhEND2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器53假設(shè)：執(zhí)行UPDATE…SET語(yǔ)句完成讀者還書(shū)，假設(shè)系統(tǒng)日期為2023-11-30，更新借閱表“Borrow”的代碼為：UPDATEBorrowSETReturnDate=GETDATE()WHERERID=‘2023216008'ANDBID='F275.3/65'ANDReturnDateISNULLUPDATE…SET執(zhí)行結(jié)果更新了表“Borrow”中還書(shū)讀者的還書(shū)日期“ReturnDate”為’2023-11-30'。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器54調(diào)用觸發(fā)器“T_Return”返回結(jié)果過(guò)期3天(1行受影響)對(duì)于讀者表“Reader”，讀者編號(hào)為2023216008的借閱數(shù)量“Lendnum”減1變?yōu)?，從而保證了數(shù)據(jù)的一致性。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器55圖書(shū)表“Book”，圖書(shū)編號(hào)為‘F275.3/65’的圖書(shū)是否借出列“LendOut”置為假（0，'False‘）。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器56【例10-7】刪除某位讀者信息。若要?jiǎng)h除一名讀者要先檢查該讀者是否有書(shū)沒(méi)還，若該讀者還有書(shū)沒(méi)還則不能被刪除。代碼：USELibraryGOCREATETRIGGERT_ReaderDEL--創(chuàng)建觸發(fā)器ONReader --基于表ReaderAFTERDELETE--在刪除后觸發(fā)AS3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器57BEGINDECLARE@LNumint--從刪除的數(shù)據(jù)行的臨時(shí)表中獲得借閱數(shù)量SELECT@LNum=LendnumFROMDELETEDIF@LNum>0 --如果借閱數(shù)量大于0BEGINPRINT'該讀者不能刪除！還有

‘+convert(char(2),@LNum)+’冊(cè)書(shū)未還。'

ROLLBACK --事務(wù)回滾撤銷所刪除的數(shù)據(jù)行ENDELSE--如果借閱數(shù)量不大于0PRINT'該讀者已被刪除！??！'--顯示數(shù)據(jù)行已刪除END3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器58假設(shè)：刪除某讀者。DELETEReaderWHERERID=‘2004216010’執(zhí)行結(jié)果：該讀者不能被刪除！還有1冊(cè)書(shū)未還。事務(wù)在觸發(fā)器中結(jié)束。批處理已中止。查看讀者表：可見(jiàn)該讀者未被刪除。3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器59【例10-8】刪除圖書(shū)保護(hù)。在圖書(shū)館圖書(shū)處理過(guò)程中，不允許隨意刪除表“Book”中的圖書(shū)。創(chuàng)建表“Book”的INSTEADOF觸發(fā)器“T_BookNoDEL”代碼：USELibraryGO4．創(chuàng)建INSTEADOF觸發(fā)器（二）創(chuàng)建DML觸發(fā)器60CREATETRIGGERT_BookNoDEL--創(chuàng)建INSTEAD觸發(fā)器ONBook --基于表BookINSTEADOFDELETE --替代觸發(fā)事件DELETEASBEGINPRINT'圖書(shū)未被刪除！' --顯示數(shù)據(jù)行，圖書(shū)未被刪除END4．創(chuàng)建INSTEADOF觸發(fā)器（二）創(chuàng)建DML觸發(fā)器61假設(shè)：刪除某圖書(shū)。DELETEBookWHEREBID='TP311.138/235‘執(zhí)行結(jié)果：圖書(shū)未被刪除！(1行受影響)查看表“Book”，可見(jiàn)BID為‘TP311.138/235’的圖書(shū)未被刪除。

（4）創(chuàng)建事件的INSTEADOF觸發(fā)器（三）創(chuàng)建DDL觸發(fā)器62基本語(yǔ)法：CREATETRIGGER觸發(fā)器名ON{ALL

SERVER|DATABASE}[WITHENCRYPTION]{AFTER}<事件類型或事件組>[,...n]AS[BEGIN]T-SQL語(yǔ)句[END]CREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器63參數(shù)說(shuō)明ALLSERVER：將DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前服務(wù)器。指定此參數(shù)，則當(dāng)前服務(wù)器中的任何位置上出現(xiàn)事件類型或事件組，就會(huì)激發(fā)該觸發(fā)器。DATABASE：將DDL觸發(fā)器的作用域應(yīng)用于當(dāng)前數(shù)據(jù)庫(kù)。如果指定了此參數(shù)，則只要當(dāng)前數(shù)據(jù)庫(kù)中出現(xiàn)事件類型或事件組，就會(huì)激發(fā)該觸發(fā)器。WITHENCRYPTION：對(duì)CREATETRIGGER語(yǔ)句的文本進(jìn)行加密。CREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器64參數(shù)說(shuō)明事件類型：導(dǎo)致激發(fā)DDL觸發(fā)器的T-SQL語(yǔ)句事件的名稱。例如CREATE_TABLE、ALTER_TABLE、DROP_TABLE

CREATE_PROCEDURE等操作。事件組：預(yù)定義的T-SQL語(yǔ)句事件分組的名稱。執(zhí)行任何屬于事件組的T-SQL語(yǔ)句事件之后，都將激發(fā)DDL觸發(fā)器。T-SQL語(yǔ)句：指定觸發(fā)器所執(zhí)行的T-SQL語(yǔ)句。CREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器65觸發(fā)器對(duì)象位置說(shuō)明在SSMS的【對(duì)象資源管理器】窗口中，服務(wù)器作用域的DDL觸發(fā)器顯示在“服務(wù)器對(duì)象”節(jié)點(diǎn)中；具有數(shù)據(jù)庫(kù)作用域的DDL觸發(fā)器位于相應(yīng)數(shù)據(jù)庫(kù)節(jié)點(diǎn)下的“可編程性”節(jié)點(diǎn)下的“數(shù)據(jù)庫(kù)觸發(fā)器”節(jié)點(diǎn)下。CREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器66【例10-9】使用DDL觸發(fā)器來(lái)防止數(shù)據(jù)庫(kù)“Library”中的任意一個(gè)表被修改或刪除。代碼:USELibraryGOCREATETRIGGERsafety1ONDATABASEFORDROP_TABLE,ALTER_TABLEASBEGINPRINT'要?jiǎng)h除和修改表之前，你必須先禁用觸發(fā)器safety1!'ROLLBACKENDCREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器67執(zhí)行結(jié)果：新建觸發(fā)器“safety1”

。作用：當(dāng)用戶試圖使用DROP或ALTER語(yǔ)句刪除或修改數(shù)據(jù)庫(kù)“Library”中的表時(shí)，調(diào)用此DDL觸發(fā)器，此觸發(fā)器的事務(wù)回滾語(yǔ)句ROLLBACK將撤銷DROP或ALTER語(yǔ)句的執(zhí)行。CREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器68【例10-10】在服務(wù)器上創(chuàng)建DDL觸發(fā)器來(lái)防止服務(wù)器中的任意一個(gè)數(shù)據(jù)庫(kù)被修改或刪除。代碼:CREATETRIGGERsafety2ONALLSERVERFORDROP_DATABASE,ALTER_DATABASEASBEGINPRINT'要?jiǎng)h除和修改數(shù)據(jù)庫(kù)之前，你必須先禁用觸發(fā)器safety2!'ROLLBACKENDCREATE、ALTER和DROP觸發(fā)（三）創(chuàng)建DDL觸發(fā)器69執(zhí)行結(jié)果：新建觸發(fā)器“safety2”

。作用：當(dāng)用戶試圖使用DROP或ALTER命令刪除或修改服務(wù)器中的數(shù)據(jù)庫(kù)時(shí)，調(diào)用此DDL觸發(fā)器，此觸發(fā)器的事務(wù)回滾語(yǔ)句ROLLBACK將撤銷DROP或ALTER命令的執(zhí)行。CREATE、ALTER和DROP觸發(fā)（四）管理觸發(fā)器70在【對(duì)象資源管理器】窗口中，展開(kāi)“數(shù)據(jù)庫(kù)”→“具體數(shù)據(jù)庫(kù)”→“具體表”節(jié)點(diǎn)→“觸發(fā)器”節(jié)點(diǎn)，擊“觸發(fā)器”節(jié)點(diǎn)，從快捷菜單中選擇“修改”命令使用SSMS管理觸發(fā)器（四）管理觸發(fā)器71在【查詢編輯器】中即可對(duì)打開(kāi)的觸發(fā)器進(jìn)行修改使用SSMS管理觸發(fā)器（四）管理觸發(fā)器72修改DML觸發(fā)器。語(yǔ)法如下：ALTERTRIGGER<觸發(fā)器名>ON<表名|視圖名>｛AFTER|INSTEADOF｝[UPDATE][,][INSERT][,][DELETE]AST-SQL語(yǔ)句1.使用T-SQL修改DML觸發(fā)器（四）管理觸發(fā)器73修改DDL觸發(fā)器。語(yǔ)法如下：ALTERTRIGGER<觸發(fā)器名>ON｛ALLSERVER|DATABASE｝[WITHENCRYPTION]｛AFTER｝<事件類型或事件組>[,…n]AST-SQL語(yǔ)句2.使用T-SQL修改DDL觸發(fā)器（四）管理觸發(fā)器74刪除觸發(fā)器。語(yǔ)法如下：DROPTRIGGER<觸發(fā)器名>3.使用T-SQL刪除觸發(fā)器（四）管理觸發(fā)器75禁用觸發(fā)器。語(yǔ)法如下：DISABLETRIGGER觸發(fā)器名ON對(duì)象名|DATABASE|ALLServer啟用觸發(fā)器。語(yǔ)法如下：ENABLETRIGGER觸發(fā)器名ON對(duì)象名|DATABASE|ALLServer4.使用T-SQL禁用與啟用觸發(fā)器（四）管理觸發(fā)器76【例10-11】禁用DDL觸發(fā)器“safety1”和“safety2”，以便進(jìn)行表的修改和刪除。代碼如下：DISABLETRIGGERsafety1ONDATABASEGODISABLETRIGGERsafety2ONALLSERVER2.使用T-SQL管理觸發(fā)器單元2.8T-SQL程序設(shè)計(jì)77創(chuàng)建與管理存儲(chǔ)過(guò)程一創(chuàng)建與管理觸發(fā)器二創(chuàng)建與管理用戶定義函數(shù)三三、創(chuàng)建與管理用戶定義函數(shù)78案例2-10-3圖書(shū)管理用戶定義函數(shù)的創(chuàng)建與管理根據(jù)圖書(shū)管理系統(tǒng)的功能需求，在數(shù)據(jù)庫(kù)“Library”中創(chuàng)建用戶定義函數(shù)。工作任務(wù)（一）用戶定義函數(shù)概述79用戶定義函數(shù)一組編譯好的、存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器上的和完成特定功能的T-SQL程序，是某數(shù)據(jù)庫(kù)的對(duì)象?？梢詫⒁粋€(gè)或多個(gè)T-SQL語(yǔ)句的子程序定義成函數(shù)，從而實(shí)現(xiàn)代碼的封裝和重用。用戶定義函數(shù)（UserDefinedFunction，UDF）可以有多個(gè)輸入?yún)?shù)并返回標(biāo)量（常量）或表值，不支持輸出參數(shù)。1.用戶定義函數(shù)的概念（一）用戶定義函數(shù)概述80用戶定義函數(shù)的優(yōu)點(diǎn)允許模塊化程序設(shè)計(jì)。能夠?qū)崿F(xiàn)較快的執(zhí)行速度。能夠減少網(wǎng)絡(luò)流量。1.用戶定義函數(shù)的概念（一）用戶定義函數(shù)概述81用戶定義函數(shù)與存儲(chǔ)過(guò)程的比較2.用戶定義函數(shù)的優(yōu)點(diǎn)項(xiàng)

目用戶定義函數(shù)存儲(chǔ)過(guò)程參數(shù)允許多個(gè)輸入?yún)?shù)，不允許輸出參數(shù)允許多個(gè)輸入/輸出參數(shù)返回值有且只有一個(gè)返回值，可以返回標(biāo)量或表值可以沒(méi)有返回值，不能返回表值調(diào)用在表達(dá)式中引用，可以嵌入在查詢語(yǔ)句的表達(dá)式中調(diào)用必須單獨(dú)調(diào)用（一）用戶定義函數(shù)概述82標(biāo)量值函數(shù)返回的是在RETURNS子句中定義類型的標(biāo)量表達(dá)式的值（單個(gè)數(shù)據(jù)值）。3.用戶定義函數(shù)的分類（一）用戶定義函數(shù)概述83表值函數(shù)：返回的是在RETURNS子句中指定的“TABLE”類型的數(shù)據(jù)行集（表值）。內(nèi)聯(lián)表值函數(shù)：沒(méi)有函數(shù)體，RETURN子句在括號(hào)中含有一條單獨(dú)的SELECT查詢語(yǔ)句。多語(yǔ)句表值函數(shù)：在BEGIN…END語(yǔ)句塊中定義的函數(shù)體包含一系列T-SQL語(yǔ)句。2.用戶定義函數(shù)的分類（二）創(chuàng)建用戶定義函數(shù)84基本語(yǔ)法CREATEFUNCTION函數(shù)名 --創(chuàng)建標(biāo)量函數(shù)([@形參數(shù)據(jù)類型][,...n]) --括號(hào)內(nèi)輸入?yún)?shù)RETURNS返回?cái)?shù)據(jù)類型

--定義返回標(biāo)量值的數(shù)據(jù)類型ASBEGINT-SQL語(yǔ)句

--函數(shù)體RETURN標(biāo)量表達(dá)式

--返回RETURNS子句中定義的數(shù)據(jù)類型的單個(gè)數(shù)據(jù)值END1.創(chuàng)建標(biāo)量值函數(shù)（二）創(chuàng)建用戶定義函數(shù)85【例10-12】創(chuàng)建標(biāo)量值函數(shù)“fn_price”，價(jià)格高于50元的書(shū)認(rèn)為是較貴的圖書(shū)，否則認(rèn)為是便宜的圖書(shū)，實(shí)現(xiàn)對(duì)圖書(shū)價(jià)格的高與低的評(píng)價(jià)。代碼:USELibraryGOCREATEFUNCTIONfn_price(@priceinputmoney)RETURNSnvarchar(5) --函數(shù)返回nvarchar(5)類型標(biāo)量AS1.創(chuàng)建標(biāo)量值函數(shù)（二）創(chuàng)建用戶定義函數(shù)86代碼:BEGINDECLARE@returnstrnvarchar(5)IF@priceinput>50 --如果輸入?yún)?shù)的值大于50SET@returnstr=‘較貴的圖書(shū)’--賦值‘較貴的圖書(shū)’ELSESET@returnstr=‘便宜的圖書(shū)’--否則賦值'便宜的圖書(shū)'RETURN@returnstr--返回字符串標(biāo)量值END1.創(chuàng)建標(biāo)量值函數(shù)（二）創(chuàng)建用戶定義函數(shù)87執(zhí)行結(jié)果：新建用戶定義函數(shù)“fn_price”

。函數(shù)功能：當(dāng)函數(shù)被調(diào)用時(shí)，參數(shù)如果大于50，則輸出較貴的圖書(shū)，否則輸出較便宜的圖書(shū)。1.創(chuàng)建標(biāo)量值函數(shù)（二）創(chuàng)建用戶定義函數(shù)88調(diào)用舉例：在表達(dá)式中使用函數(shù):SELECTBID,Bname,Author,Price,dbo.fn_price(Price)FROMBook--調(diào)用自定義函數(shù)執(zhí)行結(jié)果：1.創(chuàng)建標(biāo)量值函數(shù)（二）創(chuàng)建用戶定義函數(shù)89基本語(yǔ)法CREATEFUNCTION函數(shù)名([@形參名數(shù)據(jù)類型][,...n]) --括號(hào)內(nèi)輸入?yún)?shù)RETURNSTABLE --定義返回值為表ASBEGINRETURN(SELECT查詢語(yǔ)句)--返回查詢結(jié)果的數(shù)據(jù)行集END2.創(chuàng)建內(nèi)聯(lián)表值函數(shù)（二）創(chuàng)建用戶定義函數(shù)90【例10-13】創(chuàng)建用戶定義內(nèi)聯(lián)表值函數(shù)“fn_Publisher”，根據(jù)指定的出版社查詢?cè)摮霭嫔绯霭娴膱D書(shū)，返回結(jié)果數(shù)據(jù)行集。代碼:USELibraryGOCREATEFUNCTIONfn_Publisher(@Publishervarchar(30))RETURNSTABLE--函數(shù)返回值為表值A(chǔ)SRETURN(SELECTBID,Bname,Author,Publisher