SQL Server 數(shù)據(jù)庫技術(shù) 存儲過程和觸發(fā)器的創(chuàng)建與維護(hù)

會計學(xué)1SQLServer數(shù)據(jù)庫技術(shù)存儲過程和觸發(fā)器的創(chuàng)建與維護(hù)本章學(xué)習(xí)目標(biāo)了解存儲過程的概念；掌握創(chuàng)建、執(zhí)行存儲過程的方法；掌握查看和修改存儲過程的方法；掌握刪除存儲過程的方法；了解觸發(fā)器和一般存儲過程的主要區(qū)別；掌握創(chuàng)建和查看觸發(fā)器的方法；掌握修改和刪除觸發(fā)器的方法。第1頁/共120頁8.1概述

8.1.1存儲過程概述“存儲過程”就是將常用的或很復(fù)雜的工作,預(yù)先以SQL程序?qū)懞?然后指定一個程序名稱保存起來,以后只要用EXCUTE指令來執(zhí)行這個程序,即可完成該項(xiàng)工作.第2頁/共120頁8.1.1存儲過程概述應(yīng)該提倡多使用存儲過程，原因有以下幾點(diǎn)。（1）實(shí)現(xiàn)了模塊化編程。（2）調(diào)用一次以后，相關(guān)信息就保存在內(nèi)存中，下次調(diào)用時可以直接執(zhí)行。（3）存儲過程可以接受輸入?yún)?shù)并可以返回輸出值。（4）存儲過程具有對數(shù)據(jù)庫立即訪問的功能。（5）使用存儲過程可以加快程序的運(yùn)行速度。（6）使用存儲過程可以減少網(wǎng)絡(luò)流量。（7）使用存儲過程可以提高數(shù)據(jù)庫的安全性。第3頁/共120頁8.1.1存儲過程概述

存儲過程分為兩類：系統(tǒng)存儲過程和用戶自定義的存儲過程。系統(tǒng)存儲過程是由系統(tǒng)自動創(chuàng)建的，主要存儲在master數(shù)據(jù)庫中，一般以sp_為前綴。系統(tǒng)存儲過程完成的功能主要是從系統(tǒng)表中獲取信息。進(jìn)行系統(tǒng)的各項(xiàng)設(shè)置、相關(guān)管理工作。用戶自定義存儲過程由用戶創(chuàng)建并能完成某一特定功能的存儲過程。會被加入所屬數(shù)據(jù)庫的存儲過程項(xiàng)目中，并以對象的形式保存。第4頁/共120頁8.2存儲過程

8.2.1創(chuàng)建存儲過程創(chuàng)建存儲過程的三種方法：使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。使用企業(yè)管理器創(chuàng)建存儲過程。使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過程。第5頁/共120頁1．使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程（1）在企業(yè)管理器中，要創(chuàng)建存儲過程的數(shù)據(jù)庫，選擇“工具”菜單中的“向?qū)А辈藛雾?xiàng)，單擊向?qū)е小皵?shù)據(jù)庫”選項(xiàng)左邊的加號，選中“創(chuàng)建存儲過程向?qū)А边x項(xiàng)。第6頁/共120頁

（2）單擊“確定”按鈕。出現(xiàn)“歡迎使用創(chuàng)建存儲過程”對話框，如圖所示。第7頁/共120頁（3）選擇數(shù)據(jù)庫對話框．選擇存放存儲過程的數(shù)據(jù)庫。第8頁/共120頁（4）“選擇存儲過程”對話框。選擇存儲過程中要針對哪些表做哪些操作。第9頁/共120頁（5）確認(rèn)存儲過程信息對話框向?qū)槍γ恳粋€數(shù)據(jù)表的每一個操作，都產(chǎn)生單獨(dú)的存儲過程來處理。按此按鈕可編輯選定項(xiàng)目的設(shè)置。第10頁/共120頁（6）“編輯存儲過程屬性”對話框更改存儲過程的名稱。按此按鈕可直接修改SQL程序代碼。executeinsert_讀者基本信息表_1'67','女','fggg',7,9第11頁/共120頁2．使用SQLServer2000企業(yè)管理器創(chuàng)建存儲過程步驟如下：（1）在企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫，右擊要創(chuàng)建存儲過程的數(shù)據(jù)庫，在彈出的快捷菜單中依次選擇“新建”→“存儲過程…”命令。第12頁/共120頁（2）在文本框中可以輸入創(chuàng)建存儲過程的T-SQL語句。（3）輸入完畢可單擊“檢查語法”按鈕。（4）最后單擊“確定”保存。默認(rèn)的樣板。CREATEPROCEDURE存儲過程_讀者信息ASSELECT讀者編號,姓名,性別,所在部門from讀者基本信息表第13頁/共120頁3．使用Transact-SQL語句創(chuàng)建存儲過程可以使用Transact-SQL語句中的CREATEPROCEDURE命令創(chuàng)建存儲過程，只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。第14頁/共120頁3．使用Transact-SQL語句創(chuàng)建存儲過程語法形式如下：CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]第15頁/共120頁procedure_name：用于指定所要創(chuàng)建存儲過程的名稱。各參數(shù)的含義：第16頁/共120頁@parameter：過程中的參數(shù)。在CREATEPROCEDURE語句中可以聲明一個或多個參數(shù)。執(zhí)行時應(yīng)提供相應(yīng)的實(shí)在參數(shù).data_type：用于指定參數(shù)的數(shù)據(jù)類型。default：用于指定參數(shù)的默認(rèn)值(常量)。各參數(shù)的含義：

[{@parameterdata_type}

第17頁/共120頁AS：用于指定該存儲過程要執(zhí)行的操作。sql_statement：是存儲過程中包含的任意數(shù)目和類型的Transact-SQL語句。各參數(shù)的含義：第18頁/共120頁CREATEPROCEDURE<存儲過程名>[{@參數(shù)名><數(shù)據(jù)類型>}]AS{<SQL語句>|<語句塊>}第19頁/共120頁例:從STUDENT數(shù)據(jù)庫的三個表中查詢,返回學(xué)生學(xué)號、姓名、課程名、成績、學(xué)分。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info'ANDtype='P')DROPPROCEDUREstudent_infoGOCREATEPROCEDUREstudent_infoASSelecta.學(xué)號,姓名,課程名,成績,學(xué)分Fromstudenta,scb,coursecWHEREa.學(xué)號=b.學(xué)號Andb.課號=c.課號GOEXECUTEstudent_info簡單的存儲過程第20頁/共120頁練習(xí)：從STUDENT數(shù)據(jù)庫的三個表中查詢某人指定課程的成績和學(xué)分。該存儲過程接受與傳遞參數(shù)精確匹配的值。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info1'ANDtype='P')DROPPROCEDUREstudent_info1GOCREATEPROCEDUREstudent_info1@namechar(8),@cnamechar(16)ASSelecta.學(xué)號,姓名,課程名,成績,學(xué)分Fromstudenta,scb,coursecWHEREa.學(xué)號=b.學(xué)號Andb.課號=c.課號anda.姓名=@nameandc.課程名=@cnameGO帶參數(shù)的存儲過程第21頁/共120頁EXECUTEstudent_info1'王麗','計算機(jī)基礎(chǔ)'EXECUTEstudent_info1@name='王麗',@cname='計算機(jī)基礎(chǔ)‘第22頁/共120頁練習(xí)：從STUDENT數(shù)據(jù)庫的三個表中返回指定學(xué)生的學(xué)號、姓名、所選課程名及該課的成績。該存儲過程在參數(shù)中可使用模式匹配，如果沒提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值．USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='student_info3'ANDtype='P')DROPPROCEDUREstudent_info3GOCREATEPROCEDUREstudent_info3@namevarchar(30)='劉%'ASSelecta.學(xué)號,姓名,課程名,成績Fromstudenta,scb,coursecWHEREa.學(xué)號=b.學(xué)號Andb.課號=c.課號and姓名like@nameGO帶參數(shù)的存儲過程第23頁/共120頁EXECUTEstudent_info3/*參數(shù)使用默認(rèn)值*/EXECUTEstudent_info3'王%'/*傳遞給＠name的實(shí)參為‘王％’*/EXECUTEstudent_info3'[王張]%'[]（方括號）指定范圍（[a-f]）或集合（[abcdef]）中的任何單個字符。第24頁/共120頁

創(chuàng)建一個帶有參數(shù)的存儲過程“存儲過程_讀者借閱信息”，該存儲過程根據(jù)傳入的讀者編號，返回此人的基本信息及此人借閱的圖書信息。例8-1第25頁/共120頁USEBOOKSIFEXISTS(SELECTnameFROMsysobjectsWHEREname='存儲過程_讀者借閱信息'ANDtype='P')DROPPROCEDURE存儲過程_讀者借閱信息GOCREATEPROCEDURE存儲過程_讀者借閱信息

@讀者編號varchar(12)ASSelect讀者基本信息表.讀者編號,姓名,性別,所在部門,圖書借閱信息表.圖書編碼,書名,借閱日期From圖書基本信息表,圖書借閱信息表,讀者基本信息表WHERE讀者基本信息表.讀者編號=圖書借閱信息表.讀者編號and圖書基本信息表.圖書編碼=圖書借閱信息表.圖書編碼And讀者基本信息表.讀者編號=@讀者編號GOexec存儲過程_讀者借閱信息'JSJ03'第26頁/共120頁

如果student表中男生入學(xué)成績的平均值高于女生入學(xué)成績的平均值則顯示“男生的入學(xué)成績平均值高于女生的入學(xué)成績平均值”，否則顯示“男生的入學(xué)成績平均值低于女生的入學(xué)成績平均值”.補(bǔ)例1：建立判斷男學(xué)生與女學(xué)生的平均入學(xué)成績高低的存儲過程。第27頁/共120頁USEstudentGOCREATEPROCEDURE比較男女生入學(xué)成績ASIf((selectavg(入學(xué)成績)FromstudentWhere性別='男')>(selectavg(入學(xué)成績)FromstudentWhere性別='女'))Print'男生的入學(xué)成績平均值高于女生的入學(xué)成績平均值'ElsePrint'男生的入學(xué)成績平均值低于女生的入學(xué)成績平均值'GO第28頁/共120頁參數(shù)傳遞技巧執(zhí)行存儲過程時,若未指明參數(shù)名稱,則必須依照存儲過程所需的參數(shù)依次傳過去;而且除非該參數(shù)指定有默認(rèn)值,否則不可省略.第29頁/共120頁補(bǔ)例2:CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGO第30頁/共120頁CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGOEXECtest

GOEXECtest1

GOEXECtest1,default

GOEXECtest1,default,5

GOEXECtest1,2,5

GOEXECtest/*錯誤,第一個參數(shù)不可省略*/GOEXECtest1/*OK,第2、3參數(shù)用默認(rèn)值*/GOEXECtest1,default/*OK,可用default表示使用默認(rèn)值*/GOEXECtest1,default,5/*OK*/GOEXECtest1,2,5/*OK*/GO第31頁/共120頁也可以使用在存儲過程中聲明的參數(shù)名稱，以“@name=value”格式來指明傳入?yún)?shù)的對應(yīng)位置。例：EXECtest@c=5,@b=DEFAULT,@a=1/*不用按順序傳了*/判斷正誤：EXECtest1,@c=2EXECtest@c=2,1GOEXECtest@c=5EXECtest1,@c=2/*OK!1傳入@a,而@b使用默認(rèn)值*/GOEXECtest@c=2,1/*錯誤!因?yàn)槭褂眠^@name=value后就必須直使用此方式來傳參數(shù)*/GOEXECtest@c=5/*錯誤!因?yàn)锧a參數(shù)不可省略*/CREATEPROCEDUREtest@aint,@bint=NULL,@cint=3ASSELECT@a,@b,@cGO第32頁/共120頁8.2.2管理存儲過程1．查看存儲過程

存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。

可以使用企業(yè)管理器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。第33頁/共120頁1．查看存儲過程

在企業(yè)管理器中查看用戶創(chuàng)建的存儲過程的方法如下:1）在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫項(xiàng)，并單擊存儲過程文件夾，此時在右邊的窗格中就會顯示出數(shù)據(jù)庫中的所有存儲過程。第34頁/共120頁（2）右擊要查看的存儲過程，從彈出的快捷菜單中選擇“屬性”命令，會彈出“存儲過程屬性”對話框。第35頁/共120頁(3)右擊存儲過程“存儲過程_讀者借閱信息”,從彈出的快捷菜單中依次選擇”所有任務(wù)”-”顯示相關(guān)性”命令,會彈出相關(guān)對話框,顯示與選擇的存儲過程有依賴關(guān)系的其他數(shù)據(jù)庫對象的名稱,如圖所示.第36頁/共120頁使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程可供使用的系統(tǒng)存儲過程及其語法形式如下：（1）sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型

sp_help[[@objname=]name]參數(shù)name為要查看的存儲過程的名稱。第37頁/共120頁例8-2使用系統(tǒng)存儲過程查看“存儲過程_讀者借閱信息”的參數(shù)及其數(shù)據(jù)類型。程序清單如下：--查看存儲過程的參數(shù)及其數(shù)據(jù)類型USEBOOKSGOsp_help存儲過程_讀者借閱信息GO第38頁/共120頁（2）sp_helptext：用于顯示存儲過程的源代碼sp_helptext[[@objname=]name]參數(shù)name為要查看的存儲過程的名稱。第39頁/共120頁例:使用系統(tǒng)存儲過程查看“存儲過程_讀者借閱信息”的源代碼。程序清單如下：--查看存儲過程的參數(shù)及其數(shù)據(jù)類型USEBOOKSGOsp_helptext

存儲過程_讀者借閱信息GO第40頁/共120頁（3）sp_depends：用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象sp_depends[@objname=]’object’

參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱。第41頁/共120頁例8-3使用系統(tǒng)存儲過程查看“存儲過程_讀者借閱信息”的相關(guān)的數(shù)據(jù)庫對象。程序清單如下：--查看相關(guān)的數(shù)據(jù)庫對象USEBOOKSGOsp_depends存儲過程_讀者借閱信息GO第42頁/共120頁（4）sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表sp_stored_procedures[[@sp_name=]'name'][,[@sp_owner=]'owner'][,[@sp_qualifier=]'qualifier']

用于指定返回目錄信息的過程名。用于指定過程所有者的名稱；用于指定過程限定符的名稱。（表示數(shù)據(jù)庫名稱)第43頁/共120頁USEBOOKSExecsp_stored_proceduresGO第44頁/共120頁USEBOOKSExecsp_stored_procedures存儲過程_讀者借閱信息,dbo,booksGO第45頁/共120頁USEBOOKSexecsp_stored_procedures'%借%'GO第46頁/共120頁2．修改存儲過程定義(1)在企業(yè)管理器中，單擊目錄樹中的存儲過程圖標(biāo)，在窗口的右側(cè)右擊要修改的存儲過程，從彈出的快捷菜單中選擇“屬性”命令，則會出現(xiàn)存儲過程屬性對話框。第47頁/共120頁(2)T-SQL語句的修改存儲過程:其語法形式如下：ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]第48頁/共120頁簡要格式:ALTERPROCEDURE<存儲過程名>[{@參數(shù)名><數(shù)據(jù)類型>}]AS{<SQL語句>|<語句塊>}第49頁/共120頁練習(xí)：創(chuàng)建名為select_students的存儲過程，默認(rèn)情況下，該過程可查詢所有學(xué)生信息；

再重新改為能檢索計算機(jī)專業(yè)的學(xué)生信息的存儲過程。USESTUDENTIFEXISTS(SELECTnameFROMsysobjectsWHEREname='select_students'ANDtype='P')DROPPROCEDUREselect_studentsGOCREATEPROCEDUREselect_studentsASSelect*Fromstudentorderby學(xué)號GO建立過程第50頁/共120頁ALTERPROCEDUREselect_studentsASSelect*Fromstudentwhere院系名稱='計算機(jī)系'orderby學(xué)號GO修改過程第51頁/共120頁3．重命名存儲過程(1)在企業(yè)管理器中，右擊要操作的存儲過程名稱，從彈出的快捷菜單中選擇“重命名”命令，或者雙擊存儲過程名稱，當(dāng)存儲過程名稱變成可輸入狀態(tài)時，就可以直接修改該存儲過程的名稱了。第52頁/共120頁(2)也可以使用系統(tǒng)存儲過程sp_rename修改存儲過程的名稱，其語法形式如下：sp_rename原存儲過程名稱，新存儲過程名稱第53頁/共120頁8.2.3執(zhí)行存儲過程

在SQLServer2000中可以使用EXECUTE命令來直接執(zhí)行存儲過程，語法形式如下：

[EXEC[UTE]]存儲過程名[參數(shù)]

第54頁/共120頁程序清單如下：USEBOOKSEXEC存儲過程_讀者信息或直接寫存儲過程的名稱：USEBOOKSGO存儲過程_讀者信息例8-4：執(zhí)行前面創(chuàng)建的無參存儲過程“存儲過程_讀者信息”。第55頁/共120頁存儲過程_讀者借閱信息創(chuàng)建一個帶有參數(shù)的存儲過程“存儲過程_讀者借閱信息”，該存儲過程根據(jù)傳入的讀者編號，返回此人的基本信息及此人借閱的圖書信息。第56頁/共120頁條件（再運(yùn)行一下）USEBOOKSIFEXISTS(SELECTnameFROMsysobjectsWHEREname='存儲過程_讀者借閱信息'ANDtype='P')DROPPROCEDURE存儲過程_讀者借閱信息GOCREATEPROCEDURE存儲過程_讀者借閱信息

@讀者編號varchar(12)ASSelect讀者基本信息表.讀者編號,姓名,性別,所在部門,圖書借閱信息表.圖書編碼,書名,借閱日期From圖書基本信息表,圖書借閱信息表,讀者基本信息表WHERE讀者基本信息表.讀者編號=圖書借閱信息表.讀者編號and圖書基本信息表.圖書編碼=圖書借閱信息表.圖書編碼And讀者基本信息表.讀者編號=@讀者編號GO第57頁/共120頁例8-5執(zhí)行有參存儲過程“存儲過程_讀者借閱信息”，該存儲過程有一個輸入?yún)?shù)“讀者編號”。程序清單如下：USEBOOKSGOEXECUTE存儲過程_讀者借閱信息'JSJ03'或：USEBOOKSGOEXECUTE存儲過程_讀者借閱信息@讀者編號='JSJ03’第58頁/共120頁8.2.4刪除存儲過程1．使用企業(yè)管理器刪除存儲過程

在企業(yè)管理器中，右擊要刪除的存儲過程，從彈出的快捷菜單中選擇“刪除”命令，會彈出“除去對象”對話框。在該對話框中，單擊“全部除去”按鈕，即可完成刪除操作。第59頁/共120頁2．使用Transact-SQL語句刪除存儲過程刪除存儲過程也可以使用Transact-SQL語言中的DROP命令;DROP命令可以將一個或者多個存儲過程或者存儲過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：dropprocedure{procedure}[,…n]指定過程名稱?？蓜h除多個過程。第60頁/共120頁程序清單如下：USEBOOKSGODropprocedure

存儲過程_讀者借閱信息GO例8-6使用DROP命令刪除存儲過程“存儲過程_讀者借閱信息”。第61頁/共120頁8.1.2觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程。一般的存儲過程通過存儲過程名稱被直接調(diào)用，而觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行。

當(dāng)數(shù)據(jù)表有INSERT、UPDATE、DELETE事件發(fā)生時，所設(shè)置的觸發(fā)器即會自動被執(zhí)行，以進(jìn)行維護(hù)數(shù)據(jù)完整性，或其他數(shù)據(jù)處理工作。就是當(dāng)表內(nèi)容被更改時，會自動執(zhí)行的存儲過程。第62頁/共120頁數(shù)據(jù)的完整性（１）實(shí)體完整性。（２）域完整性。（３）參照完整性。1、實(shí)體完整性要求數(shù)據(jù)表中的每一行在表中是惟一的實(shí)體。2、采用三種方式主鍵約束：IDENTITY屬性：1、用來保證在數(shù)據(jù)表中輸入有效的數(shù)據(jù)值。方法定義字段的數(shù)據(jù)類型、設(shè)置字段非空檢查約束默認(rèn)約束默認(rèn)規(guī)則默認(rèn)值。對輸入的數(shù)據(jù)值進(jìn)行檢查，滿足條件可以保存在數(shù)據(jù)表中，反之則拒絕接受。惟一性約束：規(guī)則是對存儲在數(shù)據(jù)表中的數(shù)據(jù)值的規(guī)定和限制。設(shè)置參照完整性是為了保證數(shù)據(jù)表之間的數(shù)據(jù)保持一致，通過在兩個數(shù)據(jù)表之間的主鍵和外鍵之間或惟一鍵和外鍵之間建立外鍵約束來實(shí)現(xiàn)。約束特點(diǎn):直接設(shè)置于表內(nèi),不需編程;只能進(jìn)行比較簡單的操作;第63頁/共120頁觸發(fā)器作用觸發(fā)器可以用于SQLServer2000約束、默認(rèn)值和規(guī)則的完整性檢查；和CHECK約束相比較，觸發(fā)器是特殊的存儲過程，由于是編寫的程序,可以強(qiáng)制實(shí)現(xiàn)更加復(fù)雜的數(shù)據(jù)完整性，而且可以引用其他表中的字段。注：簡單的工作應(yīng)盡量用約束來完成，這樣容易設(shè)置及維護(hù)，執(zhí)行效率也比較好。只有當(dāng)它無法滿足需要時考慮使用觸發(fā)器。第64頁/共120頁補(bǔ)：觸發(fā)器的“特異功能”檢查所作的更改是否允許（更多樣更復(fù)雜的檢查：同時檢查多個數(shù)據(jù)表、用IF…ELSE等來作更彈性的檢查）；自定義錯誤信息（不是原來的固定信息，而是返回自定義的錯誤信息）；進(jìn)行其它相關(guān)數(shù)據(jù)的更改動作第65頁/共120頁觸發(fā)器分類

觸發(fā)器分為兩種，即AFTER觸發(fā)器和INSTEADOF觸發(fā)器。AFTER觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動（INSERT、UPDATE和DELETE操作）完成以后才被觸發(fā)?？梢詫ψ儎拥臄?shù)據(jù)進(jìn)行檢查，如果發(fā)現(xiàn)錯誤，將拒絕接受或回滾變動的數(shù)據(jù)。AFTER觸發(fā)器只能在表上定義。在同一個數(shù)據(jù)表中可以創(chuàng)建多個AFTER觸發(fā)器。第66頁/共120頁INSTEADOF觸發(fā)器：這種類型的觸發(fā)器將在數(shù)據(jù)變動以前被觸發(fā)，并取代變動數(shù)據(jù)的操作（INSERT、UPDATE和DELETE操作），而去執(zhí)行觸發(fā)器定義的操作。在表或視圖上，每個INSERT、UPDATE和DELETE語句最多只可以定義一個INSTEADOF觸發(fā)器。第67頁/共120頁8.3觸發(fā)器

8.3.1創(chuàng)建觸發(fā)器只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建觸發(fā)器，創(chuàng)建觸發(fā)器時可以引用其他數(shù)據(jù)庫中的對象。只有表的所有者有權(quán)創(chuàng)建觸發(fā)器，且不能將該權(quán)限轉(zhuǎn)給其他用戶。創(chuàng)建方法：使用企業(yè)管理器或者Transact-SQL語句來創(chuàng)建觸發(fā)器。第68頁/共120頁1．使用企業(yè)管理器創(chuàng)建觸發(fā)器步驟如下：在企業(yè)管理器中展開BOOKS數(shù)據(jù)庫，右擊“圖書基本信息表”，從彈出的快捷菜單中依次選擇“所有任務(wù)”→“管理觸發(fā)器”命令。出現(xiàn)“觸發(fā)器屬性”對話框，如圖所示。默認(rèn)是新建一個觸發(fā)器填上內(nèi)容進(jìn)行語法檢查。第69頁/共120頁可在此查看或修改已建好的觸發(fā)器的內(nèi)容。按此按鈕可刪除當(dāng)前觸發(fā)器。第70頁/共120頁（2）在“觸發(fā)器屬性”對話框中，在“名稱”文本框中選擇“新建”，然后在“文本”框中輸入創(chuàng)建觸發(fā)器的文本。當(dāng)創(chuàng)建一個觸發(fā)器時必須指定以下幾項(xiàng)內(nèi)容：1）觸發(fā)器的名稱；2）在其上定義觸發(fā)器的表；3）觸發(fā)器將何時激發(fā)；4）執(zhí)行觸發(fā)操作的編程語句。這里在“文本”框中輸入以一文本：（功能：創(chuàng)建一個INSERT觸發(fā)器）CREATETRIGGER觸發(fā)器_圖書信息表_插入ON圖書基本信息表FORINSERTASDECLARE@提示char(30)SET@提示='你插入了一條新記錄!'PRINT@提示當(dāng)在“圖書基本信息表”中插入一條新記錄時，觸發(fā)該觸發(fā)器，并給出“你插入了一條新記錄！”的提示信息。第71頁/共120頁USEBOOKSGOInsertinto圖書基本信息表(圖書編碼)Values(‘100009')GO例8-7創(chuàng)建了“觸發(fā)器_圖書信息表_插入”后，查看向圖書基本信息表中插入數(shù)據(jù)時，此觸發(fā)器所完成的功能。第72頁/共120頁2．使用Transact-SQL語句創(chuàng)建觸發(fā)器創(chuàng)建觸發(fā)器的語法：CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{{{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}[WITHAPPEND][NOTFORREPLICATION]第73頁/共120頁AS[{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}]sql_statement[...n]}}第74頁/共120頁CREATETRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}

ASsql_statement[...n]用于指定觸發(fā)器的名稱。在所屬數(shù)據(jù)庫中必須是唯一的。用于指定在其上執(zhí)行觸發(fā)器的表或視圖。（只有INSTEADOF觸發(fā)器才能設(shè)置于視圖上。用于加密syscomments表中包含CREATETRIGGER語句文本的條目。

FOR/AFTER類觸發(fā)器：只有在數(shù)據(jù)表的操作都已正確完成后才會激活觸發(fā)器；INSTEADOF類觸發(fā)器：用觸發(fā)器替代原來要執(zhí)行的數(shù)據(jù)操作。用于指定在表或視圖上執(zhí)行哪些數(shù)據(jù)修改語句時將激活觸發(fā)器的關(guān)鍵字。必須至少指定一個選項(xiàng)。如果指定的選項(xiàng)多于一個，需用逗號分隔這些選項(xiàng)。對于INSTEADOF類限制三項(xiàng)最多只能有一個。而AFTER類無限制。用來定義觸發(fā)器的內(nèi)容。第75頁/共120頁

在創(chuàng)建觸發(fā)器時，可以使用兩個特殊的臨時表：inserted表和deleted表，這兩個表都存在于內(nèi)存中。通過二表可知哪些數(shù)據(jù)已被新建、修改或刪除。觸發(fā)器可檢查deleted表、inserted表及被修改的表。兩個特殊的表：第76頁/共120頁在inserted表中存儲著被INSERT和UPDATE語句影響的新的數(shù)據(jù)行。在執(zhí)行INSERT或UPDATE語句時，新的數(shù)據(jù)行被添加到基本表中，同時這些數(shù)據(jù)行的備份

被復(fù)制到inserted臨時表中。第77頁/共120頁在deleted表中存儲著被DELETE和UPDATE語句影響的舊數(shù)據(jù)行。在執(zhí)行DELETE或UPDATE語句時，指定的數(shù)據(jù)行從基本表中刪除，然后被轉(zhuǎn)移到了deleted表中。在基本表和deleted表

中一般不會存在相同的數(shù)據(jù)行。第78頁/共120頁一個UPDATE操作實(shí)際上是由一個DELETE操作和一個INSERT操作組成的。在執(zhí)行UPDATE操作時，舊的數(shù)據(jù)行從基本表中轉(zhuǎn)移到deleted表中，然后將新的數(shù)據(jù)行同時插入基本表和inserted表中。第79頁/共120頁總結(jié)Inserted數(shù)據(jù)表Deleted數(shù)據(jù)表在插入時存放要插入的記錄在修改時存放要更新的記錄存放更新前的舊記錄在刪除時存放被刪除的舊記錄若要檢索Deleted和Inserted表中的所有記錄：可使用如下的語句：SELECT*FORMDeletedSELECT*FORMInserted第80頁/共120頁補(bǔ)例：對于BOOKS數(shù)據(jù)庫，如果在圖書基本信息表中添加或更改數(shù)據(jù)，則向用戶顯示一條信息。第81頁/共120頁USEBOOKSGO--如果已經(jīng)存在“顯示信息_觸發(fā)器”，則先刪除此觸發(fā)器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='顯示信息_觸發(fā)器'ANDtype='TR')DROPTRIGGER顯示信息_觸發(fā)器GO--創(chuàng)建“顯示信息_觸發(fā)器”CREATETRIGGER顯示信息_觸發(fā)器ON圖書基本信息表FORINSERT,UPDATEASPRINT‘尊敬的用戶：您添加或修改了一條數(shù)據(jù)'GO運(yùn)行：INSERTINTO圖書基本信息表(圖書編碼,書名,作者,定價)VALUES('100068','SQLSERVER數(shù)據(jù)庫基礎(chǔ)','王鵬',28)第82頁/共120頁補(bǔ)例：創(chuàng)建一個AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：

在圖書基本信息表上創(chuàng)建一個插入類型的觸發(fā)器“觸發(fā)器_定價檢查1”，當(dāng)在定價字段中插入數(shù)據(jù)后，觸發(fā)該觸發(fā)器，檢查定價的數(shù)據(jù)值是否大于0。第83頁/共120頁USEBOOKSGO--如果已經(jīng)存在“觸發(fā)器_定價檢查1”，則先刪除此觸發(fā)器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='觸發(fā)器_定價檢查1'ANDtype='TR')DROPTRIGGER觸發(fā)器_定價檢查1GO--創(chuàng)建“觸發(fā)器_定價檢查1”CREATETRIGGER觸發(fā)器_定價檢查1ON圖書基本信息表FORINSERTASIF(SELECT定價FROMinserted)<=0BEGINPRINT'輸入的定價數(shù)據(jù)應(yīng)該大于0!'ROLLBACKENDGO數(shù)據(jù)回滾。在sql語句執(zhí)行失敗時，使用了此語句，可以將數(shù)據(jù)回滾到sql語句執(zhí)行前的狀態(tài)。第84頁/共120頁例8-8創(chuàng)建一個AFTER觸發(fā)器，要求實(shí)現(xiàn)以下功能：在圖書基本信息表上創(chuàng)建一個插入、更新類型的觸發(fā)器“觸發(fā)器_定價檢查2”，當(dāng)在定價字段中插入或修改數(shù)據(jù)后，觸發(fā)該觸發(fā)器，檢查定價的數(shù)據(jù)值是否大于0。第85頁/共120頁USEBOOKSGO--如果已經(jīng)存在“觸發(fā)器_定價檢查2”，則先刪除此觸發(fā)器IFEXISTS(SELECTnameFROMsysobjectsWHEREname='觸發(fā)器_定價檢查2'ANDtype='TR')DROPTRIGGER觸發(fā)器_定價檢查2GO--創(chuàng)建“觸發(fā)器_定價檢查2”CREATETRIGGER觸發(fā)器_定價檢查2ON圖書基本信息表FORINSERT,UPDATEASIFUPDATE(定價)BEGINPRINT'AFTER觸發(fā)器開始執(zhí)行……'DECLARE@定價realSELECT@定價=(SELECT定價FROMinserted)IF@定價<=0PRINT'輸入的定價數(shù)據(jù)應(yīng)該大于0!'ENDGO用于測試在指定的列上進(jìn)行的INSERT或UPDATE操作。做二操作時都返回TRUE值。第86頁/共120頁USEBOOKSGOPRINT'在圖書基本信息表中插入記錄時觸發(fā)器的執(zhí)行結(jié)果:'PRINT''INSERTINTO圖書基本信息表(圖書編碼,書名,作者,定價)VALUES('100068','SQLSERVER數(shù)據(jù)庫基礎(chǔ)','王鵬',28)INSERTINTO圖書基本信息表(圖書編碼,書名,作者,定價)VALUES('100078','SQLSERVER學(xué)習(xí)指南','李東方',-35)GOPRINT'在圖書基本信息表中修改記錄時觸發(fā)器的執(zhí)行結(jié)果:'PRINT''UPDATE圖書基本信息表SET定價=-29WHERE圖書編碼='100005'UPDATE圖書基本信息表SET定價=-65WHERE圖書編碼='100006'GO第87頁/共120頁

例8-9創(chuàng)建一個AFTER觸發(fā)器，要求如下：

在“讀者基本信息表”上創(chuàng)建一個修改類型的觸發(fā)器“觸發(fā)器_級聯(lián)修改圖書借閱信息表”，當(dāng)在“讀者基本信息表”中修改某個記錄的讀者編號后，觸發(fā)該觸發(fā)器，在“圖書借閱信息表”中查找此人是否借閱了圖書，如果在圖書借閱信息表中存在此人的借閱信息，則級聯(lián)修改此人的讀者編號，使之與讀者基本信息表中修改后的讀者編號一致。第88頁/共120頁USEBOOKSGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='觸發(fā)器_級聯(lián)修改圖書借閱信息表'ANDtype='TR')DROPTRIGGER觸發(fā)器_級聯(lián)修改圖書借閱信息表GOCREATETRIGGER觸發(fā)器_級聯(lián)修改圖書借閱信息表ON讀者基本信息表FORUPDATEASPRINT'“觸發(fā)器_級聯(lián)修改圖書借閱信息表”開始執(zhí)行…….'DECLARE@讀者編號_修改前char(10)DECLARE@讀者編號_修改后char(10)PRINT'把在讀者基本信息表中修改前的記錄的讀者編號賦值給局部變量@讀者編號_修改前。'SELECT@讀者編號_修改前=讀者編號FROMDELETEDPRINT'把在讀者基本信息表中修改后的記錄的讀者編號賦值給局部變量@讀者編號_修改后。'SELECT@讀者編號_修改后=讀者編號FROMINSERTEDPRINT'開始查找并修改圖書借閱信息表相關(guān)記錄…'IF(SELECTCOUNT(*)FROM圖書借閱信息表WHERE讀者編號=@讀者編號_修改前)>0BEGINupdate圖書借閱信息表set讀者編號=@讀者編號_修改后where讀者編號=@讀者編號_修改前

print'已經(jīng)將圖書借閱信息表中的讀者編號為：'+rtrim(@讀者編號_修改前)+'的所有記錄的讀者編號修改為：'+rtrim(@讀者編號_修改后)+'。'end第89頁/共120頁CREATETRIGGER觸發(fā)器_級聯(lián)修改圖書借閱信息表ON讀者基本信息表FORUPDATEASPRINT'“觸發(fā)器_級聯(lián)修改圖書借閱信息表”開始執(zhí)行…….'DECLARE@讀者編號_修改前char(10)DECLARE@讀者編號_修改后char(10)PRINT'把讀者基本信息表中修改前的記錄的讀者編號賦值給局部變量@讀者編號_修改前。'SELECT@讀者編號_修改前=讀者編號FROMDELETEDPRINT'把讀者基本信息表中修改后的記錄的讀者編號賦值給局部變量@讀者編號_修改后。'SELECT@讀者編號_修改后=讀者編號FROMINSERTEDPRINT'開始查找并修改圖書借閱信息表相關(guān)記錄…'IF(SELECTCOUNT(*)FROM圖書借閱信息表WHERE讀者編號=@讀者編號_修改前)>0BEGINupdate圖書借閱信息表set讀者編號=@讀者編號_修改后where讀者編號=@讀者編號_修改前

print'已經(jīng)將圖書借閱信息表中的讀者編號為：'+rtrim(@讀者編號_修改前)+'的所有記錄的讀者編號修改為：'+rtrim(@讀者編號_修改后)+'。'end第90頁/共120頁運(yùn)行：USEBOOKSGO--修改未借閱圖書的詩編號update讀者基本信息表set讀者編號='JSJ05'where讀者編號='JSJ04'go第91頁/共120頁update讀者基本信息表set讀者編號='JSJ05‘where讀者編號='JSJ04'DELETEDINSERTED圖書借閱信息表中有無讀者編號為‘JSJ0４‘的？修改圖書借閱信息表中‘JSJ0４‘為'JSJ05‘

例8-9流程分析圖第92頁/共120頁USEBOOKSGO--修改已借閱圖書的讀者編號update讀者基本信息表set讀者編號='JJX02'where讀者編號='JJX01'go運(yùn)行：第93頁/共120頁8.3.2管理觸發(fā)器1．查看觸發(fā)器企業(yè)管理器方法如下：（1）查看觸發(fā)器定義信息。

在企業(yè)管理器中，展開選定數(shù)據(jù)庫，并右擊其中的某個數(shù)據(jù)表，從彈出的快捷菜單中依次選擇“所有任務(wù)”→“管理觸發(fā)器”命令，會打開觸發(fā)器屬性對話框。第94頁/共120頁在“對象”下拉列表框中可以選擇要查看的數(shù)據(jù)庫對象名稱。左邊的頁框中會顯示依賴于該對象的其他對象，右邊的頁框中顯示的是該對象依賴的其他對象。（2）查看與觸發(fā)器有依賴關(guān)系的其他數(shù)據(jù)庫對象右擊圖書基本信息表，從彈出的快捷菜單中依次選擇“所有任務(wù)”→“顯示相關(guān)性”命令，會出現(xiàn)相關(guān)性對話框。第95頁/共120頁

使用系統(tǒng)存儲過程sp_help、sp_helptext和sp_depends可分別查看觸發(fā)器的不同信息。sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。格式：sp_help‘觸發(fā)器名稱’例：sp_help

觸發(fā)器_定價檢查2使用系統(tǒng)存儲過程查看觸發(fā)器第96頁/共120頁sp_helptext：用于查看觸發(fā)器的正文信息。

sp_helptext‘觸發(fā)器名稱’例：sp_helptext觸發(fā)器_定價檢查2第97頁/共120頁sp_depends：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。

sp_depends'觸發(fā)器名稱'

sp_depends‘表名’例：sp_depends

觸發(fā)器_定價檢查2sp_depends

讀者基本信息表第98頁/共120頁例8-10使用系統(tǒng)存儲過程查看“觸發(fā)器_級聯(lián)修改圖書借閱信息表”的一般信息。程序清單如下：USEBOOKSGOsp_help'觸發(fā)器_級聯(lián)修改圖書借閱信息表'GO第99頁/共120頁例8-11使用系統(tǒng)存儲過程查看“觸發(fā)器_級聯(lián)修改圖書借閱信息表”涉及的數(shù)據(jù)表的相關(guān)信息。程序清單如下：USEBOOKSGOsp_depends'觸發(fā)器_級聯(lián)修改圖書借閱信息表'GO第100頁/共120頁2．修改觸發(fā)器修改觸發(fā)器包括修改觸發(fā)器的正文和修改觸發(fā)器的名稱。展開選定數(shù)據(jù)庫，右擊某一個數(shù)據(jù)表（從彈出的快捷菜單中依次選擇“所有任務(wù)”→“管理觸發(fā)器”命令。出現(xiàn)如圖所示對話框。在名稱選項(xiàng)框中選擇要修改的觸發(fā)器的名稱，然后在文本框中修改觸發(fā)器的SQL語句，單擊“檢查語法”按鈕，可以檢查語法是否正確。第101頁/共120頁2．修改觸發(fā)器

使用Transact-SQL語句中的altertrigger命令來修改觸發(fā)器正文。altertrigger命令的語法形式如下：ALTERTRIGGERtrigger_nameON(table|view)[WITHENCRYPTION]{第102頁/共120頁{(FOR|AFTER|INSTEADOF){[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]ASsql_statement[...n]}|第103頁/共120頁{(FOR|AFTER|INSTEADOF){[INSERT][,]UPDATE}}[NOTFORREPLICATION]AS{IFUPDATE(column)[{AND|OR}UPDATE(column)][...n]第104頁/共120頁|IF(COLUMNS_UPDATED(){bitwise_operator}updated_bitmask){comparison_operator}column_bitmask[...n]}sql_statement[...n]}}第105頁/共120頁簡要格式：ALTERTRIGGERtrigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}ASsql_statement[...n]第106頁/共120頁2．利用系統(tǒng)存儲過程修改觸發(fā)器名稱

可以使用系統(tǒng)存儲過程sp_rename來修改觸發(fā)器的名稱。sp_rename命令的語法形式如下：sp_renameoldname,newname第107頁/共120頁8.3.3刪除觸發(fā)器只有觸發(fā)器所有者才有權(quán)刪除觸發(fā)器。刪除已創(chuàng)建的觸發(fā)器有三種方法：直接刪除觸發(fā)器所在的數(shù)據(jù)表使用企業(yè)管理器刪除觸發(fā)器使用Transact-SQL語句刪除觸發(fā)器第108頁/共120頁1．直接刪除觸發(fā)器所在的數(shù)據(jù)表如果觸發(fā)器所在的數(shù)據(jù)表已經(jīng)不再使用了，可以直接刪除此數(shù)據(jù)表。刪除數(shù)據(jù)表時，SQLServer2000將會自動刪除與該表相關(guān)的所有觸發(fā)器。第109頁/共120頁2．使用企業(yè)管理器刪除觸發(fā)器在企業(yè)管理器中，右擊要刪除的觸發(fā)器所在的數(shù)據(jù)表，從彈出的快捷菜單中依次選擇“所有任務(wù)”→“管理觸發(fā)器”命令，會出現(xiàn)觸發(fā)器屬性對話框。在“名稱”選項(xiàng)框中選擇要刪除的觸發(fā)器，然后單擊“刪除”按鈕，即可刪除該觸發(fā)器。第110頁/共120