數(shù)據(jù)庫(kù)原理與應(yīng)用教程

―SQLServer2019第11章存儲(chǔ)過(guò)程、觸發(fā)器第11章存儲(chǔ)過(guò)程和觸發(fā)器在SQLServer應(yīng)用操作中，存儲(chǔ)過(guò)程扮演著相當(dāng)重要的角色。存儲(chǔ)過(guò)程可以使用戶(hù)對(duì)數(shù)據(jù)庫(kù)的管理工作變得更容易。**存儲(chǔ)過(guò)程是SQL語(yǔ)句和可選流程控制語(yǔ)句的預(yù)編譯集合，它以一個(gè)名稱(chēng)存儲(chǔ)并作為一個(gè)單元處理，能夠提高系統(tǒng)的應(yīng)用效率和執(zhí)行速度(預(yù)先編譯好的一段程序)。第11章存儲(chǔ)過(guò)程和觸發(fā)器11.1存儲(chǔ)過(guò)程開(kāi)發(fā)應(yīng)用程序時(shí)，為了易于修改和擴(kuò)充，經(jīng)常會(huì)將負(fù)責(zé)不同功能的語(yǔ)句集中起來(lái)而且按照用途分別獨(dú)立放置，以便能夠反復(fù)調(diào)用，而這些獨(dú)立放置且擁有不同功能的語(yǔ)句，即是“過(guò)程”(Procedure)。*用戶(hù)可以通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行(調(diào)用)存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程類(lèi)似于函數(shù)，是獨(dú)立存儲(chǔ)的數(shù)據(jù)庫(kù)對(duì)象。11.1存儲(chǔ)過(guò)程11.1.1存儲(chǔ)過(guò)程概述利用SQLServer創(chuàng)建一個(gè)應(yīng)用程序時(shí)，SQL是主要的編程語(yǔ)言。使用SQL進(jìn)行編程，有兩種方法。一是，在本地存儲(chǔ)SQL程序，并創(chuàng)建應(yīng)用程序向SQLServer服務(wù)器發(fā)送命令來(lái)對(duì)結(jié)果進(jìn)行處理。二是，可以把部分用SQL語(yǔ)句編寫(xiě)的程序作為存儲(chǔ)過(guò)程存儲(chǔ)在SQLServer服務(wù)器中，然后創(chuàng)建應(yīng)用程序來(lái)調(diào)用存儲(chǔ)過(guò)程，對(duì)數(shù)據(jù)結(jié)果進(jìn)行處理。一般來(lái)講，使用服務(wù)器中的存儲(chǔ)過(guò)程而不使用客戶(hù)機(jī)本地T-SQL程序的優(yōu)勢(shì)主要表現(xiàn)在**：(1)允許模塊化程序設(shè)計(jì)：一次編寫(xiě)，多次調(diào)用；易于修改。(2)允許更快速地執(zhí)行：存儲(chǔ)過(guò)程是預(yù)先編譯和優(yōu)化好的。(3)減少網(wǎng)絡(luò)流量：程序代碼（調(diào)用存儲(chǔ)過(guò)程）和數(shù)據(jù)（一次性傳輸）的傳輸量少。(4)可作為安全機(jī)制使用：可以只給用戶(hù)執(zhí)行存儲(chǔ)過(guò)程的權(quán)限，不允許他的程序直接訪(fǎng)問(wèn)數(shù)據(jù)。

11.1.1存儲(chǔ)過(guò)程概述11.1.2存儲(chǔ)過(guò)程的類(lèi)型1.系統(tǒng)存儲(chǔ)過(guò)程SQLServer2019中的許多管理活動(dòng)或信息性的活動(dòng)（如獲取數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息）都是通過(guò)一種特殊的存儲(chǔ)過(guò)程執(zhí)行的，這種存儲(chǔ)過(guò)程被稱(chēng)為系統(tǒng)存儲(chǔ)過(guò)程。系統(tǒng)存儲(chǔ)過(guò)程主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中并以sp_開(kāi)頭（如,SP_HELPDB:報(bào)告有關(guān)指定數(shù)據(jù)庫(kù)或所有數(shù)據(jù)庫(kù)的信息。11.1存儲(chǔ)過(guò)程2．本地存儲(chǔ)過(guò)程即本地服務(wù)器上的存儲(chǔ)過(guò)程，也就是一般所稱(chēng)的用戶(hù)自定義存儲(chǔ)過(guò)程*。本地存儲(chǔ)過(guò)程也就是用戶(hù)自行創(chuàng)建、能完成某一特定功能(如查詢(xún)用戶(hù)所需的數(shù)據(jù)信息)、并存儲(chǔ)在用戶(hù)數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程，一般所說(shuō)的存儲(chǔ)過(guò)程指的就是本地存儲(chǔ)過(guò)程。(有權(quán)限的用戶(hù)程序都可以調(diào)用)11.1.2存儲(chǔ)過(guò)程的類(lèi)型3.臨時(shí)存儲(chǔ)過(guò)程（有一定的使用期限）(1)本地臨時(shí)存儲(chǔ)過(guò)程(2)全局臨時(shí)存儲(chǔ)過(guò)程4.遠(yuǎn)程存儲(chǔ)過(guò)程（即遠(yuǎn)程服務(wù)器上的存儲(chǔ)過(guò)程）5.擴(kuò)展存儲(chǔ)過(guò)程（高級(jí)語(yǔ)言編寫(xiě)，功能更強(qiáng)）11.1.2存儲(chǔ)過(guò)程的類(lèi)型11.1存儲(chǔ)過(guò)程11.1.3創(chuàng)建存儲(chǔ)過(guò)程1．在SQLServerManagementStudio中創(chuàng)建存儲(chǔ)過(guò)程展開(kāi)“可編程性”選項(xiàng)，可以看到存儲(chǔ)過(guò)程列表中系統(tǒng)自動(dòng)為數(shù)據(jù)庫(kù)創(chuàng)建的系統(tǒng)存儲(chǔ)過(guò)程。右擊“存儲(chǔ)過(guò)程”選項(xiàng)，選“新建存儲(chǔ)過(guò)程”命令。2．利用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程CREATEPROCEDURE創(chuàng)建存儲(chǔ)過(guò)程，語(yǔ)法格式如下：CREATE{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUT[PUT]]][,...n][WITH{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}]ASsql_statement[...n]11.1.3創(chuàng)建存儲(chǔ)過(guò)程在創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需注意以下幾點(diǎn)。(1)存儲(chǔ)過(guò)程最大不能超過(guò)128MB。(2)用戶(hù)定義的存儲(chǔ)過(guò)程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建。(3)CREATEPROCEDURE必須是一個(gè)批處理的第一條語(yǔ)句。(4)SQLServer允許在存儲(chǔ)過(guò)程創(chuàng)建時(shí)引用一個(gè)不存在的對(duì)象，在創(chuàng)建的時(shí)候，系統(tǒng)只檢查創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)法。

11.1.3創(chuàng)建存儲(chǔ)過(guò)程[例11-1]在教學(xué)庫(kù)創(chuàng)建無(wú)參存儲(chǔ)過(guò)程，查詢(xún)每個(gè)學(xué)生的平均成績(jī)。USEteachingGOCREATEPROCEDUREstudent_avgASSELECTsno,avg(score)as'avgscore'FROMscGROUPBYsnoGO11.1.3創(chuàng)建存儲(chǔ)過(guò)程[例11-2]在教學(xué)庫(kù)創(chuàng)建帶參數(shù)的存儲(chǔ)過(guò)程，查詢(xún)某個(gè)同學(xué)的基本信息。USEteachingGOCREATEPROCEDUREGetStudent@numberchar(10)ASSELECT*FROMstudentWHEREsno=@numberGO11.1.3創(chuàng)建存儲(chǔ)過(guò)程【例11-3】創(chuàng)建存儲(chǔ)過(guò)程，修改某個(gè)學(xué)生某門(mén)課的成績(jī)。USEteachingGOCREATEPROCEDUREUpdate_score@numberchar(10),@cnochar(4),@scoreintASUPDATEscSETscore=@scoreWHEREsno=@numberandcno=@cnoSELECT*FROMscWHEREsno=@number11.1.3創(chuàng)建存儲(chǔ)過(guò)程【例11-4】在bankcard數(shù)據(jù)庫(kù)中使用流程控制語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程。假設(shè)今天銀行有活動(dòng)，如果今天某賬號(hào)交易支出總金額超過(guò)3000元，則獎(jiǎng)勵(lì)其10元。USEbankcardGOCREATEPROCEDUREadd_10@AccNOchar(20)WITHENCRYPTIONASIF(SELECTSUM(Expense)FROMTrecordWHERETdate=CONVERT(varchar(10),GETDATE(),120)ANDAccNO=@AccNO)>=3000BEGINUPDATEAccountSETBalance=Balance+10WHEREAccNO=@AccNOINSERTTrecord(TDate,AccNO,Income,Abstract)VALUES(GETDATE(),@AccNO,10,'銀行活動(dòng)獎(jiǎng)勵(lì)')END11.1.3創(chuàng)建存儲(chǔ)過(guò)程【例11-5】在bankcard數(shù)據(jù)庫(kù)創(chuàng)建帶OUTPUT參數(shù)的存儲(chǔ)過(guò)程，用于計(jì)算指定的儲(chǔ)戶(hù)的總余額，存儲(chǔ)過(guò)程中使用一個(gè)輸入?yún)?shù)（身份證號(hào)）和兩個(gè)輸出參數(shù)（儲(chǔ)戶(hù)姓名和總余額）。USEbankcardGOCREATEPROCEDUREs_balance@IDNOchar(18),@dnamenvarchar(10)OUTPUT,@sbalancemoneyOUTPUTASSELECT@dname=DnameFROMdepositorWHEREIDNO=@IDNOSELECT@sbalance=SUM(Balance)FROMaccount WHEREIDNO=@IDNOGO11.1.3創(chuàng)建存儲(chǔ)過(guò)程11.1存儲(chǔ)過(guò)程11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程使用T-SQL語(yǔ)中的EXECUTE命令。如果執(zhí)行存儲(chǔ)過(guò)程是批處理中的第一條語(yǔ)句，那么不使用EXECUTE關(guān)鍵字也可以。注意：1、對(duì)于存儲(chǔ)過(guò)程的所有者或任何一名對(duì)此過(guò)程擁有EXECUTE權(quán)限的用戶(hù)，都可以執(zhí)行此存儲(chǔ)過(guò)程。2、輸入?yún)?shù)在存儲(chǔ)過(guò)程名后逐一給定，用逗號(hào)隔開(kāi)，不必使用括號(hào)。3、如果沒(méi)有使用@參數(shù)名=value這種方式傳入值，則參數(shù)的排列必須和建立存儲(chǔ)過(guò)程所定義的次序?qū)?yīng)。4、用來(lái)接受輸出值的參數(shù)必須加上OUTPUT。EXECUTE語(yǔ)句的語(yǔ)法格式：[[EXECUTE[UTE]][@return_status=]procedure_name[;number]{[[@parameter=]value|[@parameter=]@variable[OUTPUT]]}[WITHRECOMPILE]【例11-6】執(zhí)行存儲(chǔ)過(guò)程student_avg。EXECUTEstudent_avg11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過(guò)程【例11-7】執(zhí)行帶參數(shù)的存儲(chǔ)過(guò)程GetStudent，查詢(xún)

2021010001號(hào)學(xué)生的基本信息。EXECUTEGetStudent'2021010001'【例11-8】執(zhí)行修改成績(jī)的存儲(chǔ)過(guò)程Update_score，將2022020001號(hào)學(xué)生選修的C001號(hào)課程的成績(jī)改為100。EXECUTEUpdate_score'2022020001','C001',100【例11-9】用賬號(hào)412542800335120***01執(zhí)行存儲(chǔ)過(guò)程add_10。EXECUTEadd_10'412542800335120***01'11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過(guò)程【例11-10】執(zhí)行帶有輸入和輸出參數(shù)的存儲(chǔ)過(guò)程s_balance。Declare@IDNOchar(18),@dnamenvarchar(10),@sbalancemoneyEXECUTEs_balance'133***198812110101',@dnameOUTPUT,@sbalanceOUTPUTPrint'儲(chǔ)戶(hù)'+@dname+'目前總余額'+str(@sbalance)11.1.4執(zhí)行（調(diào)用）存儲(chǔ)過(guò)程11.1.5查看、修改和刪除存儲(chǔ)過(guò)程1．查看存儲(chǔ)過(guò)程可以執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_helptext，用于查看創(chuàng)建存儲(chǔ)過(guò)程的命令語(yǔ)句；也可以執(zhí)行系統(tǒng)存儲(chǔ)過(guò)程sp_help，用于查看存儲(chǔ)過(guò)程的名稱(chēng)、擁有者、類(lèi)型、創(chuàng)建時(shí)間等基本信息。【例11-11】查看存儲(chǔ)過(guò)程s_balance的相關(guān)信息。(1)sp_helptexts_balance

(2)sp_helps_balance

11.1存儲(chǔ)過(guò)程11.1.5查看、修改和刪除存儲(chǔ)過(guò)程2．修改存儲(chǔ)過(guò)程可以在SQLServerManagementStudio中“修改”；也可以通過(guò)T-SQL語(yǔ)句完成，語(yǔ)法與創(chuàng)建時(shí)完全相同：ALTER{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][[OUT[PUT]][,...n][WITH{RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION

}[,...n]][FORREPLICATION]ASsql_statement[...n]【例11-12】修改存儲(chǔ)過(guò)程add_10，將3000元和10元設(shè)置為兩個(gè)參數(shù)的默認(rèn)值，使存儲(chǔ)過(guò)程應(yīng)用更靈活。USEbankcardGOALTERPROCEDUREadd_10@AccNOchar(20),@expmoney=3000,@addint=10WITHENCRYPTIONASIF(SELECTSUM(Expense)FROMTrecordWHERETDate=CONVERT(varchar(10),GETDATE(),120)ANDAccNO=@AccNO)>=@expBEGINUPDATEAccountSETBalance=Balance+@addWHEREAccNO=@AccNOINSERTTrecord(TDate,AccNO,Income,Abstract)VALUES(CONVERT(varchar(10),GETDATE(),120),@AccNO,@add,'銀行活動(dòng)獎(jiǎng)勵(lì)')END11.1.5查看、修改和刪除存儲(chǔ)過(guò)程【例11-13】執(zhí)行帶有參數(shù)和默認(rèn)值的存儲(chǔ)過(guò)程add_10。(1)EXECUTEadd_10'412542800335120***01'(2)EXECUTEadd_10'412542800335120***01',2000(3)EXECUTEadd_10'412542800335120***01',5000,2011.1.5查看、修改和刪除存儲(chǔ)過(guò)程3．刪除存儲(chǔ)過(guò)程對(duì)于不需要的存儲(chǔ)過(guò)程可以在SQLServerManagementStudio中刪除；也可以使用T-SQL語(yǔ)句中的DROPPROCEDURE命令刪除。T-SQL語(yǔ)句的語(yǔ)法格式為：DROPPROCEDURE{procedure_name}[,…n]procedure_name指要?jiǎng)h除的存儲(chǔ)過(guò)程或存儲(chǔ)過(guò)程組的名稱(chēng)?！纠?1-14】刪除存儲(chǔ)過(guò)程s_balanceDROPPROCEDUREs_balance11.1.5查看、修改和刪除存儲(chǔ)過(guò)程11.2觸發(fā)器存儲(chǔ)過(guò)程是SQL語(yǔ)句和可選流程控制語(yǔ)句的預(yù)編譯集合，它以一個(gè)名稱(chēng)存儲(chǔ)并作為一個(gè)單元處理，用戶(hù)可以通過(guò)指定存儲(chǔ)過(guò)程的名稱(chēng)并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行(調(diào)用)存儲(chǔ)過(guò)程。*就本質(zhì)而言，觸發(fā)器也是一種存儲(chǔ)過(guò)程，是一種特殊類(lèi)型的存儲(chǔ)過(guò)程。它在特定語(yǔ)言事件發(fā)生時(shí)自動(dòng)執(zhí)行。11.2.1觸發(fā)器概述在SQLServer數(shù)據(jù)庫(kù)系統(tǒng)中，存儲(chǔ)過(guò)程和觸發(fā)器都是SQL語(yǔ)句和可選流程控制語(yǔ)句的集合。觸發(fā)器是與表緊密聯(lián)系在一起的，它在特定的表上定義，是一種在基本表被修改時(shí)自動(dòng)執(zhí)行的內(nèi)嵌過(guò)程，主要通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行；而存儲(chǔ)過(guò)程是定義在數(shù)據(jù)庫(kù)內(nèi)的獨(dú)立對(duì)象，可以通過(guò)存儲(chǔ)過(guò)程名被直接調(diào)用。當(dāng)對(duì)某一張表進(jìn)行諸如UPDATE、INSERT、DELETE這些操作時(shí)，SQLServer就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句。

觸發(fā)器的功能觸發(fā)器一般是用來(lái)保持?jǐn)?shù)據(jù)的完整性，而不是返回大量的查詢(xún)結(jié)果。使用觸發(fā)器主要可以實(shí)現(xiàn)以下功能：(1)強(qiáng)制比CHECK約束更復(fù)雜的數(shù)據(jù)完整性。(2)使用自定義的錯(cuò)誤提示信息(3)實(shí)現(xiàn)數(shù)據(jù)庫(kù)中多張表的級(jí)聯(lián)修改(4)比較數(shù)據(jù)庫(kù)修改前后數(shù)據(jù)的狀態(tài)(5)調(diào)用存儲(chǔ)過(guò)程(6)維護(hù)非規(guī)范化數(shù)據(jù)11.2.2觸發(fā)器的分類(lèi)1．DML觸發(fā)器*（重點(diǎn)）DML觸發(fā)器是當(dāng)數(shù)據(jù)庫(kù)服務(wù)器中發(fā)生數(shù)據(jù)操作語(yǔ)言(DML)事件時(shí)會(huì)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。DML事件包括在指定表或視圖中修改數(shù)據(jù)的INSERT、UPDATE或DELETE語(yǔ)句。*系統(tǒng)將觸發(fā)器和觸發(fā)它的語(yǔ)句作為可在觸發(fā)器內(nèi)回滾的單個(gè)事務(wù)對(duì)待，如果檢測(cè)到錯(cuò)誤，則整個(gè)事務(wù)自動(dòng)回滾。觸發(fā)器的分類(lèi)(1)AFTER觸發(fā)器：這類(lèi)觸發(fā)器是在記錄已經(jīng)改變完之后，才會(huì)被激活執(zhí)行，它主要是用于記錄變更后的處理或檢查。(2)INSTEADOF觸發(fā)器：與AFTER觸發(fā)器不同，這類(lèi)觸發(fā)器一般是用來(lái)取代原本的操作，在記錄變更之前發(fā)生的，它并不去執(zhí)行原來(lái)SQL語(yǔ)句里的操作（UPDATE、INSERT、DELETE），而去執(zhí)行觸發(fā)器本身所定義的操作。觸發(fā)器的分類(lèi)2．DDL觸發(fā)器（了解）DDL觸發(fā)器是SQLServer2005新增的一種觸發(fā)器，它在響應(yīng)數(shù)據(jù)定義語(yǔ)言（DDL，create、alter和drop）語(yǔ)句時(shí)觸發(fā)。添加、刪除或修改數(shù)據(jù)庫(kù)的對(duì)象，一旦誤操作，可能導(dǎo)致大麻煩。如果想能夠跟蹤這種問(wèn)題和定位問(wèn)題的根源，就可以利用DDL觸發(fā)器來(lái)記錄類(lèi)似“用戶(hù)建立表”這種變化的操作，這樣可以大大減輕跟蹤和定位數(shù)據(jù)庫(kù)模式的變化的繁瑣程度。觸發(fā)器的分類(lèi)一般，在以下幾種情況下可以使用DDL觸發(fā)器：(1)數(shù)據(jù)庫(kù)里的庫(kù)架構(gòu)或數(shù)據(jù)表架構(gòu)很重要，不允許被修改（alter）。(2)防止數(shù)據(jù)庫(kù)或數(shù)據(jù)表被誤操作刪除（drop）。(3)在修改某個(gè)數(shù)據(jù)表結(jié)構(gòu)的同時(shí)修改另一個(gè)數(shù)據(jù)表的相應(yīng)的結(jié)構(gòu)（為了保持一致）。(4)要記錄對(duì)數(shù)據(jù)庫(kù)結(jié)構(gòu)操作的事件。11.2.3創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器需要注意的問(wèn)題*：(1)CREATETRIGGER語(yǔ)句必須是批處理中的第一個(gè)語(yǔ)句，而且只能用于一個(gè)表或視圖。(2)創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)為表的所有者，不能將該權(quán)限轉(zhuǎn)給其他用戶(hù)。(3)觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)以外的對(duì)象，但只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建觸發(fā)器。(4)雖然不能在臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)器可以引用臨時(shí)表。(5)TRUNCATETABLE語(yǔ)句不會(huì)激發(fā)DELETE觸發(fā)器。創(chuàng)建觸發(fā)器的方法1．在SQLServerManagementStudio中創(chuàng)建DML觸發(fā)器展開(kāi)要?jiǎng)?chuàng)建DML觸發(fā)器的數(shù)據(jù)庫(kù)和其中的表或視圖（如student表），右鍵單擊“觸發(fā)器”選項(xiàng)，選擇“新建觸發(fā)器”命令。2．利用T-SQL語(yǔ)句創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語(yǔ)法CREATETRIGGER創(chuàng)建觸發(fā)器語(yǔ)法格式：CREATETRIGGER

trigger_name

ON{table_name|view}

[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}

{[INSERT][DELETE][UPDATE]}

ASsql_statement[...n]--當(dāng)插入、刪除或修改表中數(shù)據(jù)時(shí)，觸發(fā)的sql語(yǔ)句創(chuàng)建觸發(fā)器的簡(jiǎn)單例子[例11-15]使用DDL觸發(fā)器limited來(lái)防止數(shù)據(jù)庫(kù)中的任一表被修改或刪除(自定義錯(cuò)誤提示)。USEteachingGOCREATETRIGGERlimitedONdatabaseFORDROP_TABLE,ALTER_TABLEASPRINT'觸發(fā)器不允許您執(zhí)行對(duì)表的修改或刪除操作!'ROLLBACK

觸發(fā)語(yǔ)句:ALTERTABLEstudentADDbirthdaydatetime創(chuàng)建觸發(fā)器的簡(jiǎn)單例子[例11-17]為學(xué)生表創(chuàng)建一個(gè)簡(jiǎn)單DML觸發(fā)器，在插入和修改數(shù)據(jù)時(shí)，都會(huì)自動(dòng)顯示提示信息。USEteachingGOCREATETRIGGERreminderONstudentFORINSERT,UPDATEASprint'你在插入或修改學(xué)生表的數(shù)據(jù)'觸發(fā)語(yǔ)句:UPDATEstudentSETsname='劉小梅'WHEREsname='劉梅'創(chuàng)建觸發(fā)器的簡(jiǎn)單例子[例11-19]為學(xué)生表創(chuàng)建一個(gè)DML觸發(fā)器，在插入和修改數(shù)據(jù)時(shí)，都會(huì)自動(dòng)顯示所有學(xué)生的信息。CREATETRIGGERprint_tableONstudentFORINSERT,UPDATEASselect*fromstudent觸發(fā)語(yǔ)句:UPDATEstudentSETsname='劉梅'WHEREsname='劉小梅'創(chuàng)建觸發(fā)器的簡(jiǎn)單例子[例11-21]在學(xué)生表上創(chuàng)建一個(gè)DELETE類(lèi)型的觸發(fā)器，刪除數(shù)據(jù)時(shí)，顯示刪除學(xué)生的個(gè)數(shù)。CREATETRIGGERdel_countONstudentFORDELETEASDECLARE@countvarchar(50)SET@count=STR(@@ROWCOUNT)+'

個(gè)學(xué)生被刪除'

SELECT@countRETURN觸發(fā)語(yǔ)句:

DELETEFROMstudentWHEREspecialty='計(jì)算機(jī)'創(chuàng)建觸發(fā)器SQLServer為每個(gè)DML觸發(fā)器都定義了兩個(gè)特殊的表，一個(gè)是插入表:Inserted，一個(gè)是刪除表:Deleted。它們建在數(shù)據(jù)庫(kù)服務(wù)器的內(nèi)存中，是由系統(tǒng)管理的邏輯表，而不是真正存儲(chǔ)在數(shù)據(jù)庫(kù)中的物理表。對(duì)于這兩個(gè)表，用戶(hù)只有讀取的權(quán)限，沒(méi)有修改的權(quán)限。在觸發(fā)器的執(zhí)行過(guò)程中，SQLServer建立和管理這兩個(gè)臨時(shí)表。這兩個(gè)表的結(jié)構(gòu)與觸發(fā)器所在數(shù)據(jù)表的結(jié)構(gòu)是完全一致的，其中包含了在激發(fā)觸發(fā)器的操作中插入或刪除的所有記錄。當(dāng)觸發(fā)器的工作完成之后，這兩個(gè)表也將會(huì)從內(nèi)存中刪除。創(chuàng)建觸發(fā)器**兩個(gè)臨時(shí)表的作用在用戶(hù)執(zhí)行INSERT語(yǔ)句時(shí)，所有被添加的記錄都會(huì)存儲(chǔ)在Inserted表以及觸發(fā)器所在的表中；在用戶(hù)執(zhí)行DELETE語(yǔ)句時(shí)，從觸發(fā)器所在的表中被刪除的行會(huì)發(fā)送到Deleted表；而對(duì)于UPDATE語(yǔ)句，SQLServer先將要進(jìn)行修改的記錄行存儲(chǔ)到Deleted表中，然后再將修改后的數(shù)據(jù)行存儲(chǔ)到Inserted表以及觸發(fā)器所在的表中。創(chuàng)建觸發(fā)器[例]為student表創(chuàng)建一個(gè)名為s_tr的觸發(fā)器，當(dāng)執(zhí)行添加、更新或刪除時(shí)，激活該觸發(fā)器。通過(guò)此例，了解Inserted表和Deleted表的功能。CREATETRIGGERs_trONstudentFORINSERT,UPDATE,DELETEASSELECT*FROMinsertedSELECT*FROMdeleted觸發(fā)語(yǔ)句:UPDATEstudentSETsname='張玲'WHEREsno='2021010001'創(chuàng)建觸發(fā)器【例11-23】在bankcard數(shù)據(jù)庫(kù)中使用流程控制語(yǔ)句創(chuàng)建insert觸發(fā)器。當(dāng)向交易記錄表（Trecord）中添加了一條交易信息時(shí)，如果今天某賬號(hào)交易支出某個(gè)金額，則其賬戶(hù)余額減去此金額；如果今天某賬號(hào)收入某個(gè)金額，則其賬戶(hù)余額加上此金額。USEbankcard

GO

CREATETRIGGERTransactionsONTrecord

FORINSERT

AS

IF(SELECTExpenseFROMinserted)ISNOTNULL

UPDATEaccountSETBalance=Balance-(SELECTExpenseFROMinserted)

WHEREAccNO=(SELECTAccNOFROMinserted)

ELSE

UPDATEaccountSETBalance=Balance+(SELECTIncomeFROMinserted)

WHEREAccNO=(SELECTAccNOFROMinserted)創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器【例11-24】向交易記錄表（Trecord）中添加了兩條交易信息記錄，觸發(fā)此觸發(fā)器。INSERTTrecordVALUES(getdate(),'412542800335120***06',50,NULL,'412542800335120***08','北國(guó)超市','消費(fèi)支出')INSERTTrecordVALUES(getdate(),'412542800335120***08',NULL,50,'412542800335120***06','北國(guó)超市','銷(xiāo)售收入')觸發(fā)器可以實(shí)現(xiàn)復(fù)雜的約束和特殊的約束：在教學(xué)庫(kù)創(chuàng)建一個(gè)教師表和一個(gè)教師工資表Createtableteacher(tnointprimarykey,tnamenvarchar(6),prof_titlenvarchar(10))gocreatetableteacher_salary(tnointprimarykeyforeignkeyreferencesteacher(tno),tnamenvarchar(6),salaryint)go創(chuàng)建觸發(fā)器insertteachervalues(1,'鄭浩','教授')insertteachervalues(2,'王偉','副教授')insertteachervalues(3,'李平','講師')創(chuàng)建一個(gè)工資級(jí)別表createtablesalary_level(prof_titlenvarchar(10)primarykey,minsalaryint,maxsalaryint)GoINSERTsalary_levelVALUES('教授',10000,12000)INSERTsalary_levelVALUES('副教授',7900,10000)INSERTsalary_levelVALUES('講師',6500,8500)INSERTsalary_levelVALUES('助教',5900,6900)創(chuàng)建觸發(fā)器

【例11-25】在教師工資表上創(chuàng)建一個(gè)觸發(fā)器，用于實(shí)現(xiàn)復(fù)雜的約束：在對(duì)教師的工資進(jìn)行錄入和修改時(shí)，按職稱(chēng)級(jí)別進(jìn)行約束。CREATETRIGGERteacher_sala1ONteacher_salaryFORINSERT,UPDATEASDECLARE@minsalaryint,@maxsalaryint,@salaryint,@profnvarchar(10),@tnamenvarchar(6)SELECT@minsalary=minsalary,@maxsalary=maxsalary,@salary=i.salary,@prof=t.prof_title,@tname=i.tname創(chuàng)建觸發(fā)器FROMinsertedi,salary_levels,teachertwheres.prof_title=t.prof_titleandt.tno=i.tnoIFNOT(@salaryBETWEEN@minsalaryAND@maxsalary)BEGIN

PRINT@tname+'的職稱(chēng)為:'+@prof+'工資應(yīng)該在'+str(@minsalary)+'到'+str(@maxsalary)+'之間。'ROLLBACKEND利用命令觸發(fā)該觸發(fā)器：insertteacher_salaryvalues(1,

'鄭浩',9800)創(chuàng)建觸發(fā)器[例11-26]在教師工資表上創(chuàng)建一個(gè)觸發(fā)器，用于實(shí)現(xiàn)特殊的約束：規(guī)定每月的10號(hào)前發(fā)工資，即對(duì)教師的工資進(jìn)行錄入時(shí)，觸發(fā)此觸發(fā)器，時(shí)間不對(duì)不能錄入。CREATETRIGGERteacher_sala2ONteacher_salaryFORINSERTASdeclare@dintset@d=day(getdate())IF@d>10BEGINPRINT'只能在每月的10號(hào)以前發(fā)工資,今天是'+str(@d)+'號(hào)。'ROLLBACKEND利用命令觸發(fā)該觸發(fā)器：insertteacher_salaryvalues(2,'王偉',7200)updateteacher_salarysetsalary=9400wheretno=1創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器[例11-27]觸發(fā)器中調(diào)用存儲(chǔ)過(guò)程。首先創(chuàng)建一個(gè)存儲(chǔ)過(guò)程p1如下：CREATEPROCp1ASSelect*fromstudent然后，為學(xué)生表創(chuàng)建一個(gè)觸發(fā)器tr1，在插入、修改或刪除數(shù)據(jù)時(shí)，都會(huì)調(diào)用存儲(chǔ)過(guò)程p1。CREATETRIGGERtr1onstudentforinsert,update,deleteASEXECp1創(chuàng)建觸發(fā)器[例11-28]主要針對(duì)某些列實(shí)施監(jiān)控的列級(jí)觸發(fā)器。首先，建立登記修改人帳號(hào)的表change_user，