觸發(fā)器與存儲過程課件

第七章觸發(fā)器與存儲過程學(xué)習(xí)目標(biāo)掌握觸發(fā)器的類型；掌握觸發(fā)器創(chuàng)建，修改和刪除；掌握存儲過程的創(chuàng)建和使用。學(xué)習(xí)重點(diǎn)觸發(fā)器的創(chuàng)建；inserted表和deleted表的使用；存儲過程的創(chuàng)建。7.1觸發(fā)器概述當(dāng)在指定表中使用UPDATE、INSERT或DELETE中一種或多種數(shù)據(jù)修改操作對數(shù)據(jù)進(jìn)行修改時(shí)，觸發(fā)器會生效。SQLServer2000將觸發(fā)器和觸發(fā)它的語句作為一個(gè)事務(wù)對待，若條件不滿足，可以回滾整個(gè)事務(wù)。觸發(fā)器主要用于強(qiáng)制復(fù)雜的業(yè)務(wù)規(guī)則或要求。

7.1觸發(fā)器概述觸發(fā)器的優(yōu)點(diǎn)如下：1．觸發(fā)器是被動的：它們在對表的數(shù)據(jù)作了任何修改之后立即被激活。2．觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進(jìn)行級聯(lián)更改。3．觸發(fā)器可以強(qiáng)制限制，這些限制比用CHECK約束所定義的更復(fù)雜。

7.1觸發(fā)器概述根據(jù)觸發(fā)器的執(zhí)行順序，可將觸發(fā)器分為：FOR/AFTER觸發(fā)器和INSTEADOF觸發(fā)器。FOR/AFTER觸發(fā)器：都是在觸發(fā)觸發(fā)器的INSERT，UPDATE和DELETE語句執(zhí)行完成之后執(zhí)行的。INSTEADOF觸發(fā)器：不執(zhí)行觸發(fā)觸發(fā)器的操作（INSERT，UPDATE和DELELTE語句），只完成觸發(fā)的動作。即用觸發(fā)器中定義的操作替代觸發(fā)操作。

7.2創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的T-SQL語句為CREATETRIGGER，其語法為：CREATETRIGGERtrigger_name

ON{tablename|viewname}

[WITHENCRYPTION]

{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

AS

[{IFUPDATE()

[{AND|OR}UPDATE(column_name)

][...n]

sql_statement[

...n]

}

7.2創(chuàng)建觸發(fā)器例7.1當(dāng)向Stu表添加記錄完成時(shí)，返回一條信息“操作完成”。USEstudentsGOCREATETRIGGERtri_stuONStuFORINSERTASPRINT'操作完成'驗(yàn)證觸發(fā)器是否能正常工作，向Stu表添加一條記錄。INSERTStuVALUES('王二','女','1990-10-3','','計(jì)算機(jī)軟件','1001',NULL)7.2創(chuàng)建觸發(fā)器定義觸發(fā)器的注意事項(xiàng)：1．CREATETRIGGER必須是批處理中的第一條語句，并且只能應(yīng)用到一個(gè)表中。2．觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建，不過觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫的外部對象。3．觸發(fā)器中不允許使用下列T-SQL語句：ALTERDATABASEDISKRESIZECREATEDATABASEDISKINITDROPDATABASELOADDATABASERECONFIGURELOADLOGRESTORELOGRESTOREDATABASE7.3管理觸發(fā)器例7．2修改例７．1中創(chuàng)建的觸發(fā)器，將其改為當(dāng)對Stu表進(jìn)行插入（INSERT），修改（UPDATE）和刪除（DELETE）時(shí)，都顯示完成信息。USEstudentsGOALTERTRIGGERtri_stuONStuFORINSERT，UPDATE，DELETEASPRINT'操作完成'7.3管理觸發(fā)器若要使用T-SQL語句查看觸發(fā)器的定義，可使用sp_helptext存儲過程。其語法格式為：EXECsp_helptexttrigger_name若要了解在某個(gè)表上存儲哪些觸發(fā)器可以使用存儲過程sp_helptrigger，語法格式為：[EXEC]sp_helptriggertable_name啟用和禁用觸發(fā)器的語法格式為:ALTERTABLEtable_namedisable|enableTRIGGERtrigger_name7.3管理觸發(fā)器例7.3再次修改例1中創(chuàng)建的觸發(fā)器，將其文本內(nèi)容加密。USEstudentsGOALTERTRIGGERtri_stuONStuWITHENCRYPTIONFORINSERT,UPDATE,DELETEASPRINT’操作完成’7.4INSERTED表和DELETED表觸發(fā)器有兩個(gè)重要的臨時(shí)表：INSERTED表和DELETED表。當(dāng)向數(shù)據(jù)表中插入數(shù)據(jù)時(shí)，觸發(fā)INSERT觸發(fā)器，新插入的記錄的副本存儲在INSERTED表中，INSERTED表與數(shù)據(jù)有相同的表結(jié)構(gòu)。當(dāng)從數(shù)據(jù)表中刪除數(shù)據(jù)時(shí)，觸發(fā)DELETE觸發(fā)器，被刪除的記錄的副本存儲在DELETED表中。DELETED表與數(shù)據(jù)有相同的表結(jié)構(gòu)。7.4INSERTED表和DELETED表修改數(shù)據(jù)，可以看作是將原記錄刪除，插入一條新記錄。因此，當(dāng)在數(shù)據(jù)表中修改數(shù)據(jù)時(shí)，觸發(fā)UPDATE觸發(fā)器，原記錄的副本存儲在DELETED表中，修改后的記錄的副本存儲在INSERTED表中。7.4INSERTED表和DELETED表例7.6定義一個(gè)觸發(fā)器，課程號（CouID）以“B”開頭為選修課，則學(xué)生成績（Score）低于60分的不錄入。USEstudentsGOCREATETRIGGERtri_BScoreONScoreFORINSERTASIF(SELECTScoreFROMINSERTEDWHERECouIDLike'B%')<60BEGIN ROLLBACKTRAN PRINT'該成績不能錄入'END7.4INSERTED表和DELETED表例7.8當(dāng)對Scores表的Score字段進(jìn)行更新時(shí)，取消更新操作，并顯示信息“學(xué)生成績不能被修改，請與教務(wù)處聯(lián)系”。USEstudentsGOCREATETRIGGERtri_scONScoresFORUPDATEASIFUPDATE（Score）BEGINROLLBACKTRANPRINT'學(xué)生成績不能被修改，請與教務(wù)處聯(lián)系'END7.4INSERTED表和DELETED表例7.9定義一個(gè)觸發(fā)器，不允許將學(xué)生的成績修改為100分或60分。USEstudentsGOCREATETRIGGERtri_update_scoreONScoresFORUPDATEASIF（SELECTScoreFROMINSERTED）=100OR（SELECTScoreFROMINSERTED）=60BEGIN PRINT’不能將學(xué)生的成績修改為100分或60分’ ROLLBACKTRANEND7.5觸發(fā)器應(yīng)用INSTEADOF觸發(fā)器包含替代原操作（INSERT，UPDATE，DELETE）語句的代碼。通常用于在不能直接更新的視圖上，替代原操作，使其更新。在用戶看來是直接在不可更新的視圖上更新了。

7.5觸發(fā)器應(yīng)用例7.10能夠直接刪除視圖stu_dep中的記錄。1．定義觸發(fā)器USEstudentsGOCREATETRIGGERtri_stuONstu_depINSTEADOFDELETEASDELETEStuWHEREStuIDIN（SELECTStuIDFROMDELETED）GO7.5觸發(fā)器應(yīng)用2．使用刪除語句DELETEstu_depWHEREStuID=‘20070105’3．查看結(jié)果SELECT*FROMstu_dep7.5觸發(fā)器應(yīng)用RAISERROR({msg_id|msg_str}{,severity,state}說明:msg_id:存儲于sysmessages表中的用戶定義的錯(cuò)誤信息。用戶定義錯(cuò)誤信息的錯(cuò)誤號應(yīng)大于50,000。由特殊消息產(chǎn)生的錯(cuò)誤是第50,000號。Severity:用戶定義的與消息關(guān)聯(lián)的嚴(yán)重級別。用戶可以使用從0到18之間的嚴(yán)重級別。7.6存儲過程存儲過程(storedprocedure)是一組經(jīng)過預(yù)先編澤的SQL代碼，存放在服務(wù)器中。用戶可以調(diào)用一個(gè)單獨(dú)的存儲過程得到相應(yīng)的返回值，從而完成一系列的操作。

7.6存儲過程2．存儲過程的優(yōu)點(diǎn)（1）使用存儲過程可以降低網(wǎng)絡(luò)的流量（2）提高了性能（3）存儲過程允許用戶進(jìn)行模塊化的程序設(shè)計(jì)用戶可以很快的創(chuàng)建一個(gè)存儲過程，然后把它存儲到數(shù)據(jù)庫中，并可在自己的應(yīng)用程序中多次調(diào)用它。（4）存儲過程還可以作為安全機(jī)制的一部分存儲過程的分類系統(tǒng)存儲過程由系統(tǒng)定義，存放在master數(shù)據(jù)庫中類似C語言中的系統(tǒng)函數(shù)系統(tǒng)存儲過程的名稱都以“sp_”開頭或”xp_”開頭用戶自定義存儲過程由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程類似C語言中的用戶自定義函數(shù)7.6存儲過程7.6存儲過程-常用的系統(tǒng)存儲過程4-1系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實(shí)際文本。EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures

7.6存儲過程-常用的系統(tǒng)存儲過程4-2修改數(shù)據(jù)庫的名稱(單用戶訪問)列出當(dāng)前系統(tǒng)中的數(shù)據(jù)庫當(dāng)前數(shù)據(jù)庫中查詢的對象的列表返回某個(gè)表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當(dāng)前數(shù)據(jù)庫中的存儲過程常用的擴(kuò)展存儲過程：xp_cmdshell

可以執(zhí)行DOS命令下的一些的操作以文本行方式返回任何輸出調(diào)用語法：

EXECxp_cmdshellDOS命令[NO_OUTPUT]7.6存儲過程-常用的系統(tǒng)存儲過程4-37.6存儲過程-常用的系統(tǒng)存儲過程4-4USEmasterGOEXECxp_cmdshell'mkdird:\bank',NO_OUTPUTIFEXISTS(SELECT*FROMsysdatabasesWHEREname='bankDB')DROPDATABASEbankDBGOCREATEDATABASEbankDB(…)GOEXECxp_cmdshell'dirD:\bank\'--查看文件創(chuàng)建數(shù)據(jù)庫bankDB，要求保存在D:\bank創(chuàng)建文件夾D:\bank查看文件夾D:\bank7.7創(chuàng)建存儲過程創(chuàng)建存儲過程語法格式：CREATEPROC[EDURE]procedure_name

AS

sql_statements系統(tǒng)存儲過程帶有“sp_”前綴，用戶自定義的存儲過程名稱一般以prc作為前綴。

7.7創(chuàng)建存儲過程存儲過程不會自動執(zhí)行，執(zhí)行存儲過程的方法有：（1）EXECUTE存儲過程名；（2）EXEC存儲過程名；（3）存儲過程名。7.7創(chuàng)建存儲過程例7.13創(chuàng)建一個(gè)存儲過程，它將顯示出特定學(xué)生的信息。CREATEPROCEDUREprc_DisplayStudentDetails@iStuIDintASBEGINPRINTconvert(char,@iStuID)+'details'SELECT*FROMstuWHEREStuID=@iStuIDEND7.7創(chuàng)建存儲過程執(zhí)行該存儲過程，查找學(xué)號為“20070103”學(xué)生的信息。execprc_DisplayStudentDetails200701037.7創(chuàng)建存儲過程若存儲過程的查詢條件是可變的，根據(jù)用戶在執(zhí)行該存儲過程時(shí)，由用戶輸入來決定。語法格式：CREATEPROC[EDURE]procedure_name

[{@parameterdata_type}AS

sql_statements參數(shù)：作為存儲過程與調(diào)用它的對象之間交換數(shù)據(jù)的一種方法。7.7創(chuàng)建存儲過程參數(shù)的類型：輸入?yún)?shù)——它用來接收調(diào)用者向存儲過程傳遞(單向)的數(shù)據(jù)。輸出參數(shù)——用來向調(diào)用者返回一個(gè)或多個(gè)數(shù)據(jù)。7.7創(chuàng)建存儲過程從存儲過程中返回?cái)?shù)據(jù)1．用return語句返回值2．利用輸出參數(shù)返回多個(gè)值7.7創(chuàng)建存儲過程例7.14編寫一個(gè)存儲過程，根據(jù)學(xué)號顯示該生的詳細(xì)信息，如果沒有找到返回值10。CREATEPROCEDUREprc_SearchStudent@studentidintASBEGINPRINT'STUDENTDETAILS'SELECT*FROMstuWHEREStuID=@studentidIF(@@rowcount=0)RETURN10END7.7創(chuàng)建存儲過程執(zhí)行該存儲過程DECLARE@countintexec@count=prc_SearchStudent20070111PRINT'@count='+convert(char,@count)

7.7創(chuàng)建存儲過程要從存儲過程中返回的信息不止一個(gè)值，選擇用“輸出參數(shù)”來實(shí)現(xiàn)。語法格式：CREATEPROCEDUREprocedure_name[{@parameterdatatype}[OUTPUT]][,…n]ASSql_statements7.7創(chuàng)建存儲過程例7.15創(chuàng)建一個(gè)存儲過程，當(dāng)學(xué)生要轉(zhuǎn)班的時(shí)候，輸入學(xué)生的學(xué)號和要轉(zhuǎn)到的班級號，修改stu表中的Class，存儲過程執(zhí)行完后返回學(xué)生的姓名(StuName)、性別(Sex)。7.7創(chuàng)建存儲過程CREATEPROCEDUREprc_UpdateClass@iStuIDint,@cClasschar(16),@cStuNamechar(10)OUTPUT,@cSexchar(2)OUTPUTASBEGINIFEXISTS(SELECT*FROMstuWHEREStuID=@iStuID)BEGINUPDATEstuSETClass=@cClassWHEREStuID=@iStuIDSELECT@cStuName=StuName,@cSex=SexFROMstuWHEREStuID=@iStuIDRETURN0ENDELSERETURN1END7.7創(chuàng)建存儲過程執(zhí)行帶參數(shù)的存儲過程DECLARE@namechar(10)DECLARE@sexchar(2)DECLARE@resultintEXEC@result=prc_UpdateClass'20070110','05市場營銷',@nameOUTPUT,@sexOUTPUTIF(@result=0)BEGINPRINT'THESTUDENTDETAILS'

PRINT'NAME:'+@namePRINT'SEX:'+@sexENDELSEPRINT'NOTFOUND!!'7.7創(chuàng)建存儲過程例7.16創(chuàng)建一存儲過程，它將分行顯示存儲過程prc_UpdateClass中返回的信息。CREATEPROCEDUREprc_Executeprc@iStuIDint,@cClasschar(16)ASBEGINDECLARE@namechar(10)DECLARE@sexchar(2)DECLARE@resultintEXEC@result=prc_UpdateClass@iStuID,@cClass,@nameOUTPUT,@sexOUTPUT

7.7創(chuàng)建存儲過程IF(@result=0)BEGINPRINT'THESTUDENTDETAILS'PRINT'NAME:'+@namePRINT'SEX:'+@sexENDELSEPRINT'NOTFOUND!!'END執(zhí)行存儲過程Prc_Executeprc'20070110','05市場營銷'7.8修改和刪除存儲過程修改存儲過程所要用到的語句語法格式：ALTERPROC[EDURE] procedure_n