數據庫技術及應用（SQL Server ）2.8

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

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

[[@形參數據類型] --輸入參數

|[@形參數據類型=默認值] --默認值參數

|[@變參數據類型OUTPUT] --輸出參數（返回值）

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

--輸出參數1得到圖書總價值，賦值語句SELECT@SUMPrice=SUM(price)FROMBookWHEREPublisher=@Publisher

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

--輸出變量（實參）值返回結果4．帶輸出參數的存儲過程（三）管理存儲過程24修改ALTERPROC[EDURE]過程名 --修改存儲過程

[[@形參數據類型] --輸入參數

|[@形參數據類型=默認值] --默認值參數

|[@變參數據類型OUTPUT] --輸出參數

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

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

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

2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器48USELibraryGOCREATETRIGGERT_Return--創(chuàng)建觸發(fā)器ONBorrow--基于表“Borrow”AFTERUPDATE--在更新操作之后激發(fā)執(zhí)以下語句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)--讀者編號SET@tsbh=(SELECTBIDFROMinserted) --圖書編號SET@hsrq=(SELECTReturnDateFROMinserted)--還書日期2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器50/*從借閱表中查詢出該讀者的本次還書日期和應還日期之差,DATEDIFF函數返回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，沒過期，如果@days大于0，即ReturnDate>SReturnDate，過期@days天*/IF@days<=0PRINT'沒有過期！'ELSEPRINT'過期'+convert(char(6),@days)+'天'2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器52--將讀者表中該讀者的借閱數量減1UPDATEReaderSETLendnum=Lendnum-1WHERERID=@dzbh--將圖書表“Book”中該書是否借出置為0UPDATEBookSETLentOut=0WHEREBID=@tsbhEND2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器53假設：執(zhí)行UPDATE…SET語句完成讀者還書，假設系統(tǒng)日期為2023-11-30，更新借閱表“Borrow”的代碼為：UPDATEBorrowSETReturnDate=GETDATE()WHERERID=‘2023216008'ANDBID='F275.3/65'ANDReturnDateISNULLUPDATE…SET執(zhí)行結果更新了表“Borrow”中還書讀者的還書日期“ReturnDate”為’2023-11-30'。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器54調用觸發(fā)器“T_Return”返回結果過期3天(1行受影響)對于讀者表“Reader”，讀者編號為2023216008的借閱數量“Lendnum”減1變?yōu)?，從而保證了數據的一致性。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器55圖書表“Book”，圖書編號為‘F275.3/65’的圖書是否借出列“LendOut”置為假（0，'False‘）。2．創(chuàng)建AFTERUPDATE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器56【例10-7】刪除某位讀者信息。若要刪除一名讀者要先檢查該讀者是否有書沒還，若該讀者還有書沒還則不能被刪除。代碼：USELibraryGOCREATETRIGGERT_ReaderDEL--創(chuàng)建觸發(fā)器ONReader --基于表ReaderAFTERDELETE--在刪除后觸發(fā)AS3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器57BEGINDECLARE@LNumint--從刪除的數據行的臨時表中獲得借閱數量SELECT@LNum=LendnumFROMDELETEDIF@LNum>0 --如果借閱數量大于0BEGINPRINT'該讀者不能刪除！還有

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

ROLLBACK --事務回滾撤銷所刪除的數據行ENDELSE--如果借閱數量不大于0PRINT'該讀者已被刪除?。?！'--顯示數據行已刪除END3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器58假設：刪除某讀者。DELETEReaderWHERERID=‘2004216010’執(zhí)行結果：該讀者不能被刪除！還有1冊書未還。事務在觸發(fā)器中結束。批處理已中止。查看讀者表：可見該讀者未被刪除。3．創(chuàng)建AFTERDELETE觸發(fā)器（二）創(chuàng)建DML觸發(fā)器59【例10-8】刪除圖書保護。在圖書館圖書處理過程中，不允許隨意刪除表“Book”中的圖書。創(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'圖書未被刪除！' --顯示數據行，圖書未被刪除END4．創(chuàng)建INSTEADOF觸發(fā)器（二）創(chuàng)建DML觸發(fā)器61假設：刪除某圖書。DELETEBookWHEREBID='TP311.138/235‘執(zhí)行結果：圖書未被刪除！(1行受影響)查看表“Book”，可見BID為‘TP311.138/235’的圖書未被刪除。

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

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

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

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

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

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

--定義返回標量值的數據類型ASBEGINT-SQL語句

--函數體RETURN標量表達式

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

。函數功能：當函數被調用時，參數如果大于50，則輸出較貴的圖書，否則輸出較便宜的圖書。1.創(chuàng)建標量值函數（二）創(chuàng)建用戶定義函數88調用舉例：在表達式中使用函數:SELECTBID,Bname,Author,Price,dbo.fn_price(Price)FROMBook--調用自定義函數執(zhí)行結果：1.創(chuàng)建標量值函數（二）創(chuàng)建用戶定義函數89基本語法CREATEFUNCTION函數名([@形參名數據類型][,...n]) --括號內輸入參數RETURNSTABLE --定義返回值為表ASBEGINRETURN(SELECT查詢語句)--返回查詢結果的數據行集END2.創(chuàng)建內聯表值函數（二）創(chuàng)建用戶定義函數90【例10-13】創(chuàng)建用戶定義內聯表值函數“fn_Publisher”，根據指定的出版社查詢該出版社出版的圖書，返回結果數據行集。代碼:USELibraryGOCREATEFUNCTIONfn_Publisher(@Publishervarchar(30))RETURNSTABLE--函數返回值為表值ASRETURN(SELECTBID,Bname,Author,Publisher