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

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

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

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

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

7.6存儲過程2．存儲過程的優(yōu)點（1）使用存儲過程可以降低網絡的流量（2）提高了性能（3）存儲過程允許用戶進行模塊化的程序設計用戶可以很快的創(chuàng)建一個存儲過程，然后把它存儲到數(shù)據(jù)庫中，并可在自己的應用程序中多次調用它。（4）存儲過程還可以作為安全機制的一部分存儲過程的分類系統(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列出服務器上的所有數(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ā)器或視圖的實際文本。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ù)庫的名稱(單用戶訪問)列出當前系統(tǒng)中的數(shù)據(jù)庫當前數(shù)據(jù)庫中查詢的對象的列表返回某個表列的信息查看表stuInfo的信息查看表stuInfo的約束查看表stuMarks的索引查看視圖的語句文本查看當前數(shù)據(jù)庫中的存儲過程常用的擴展存儲過程：xp_cmdshell

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

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)建一個存儲過程，它將顯示出特定學生的信息。CREATEPROCEDUREprc_DisplayStudentDetails@iStuIDintASBEGINPRINTconvert(char,@iStuID)+'details'SELECT*FROMstuWHEREStuID=@iStuIDEND7.7創(chuàng)建存儲過程執(zhí)行該存儲過程，查找學號為“20070103”學生的信息。execprc_DisplayStudentDetails200701037.7創(chuàng)建存儲過程若存儲過程的查詢條件是可變的，根據(jù)用戶在執(zhí)行該存儲過程時，由用戶輸入來決定。語法格式：CREATEPROC[EDURE]procedure_name

[{@parameterdata_type}AS

sql_statements參數(shù)：作為存儲過程與調用它的對象之間交換數(shù)據(jù)的一種方法。7.7創(chuàng)建存儲過程參數(shù)的類型：輸入參數(shù)——它用來接收調用者向存儲過程傳遞(單向)的數(shù)據(jù)。輸出參數(shù)——用來向調用者返回一個或多個數(shù)據(jù)。7.7創(chuàng)建存儲過程從存儲過程中返回數(shù)據(jù)1．用return語句返回值2．利用輸出參數(shù)返回多個值7.7創(chuàng)建存儲過程例7.14編寫一個存儲過程，根據(jù)學號顯示該生的詳細信息，如果沒有找到返回值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)建存儲過程要從存儲過程中返回的信息不止一個值，選擇用“輸出參數(shù)”來實現(xiàn)。語法格式：CREATEPROCEDUREprocedure_name[{@parameterdatatype}[OUTPUT]][,…n]ASSql_statements7.7創(chuàng)建存儲過程例7.15創(chuàng)建一個存儲過程，當學生要轉班的時候，輸入學生的學號和要轉到的班級號，修改stu表中的Class，存儲過程執(zhí)行完后返回學生的姓名(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