存儲過程和觸發(fā)器

1、第10章 存儲過程和觸發(fā)器【學(xué)習(xí)目標(biāo)】本章將要學(xué)習(xí)存儲過程和觸發(fā)器的基本概念、作用和基本操作。本章學(xué)習(xí)要點：u 存儲過程的概念、作用、分類；u 存儲過程的創(chuàng)建、查看、修改和執(zhí)行；u 觸發(fā)器的主要作用、類型；u inserted表和deleted表的作用和使用；u 觸發(fā)器的創(chuàng)建方法、查看、修改和執(zhí)行?！緦W(xué)習(xí)導(dǎo)航】存儲過程（Store Procedure）和觸發(fā)器（Trigger）是SQL Server 數(shù)據(jù)庫系統(tǒng)重要的數(shù)據(jù)庫對象，在以SQL Server 2005 為后臺數(shù)據(jù)庫創(chuàng)建的應(yīng)用程序中具有重要的應(yīng)用價值。本章主要內(nèi)容見圖10-1所示的學(xué)習(xí)導(dǎo)航。圖10-1 本章內(nèi)容學(xué)習(xí)導(dǎo)航10.1 存儲過

2、程概述Transact-SQL語言是應(yīng)用程序與SQL Server數(shù)據(jù)庫之間的主要編程接口，大量的時間將花費在Transact-SQL語句和應(yīng)用程序代碼上。在很多情況下，許多代碼被重復(fù)使用多次，每次都輸入相同的代碼不但繁瑣，更由于在客戶機(jī)上的大量命令語句逐條向SQL Server 發(fā)送，將降低系統(tǒng)運行效率。因此，SQL Server提供了一種方法，它將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，應(yīng)用程序只需調(diào)用它的名稱，就可實現(xiàn)某個特定任務(wù)，這種方法就是存儲過程。下面將詳細(xì)介紹存儲過程的概念、特點、創(chuàng)建、執(zhí)行等內(nèi)容。10.1.1 存儲過程的概念SQL SERVER 中T-S

3、QL語言為了實現(xiàn)特定任務(wù)而將一些需要多次調(diào)用的固定的操作編寫成子程序并集中以一個存儲單元的形式存儲在服務(wù)器上，由SQL Server數(shù)據(jù)庫服務(wù)器通過子程序名來調(diào)用它們，這些子程序就是存儲過程。存儲過程是一種數(shù)據(jù)庫對象，存儲在數(shù)據(jù)庫內(nèi)，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行，具有很強(qiáng)的編程功能。存儲過程可以使用EXECUTE語句來運行。在SQL Server中使用存儲過程而不使用存儲在客戶端計算機(jī)本地的T-SQL程序有以下幾個方面的好處。（1）加快系統(tǒng)運行速度。存儲過程只在創(chuàng)建時進(jìn)行編譯，以后每次執(zhí)行存儲過程都不需再重新編譯，而一般SQL語句每執(zhí)行依次就編譯一次，所以使用

4、存儲過程可提高數(shù)據(jù)庫執(zhí)行速度。（2）封裝復(fù)雜操作。當(dāng)對數(shù)據(jù)庫進(jìn)行復(fù)雜操作時（如對多個表進(jìn)行Update Insert，Query，Delete時），可用存儲過程將此復(fù)雜操作封裝起來與數(shù)據(jù)庫提供的事務(wù)處理結(jié)合一起使用。（3）實現(xiàn)代碼重用?？梢詫崿F(xiàn)模塊化程序設(shè)計，存儲過程一旦創(chuàng)建，以后即可在程序中調(diào)用任意多次，這可以改進(jìn)應(yīng)用程序的可維護(hù)性，并允許應(yīng)用程序統(tǒng)一訪問數(shù)據(jù)庫。（4）增強(qiáng)安全性。可設(shè)定特定用戶具有對指定存儲過程的執(zhí)行權(quán)限而不具備直接對存儲過程中引用的對象具有權(quán)限；可以強(qiáng)制應(yīng)用程序的安全性；參數(shù)化存儲過程有助于保護(hù)應(yīng)用程序不受SQL注入式攻擊。（5）減少網(wǎng)絡(luò)流量。因為存儲過程存儲在服務(wù)器上，

5、并在服務(wù)器上運行。一個需要數(shù)百行T-SQL代碼的操作可以通過一條執(zhí)行過程代碼的語句來執(zhí)行；而不需要在網(wǎng)絡(luò)中發(fā)送數(shù)百行代碼，這樣就可以減少網(wǎng)絡(luò)流量。10.1.2 存儲過程的分類在SQL Server 2005中存儲過程可以分為兩類：系統(tǒng)存儲過程和用戶存儲過程。1系統(tǒng)存儲過程系統(tǒng)存儲過程是由SQL Server系統(tǒng)提供的存儲過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲過程主要用來從系統(tǒng)表中獲取信息,為系統(tǒng)管理員管理SQL Server 提供幫助,為用戶查看數(shù)據(jù)庫對象提供方便。例如，執(zhí)行sp_helptext系統(tǒng)存儲過程可以顯示規(guī)則、默認(rèn)值、未加密的存儲過程、用戶函數(shù)、觸發(fā)器或視圖的文本信息；執(zhí)行sp_

6、depends系統(tǒng)存儲過程可以顯示有關(guān)數(shù)據(jù)庫對象相關(guān)性的信息；執(zhí)行sp_rename系統(tǒng)存儲過程可以更改當(dāng)前數(shù)據(jù)庫中用戶創(chuàng)建對象的名稱。SQL Server中許多管理工作是通過執(zhí)行系統(tǒng)存儲過程來完成的，許多系統(tǒng)信息也可以通過執(zhí)行系統(tǒng)存儲過程而獲得。系統(tǒng)存儲過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是sp_。在調(diào)用時不必在存儲過程前加上數(shù)據(jù)庫名。有關(guān)系統(tǒng)存儲過程的詳細(xì)介紹請參考SQL Server聯(lián)機(jī)叢書。除了以sp_為前綴的系統(tǒng)存儲過程，我們還常見到以xp_為前綴的存儲過程，這種存儲過程為擴(kuò)展存儲過程。擴(kuò)展存儲過程主要用于擴(kuò)展SQL Server的功能。2用戶存儲過程用戶存儲過程是指用戶根據(jù)自

7、身需要，為完成某一特定功能，在用戶數(shù)據(jù)庫中創(chuàng)建的存儲過程。用戶創(chuàng)建存儲過程時，存儲過程名的前面加上“#”，是表示創(chuàng)建全局臨時存儲過程；在存儲過程名的前面加上“#”，是表示創(chuàng)建局部臨時存儲過程。局部臨時存儲過程只能在創(chuàng)建它的會話中可用，當(dāng)前會話結(jié)束時除去；全局臨時存儲過程可以在所有會話中使用，即所有用戶均可以訪問該過程。它們都保存在tempdb數(shù)據(jù)庫中。10.1.3存儲過程的創(chuàng)建與管理在SQL Server 2005 中通?？梢允褂脙煞N方法創(chuàng)建存儲過程：一種是使用圖形化管理工具SQL Server Management Studio 創(chuàng)建存儲過程；另一種是使用T-SQL 語句創(chuàng)建存儲過程。創(chuàng)建存

8、儲過程時，需要注意下列事項： 只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。 創(chuàng)建存儲過程時，應(yīng)指定所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)、執(zhí)行數(shù)據(jù)庫操作的編程語句和返回至調(diào)用過程或批處理以表明成功或失敗的狀態(tài)值。 在用戶存儲過程的定義中不能使用下列對象創(chuàng)建語句：CREATE VIEW、CREATE DEFAULT、CREATE RULE、CREATE PROCEDURE、CREATE TRIGGER。即在存儲過程的創(chuàng)建中不能嵌套創(chuàng)建以上這些對象。存儲過程創(chuàng)建后，可以使用EXECUTE語句來執(zhí)行（可以簡寫為EXEC），如果它是一個批處理中的第一條語句，則關(guān)鍵字EXECUTE（或EXEC）也可省略。1

9、0.1.4 使用SSMS創(chuàng)建和執(zhí)行存儲過程【案例10-1】執(zhí)行系統(tǒng)存儲過程sp_help查看教務(wù)管理數(shù)據(jù)庫stu中class表的信息。程序清單：use stugoexec sp_help class 運行以上程序，結(jié)果如圖10-2所示。圖10-2 執(zhí)行系統(tǒng)存儲過程sp_help查看class表信息【案例10-2】在數(shù)據(jù)庫stu中，創(chuàng)建一個名稱為“stu_softjs”的存儲過程,通過該存儲過程可查詢出軟件工程系所有教授的信息。操作步驟如下：（1）啟動SQL Server Management Studio，在【對象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】【stu】【可編程性】節(jié)點。（2）右鍵單擊

10、【存儲過程】節(jié)點，選擇【新建存儲過程】命令，打開【創(chuàng)建存儲過程模版】文檔窗口，如圖10-3所示。（3）用戶在模版文檔窗口中根據(jù)相應(yīng)提示輸入存儲過程名稱和T-SQL語句。創(chuàng)建存儲過程“stu_softjs”，如圖10-4所示。（4）單擊【執(zhí)行】按鈕，完成存儲過程的創(chuàng)建。【提示】·在模板文檔窗口中可以把不必要的參數(shù)去掉。·在第（2）步驟右鍵單擊“存儲過程”節(jié)點，選擇“刷新”，即可看到剛剛創(chuàng)建好的存儲過程。圖10-3 【創(chuàng)建存儲過程模版】文檔窗口圖10-4 創(chuàng)建存儲過程“stu_softjs”【案例10-3】使用SSMS執(zhí)行上面例子中創(chuàng)建的存儲過程“stu_softjs”。 啟動

11、SQL Server Management Studio,在【對象資源管理器】中依次展開【數(shù)據(jù)庫】【student】【可編程性】【存儲過程】節(jié)點。 右鍵單擊【stu_softjs】存儲過程，選擇【執(zhí)行存儲過程】命令，如圖10-5所示。 打開【執(zhí)行過程】對話框，再單擊【確定】按鈕即可。圖10-5 選擇【執(zhí)行存儲過程】命令10.1.5 使用SSMS查看、修改和刪除存儲過程【案例10-4】使用SSMS，查看上例中創(chuàng)建的存儲過程“stu_softjs”的屬性。（1）在如圖10-5所示的右鍵菜單中，選擇【屬性】菜單，打開【存儲過程屬性】對話框。（2）選擇【常規(guī)】選項卡：可以查看到該存儲過程屬于哪個數(shù)據(jù)庫

12、、創(chuàng)建如期和屬于男個數(shù)據(jù)庫用戶等信息。（3）選擇【權(quán)限】選項卡：可以為該存儲過程添加用戶并授予其權(quán)限。（4）選擇【擴(kuò)展屬性】選項卡：可以了解排序規(guī)則等擴(kuò)展屬性?！咎崾尽吭谌鐖D10-4所示的右鍵菜單中，選擇“刪除”菜單命令可以刪除指定的存儲過程；選擇“修改”命令進(jìn)入存儲過程文本修改狀態(tài)，可對存儲過程進(jìn)行修改；選擇“重命名” 可以實現(xiàn)存儲過程的名稱的更改。10.1.6 使用T-SQL語句創(chuàng)建和執(zhí)行存儲過程1.創(chuàng)建存儲過程使用T-SQL語句CREATE PROC可以創(chuàng)建存儲過程，其基本語句格式如下所示。CREATE PROCEDURE 存儲過程名參數(shù)1 數(shù)據(jù)類型=默認(rèn)值 OUTPUT， nWHTI

13、ENCRYPTIONRECOMPILEASSQL語句參數(shù)含義：u 存儲過程名：要符合標(biāo)識符規(guī)則，少于128個字符。u 參數(shù)：過程中的參數(shù)。在CREATE PROCEDURE語句中可以聲明一個或多個參數(shù)。u OUTPUT：表明該參數(shù)是一個返回參數(shù)。u AS：用于指定該存儲過程要執(zhí)行的操作。u SQL語句：是存儲過程中要包含的任意數(shù)目和類型的T-SQL語句。u ENCRYPTION：用于加密存儲過程文本。本加密的存儲過程，其圖標(biāo)上有“加鎖”標(biāo)志，其定義內(nèi)容不可修改，也不可通過系統(tǒng)存儲過程sp_helptext查看。u RECOMPILE：設(shè)置該選項后，存儲過程將在運行時重新編譯。【案例10-5】在

14、“stu”數(shù)據(jù)庫中，創(chuàng)建一個存儲過程“stu_softxs”,通過該存儲過程可以查詢軟件技術(shù)專業(yè)的學(xué)生信息。(1) 在“查詢編輯器”窗口中輸入如下代碼：USE stu GOCREATE PROC stu_softxsAS Select *From studentWhere s_classid in(SELECT c_id FROM classWHERE sp_id in (SELECT sp_id FROM specialityWHERE sp_name=軟件技術(shù))GO(2) 單擊“分析”按鈕，進(jìn)行語法檢查；再單擊“執(zhí)行”按鈕，創(chuàng)建存儲過程。2.執(zhí)行存儲過程執(zhí)行存儲過程的基本語句格式：EXEC

15、 procedure_name Value_List參數(shù)含義: procedure_name：要執(zhí)行的存儲過程的名稱。 Value_List：輸入?yún)?shù)值?！景咐?0-6】執(zhí)行以上存儲過程“stu_softxs”。代碼如下:USE STUGOEXEC stu_softxsGO執(zhí)行以上代碼，結(jié)果如圖10-6所示。圖10-6 執(zhí)行存儲過程“stu_softxs”結(jié)果10.1.7 創(chuàng)建和執(zhí)行帶參數(shù)的存儲過程帶參數(shù)的存儲過程可以擴(kuò)展存儲過程的功能。使用輸入?yún)?shù)，可以將外部信息輸入到存儲過程；使用輸出參數(shù)，可以將存儲過程內(nèi)的信息轉(zhuǎn)到外部。創(chuàng)建帶參數(shù)的存儲過程時，參數(shù)可以是一個，也可以是多個，多個參數(shù)時，參

16、數(shù)之間用逗號分隔。所有數(shù)據(jù)類型均可以作為存儲過程的參數(shù)，一般情況下，參數(shù)的數(shù)據(jù)類型要與它相關(guān)的字段的數(shù)據(jù)類型一致。1. 帶有輸入?yún)?shù)的存儲過程【案例10-7】在“stu”數(shù)據(jù)庫中，創(chuàng)建存儲過程“stu_zymc”,該存儲過程帶有一個用于接收用戶輸入專業(yè)名稱的輸入?yún)?shù)。該存儲過程要求被執(zhí)行時，它將根據(jù)用戶輸入的專業(yè)名稱列出該專業(yè)的所有班級信息。代碼如下：CREATE PROC stu_zymc zymc varchar(20)/*變量參數(shù)的數(shù)據(jù)類型與長度都要和表中相關(guān)字段的定義一致*/ASSELECT * FROM classWHERE sp_id in(SELECT sp_id FROM sp

17、ecialityWHERE sp_name=zymc)GO執(zhí)行該存儲過程“stu_zymc”，代碼如下：Use stuGoEXEC stu_zymc 軟件技術(shù)go執(zhí)行結(jié)果如圖10-7所示。【提示】執(zhí)行帶參數(shù)的存儲過程，有兩種方式。·按位置轉(zhuǎn)遞。在調(diào)用存儲過程時，直接給出參數(shù)值。如果多于一個參數(shù)，給出的參數(shù)值要與定義的參數(shù)順序一致。例如：EXEC stu_zymc 軟件技術(shù)。·使用參數(shù)名稱轉(zhuǎn)遞。在調(diào)用存儲過程時，按“參數(shù)名=參數(shù)值”的形式給出參數(shù)值。采用此方式，參數(shù)如果多于一個時，給出的參數(shù)順序可以與定義的參數(shù)的順序不一致。例如：EXEC stu_zymc zymc=軟件技術(shù)

18、。圖10-7 執(zhí)行存儲過程顯示出相應(yīng)專業(yè)的班級信息2. 帶有參數(shù)默認(rèn)值的存儲過程【案例10-8】如果要求上例中創(chuàng)建的存儲過程stu_zymc在被執(zhí)行時不給出參數(shù)值將默認(rèn)顯示軟件技術(shù)專業(yè)的班級信息，則創(chuàng)建該存儲過程的代碼為：/*變量參數(shù)的默認(rèn)值是“軟件技術(shù)”專業(yè)*/ CREATE PROC stu_zymc zymc varchar(20) =軟件技術(shù) AS SELECT * FROM class WHERE sp_id in(SELECT sp_id FROM specialityWHERE sp_name=zymc)GO 不帶參數(shù)值執(zhí)行該存儲過程，代碼：EXEC stu_zymc3. 帶有輸

19、出參數(shù)的存儲過程輸出參數(shù)用于在存儲過程中返回值，使用OUTPUT聲明輸出參數(shù)?！景咐?0-9】在stu數(shù)據(jù)庫中，創(chuàng)建一個帶有輸出參數(shù)的存儲過程stu_xspjf,其中輸出參數(shù)用于返回學(xué)號為0501010102的學(xué)生的平均成績。創(chuàng)建該存儲過程代碼：USE stuGO/*輸出參數(shù)的數(shù)據(jù)類型要與它接收的值的類型一致*/create proc stu_xspjf xspjf tinyint outputasselect xspjf=avg(sc_grade)from stucoursewhere s_num='0501010102'go執(zhí)行該存儲過程并輸出顯示的代碼如下,執(zhí)行結(jié)果如圖1

20、0-8所示。use stugodeclare avg tinyintexec stu_xspjf avg outputprint '學(xué)號為0501010102的同學(xué)的平均分為：'+ltrim(str(avg)+'分'【提示】執(zhí)行帶有輸出參數(shù)的存儲過程時，需要聲明變量來接收存儲過程中由輸出參數(shù)返回來的返回值。一般情況下，聲明的變量的數(shù)據(jù)類型要與存儲過程的輸出參數(shù)的數(shù)據(jù)類型一致。在使用該變量時，還必須為它加上OUTPUT 聲明。圖10-8 執(zhí)行帶有輸出參數(shù)的存儲過程并輸出顯示信息10.1.8 使用T-SQL語句查看、修改和刪除存儲過程1 使用SSMS查看、修改和刪除

21、存儲過程請參閱【案例10-4】、【提示】和圖10-5。2.使用系統(tǒng)存儲過程查看存儲過程信息（1）使用sp_help 查看存儲過程的一般信息,包含存儲過程的名稱、擁有者、類型和創(chuàng)建時間,其語法格式為:Exec Sp_help 存儲過程名（2）使用sp_helptext查看存儲過程的定義信息,其語法格式為:Exec Sp_helptext 存儲過程名【案例10-10】分別使用系統(tǒng)存儲過程sp_help和sp_helptext查看stu數(shù)據(jù)庫中的存儲過程“stu_xspjf”的定義、相關(guān)性及一般信息。代碼如下，結(jié)果如圖10-9所示。USE stuGOEXEC sp_help stu_xspjfEXE

22、C sp_helptext stu_xspjfGO 圖10-9 使用系統(tǒng)存儲過程查看存儲過程信息3.使用語句修改存儲過程使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行CREATE PROCEDURE語句創(chuàng)建的過程，ALTER PROCEDURE基本語句格式如下。ALTER PROCEDURE 存儲過程名參數(shù)1 數(shù)據(jù)類型=默認(rèn)值OUTPUT,.參數(shù) n 數(shù)據(jù)類型=默認(rèn)值OUTPUTASSQL語句.各參數(shù)含義與CREATE PROCDURE語句相同，只是把創(chuàng)建時的CREATE 改為了 ALTER。因為修改和創(chuàng)建時的過程方法一樣，在這里不再另外舉例說明。4.使用語句刪除存儲過程當(dāng)存儲過程

23、沒有存在的意義時，可以使用DROP PROCEDURE 語句將其刪除。用于刪除存儲過程的基本語句格式：DROP PROCEDURE 存儲過程名,n【案例10-11】刪除“stu”數(shù)據(jù)庫中的存儲過程“stu_xspjf”。代碼如下：USE stu GO DROP PROCEDURE stu_xspjfGO10.2 觸發(fā)器概述10.2.1 觸發(fā)器的概念1.觸發(fā)器的作用觸發(fā)器是一種特殊類型的存儲過程，它在指定的表中的數(shù)據(jù)發(fā)生變化時自動生效，實現(xiàn)表間的數(shù)據(jù)完整性和復(fù)雜的業(yè)務(wù)規(guī)則。與前面介紹過的存儲過程不同，存儲過程可以通過存儲過程名字被直接調(diào)用，而觸發(fā)器不能，觸發(fā)器主要通過事件進(jìn)行觸發(fā)而自動執(zhí)行的。當(dāng)

24、對某一表進(jìn)行諸如INSERT、UPDATE或DELETE操作時，如果在這些操作上定義了觸發(fā)器，SQL Server就會自動執(zhí)行觸發(fā)器（執(zhí)行觸發(fā)器中所定義的SQL語句），從而確保對數(shù)據(jù)的處理必須符合由這些SQL語句所定義的規(guī)則。觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性，除此之外，觸發(fā)器還有其他許多不同的功能。 強(qiáng)化約束：觸發(fā)器能夠?qū)崿F(xiàn)比CHECK語句更為復(fù)雜的約束。CHECK約束不允許引用其他表中的列來完成檢查工作，而觸發(fā)器可以引用其他表中的列。 跟蹤變化：觸發(fā)器可以偵測數(shù)據(jù)庫的操作，從而不允許數(shù)據(jù)庫中未經(jīng)許可的指定更新和變化。 級聯(lián)運行：觸發(fā)器可以偵

25、測數(shù)據(jù)庫內(nèi)的操作，并自動地級聯(lián)影響整個數(shù)據(jù)庫的相關(guān)內(nèi)容。例如，某個表上的觸發(fā)器中包含有對另外一個表的數(shù)據(jù)操作（如插入、更新、刪除），而該操作又導(dǎo)致該表上觸發(fā)器被觸發(fā)。2.觸發(fā)器類型在SQL Server2005中，觸發(fā)器分為DML觸發(fā)器和DDL觸發(fā)器兩大類。當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時將調(diào)用DML觸發(fā)器，當(dāng)服務(wù)器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL）事件時將調(diào)用DDL觸發(fā)器。DML觸發(fā)器是當(dāng)數(shù)據(jù)庫服務(wù)器中發(fā)生數(shù)據(jù)庫操作語言（DML）事件時要執(zhí)行的操作。DML事件包括對表或視圖發(fā)出的UPDATE、INSERT或DELETE 語句。DML觸發(fā)器用于在數(shù)據(jù)庫修改時強(qiáng)制執(zhí)行業(yè)務(wù)規(guī)則，以及擴(kuò)

26、展SQL Server2005約束、默認(rèn)值和規(guī)則的完整性檢查邏輯。根據(jù)DML觸發(fā)器被激活的時機(jī)不同又可以分為AFTER觸發(fā)器和INSTEAD OF 觸發(fā)器。 AFTER觸發(fā)器又稱為后觸發(fā)器。在執(zhí)行了INSERT、UPDATE 或DELETE語句操作之后執(zhí)行AFTER觸發(fā)器。指定AFTER觸發(fā)器與指定FOR相同，它是Microsoft SQL Server早期版本中唯一可用的選項，但AFTER觸發(fā)器只能在表上指定。 INSTEAD OF觸發(fā)器又稱為替代觸發(fā)器。該類觸發(fā)器代替觸發(fā)器操作執(zhí)行，即觸發(fā)器在數(shù)據(jù)發(fā)生變動之前被觸發(fā)，取代變動數(shù)據(jù)的操作（INSERT、UPDATE或DELETE操作），執(zhí)行觸

27、發(fā)器定義的操作。該類觸發(fā)器既可在表上定義，也可在視圖上定義。對于每個觸發(fā)器操作（INSERT、UPDATE和DELETE）只能定義一個INSTEAD OF觸發(fā)器。DDL觸發(fā)器是SQL Server2005的新增功能。DDL觸發(fā)器是一種特殊的觸發(fā)器，它不會為響應(yīng)針對表或視圖的UPDATE、INSERT或DELETE語句而激發(fā)。相反，它在響應(yīng)數(shù)據(jù)定義語言（DDL）語句時觸發(fā)，這些語句主要是以CREATE、ALTER和DROP開頭的語句。它們可以用于數(shù)據(jù)庫中執(zhí)行管理任務(wù)，例如，審核以及規(guī)范數(shù)據(jù)庫操作。因為DDL觸發(fā)器和DML觸發(fā)器可以使用相似的SQL語法進(jìn)行創(chuàng)建、修改和刪除，它們還具有其他相似的行為

28、。所以這里只介紹DML觸發(fā)器的創(chuàng)建與使用。10.2.2 inserted表和deleted表系統(tǒng)為每個觸發(fā)器創(chuàng)建兩個特殊臨時表：inserted表和deleted表。這兩個表都是邏輯表，由系統(tǒng)管理存儲在內(nèi)存中，它們在結(jié)構(gòu)上與該觸發(fā)器作用的表相同。這兩個表是只讀，用戶不能對其修改和寫入內(nèi)容，但可以在觸發(fā)器執(zhí)行過程中引用這兩個表中的數(shù)據(jù)。當(dāng)觸發(fā)器工作完成后，與該觸發(fā)器相關(guān)的這兩個表也被刪除。 Inserted表用于存儲INSERT和UPDATE語句所影響的行的副本。如果表存在INSERT 觸發(fā)器，向表中插入數(shù)據(jù)時，系統(tǒng)將自動創(chuàng)建一個與觸發(fā)器具有相同表結(jié)構(gòu)的Inserted臨時表，新的記錄被添加到觸

29、發(fā)器表和Inserted表中。Inserted表就是用來存儲向原表插入的紀(jì)錄副本。Deleted表用于存儲DELETE和UPDATE語句所影響的行的副本。在執(zhí)行DELETE或UPDATE語句時，從觸發(fā)器表中刪除原記錄，并把刪除的記錄的副本臨時存放到daleted表中。這樣做的目的是：一旦觸發(fā)器遇到了強(qiáng)迫它中止的語句被執(zhí)行時，刪除的那些行可以從deleted表中得以恢復(fù)?！咎崾尽啃薷谋碇械臄?shù)據(jù)，相當(dāng)于刪除一條舊的記錄，添加一條新的記錄。其中，被刪除的記錄放在Deleted表中，添加的新的記錄放在Inserted表中。10.2.3 創(chuàng)建與管理觸發(fā)器1使用T-SQL創(chuàng)建與管理觸發(fā)器T-SQL語言使用

30、CREATE TRIGGER命令創(chuàng)建觸發(fā)器。創(chuàng)建DML觸發(fā)器的基本語句格式：CREATE TRIGGER 觸發(fā)器名ON 表| 視圖FOR|AFTER|INSTEAD OFINSERT|UPDATE|DELETEAS DML語句【案例10-12】在stu數(shù)據(jù)庫的學(xué)生表student中創(chuàng)建一個觸發(fā)器tr_scxs，當(dāng)學(xué)生表student有記錄被刪除時，顯示“XXX同學(xué)信息已被你成功刪除！”。創(chuàng)建該觸發(fā)器代碼如下：CREATE TRIGGER tr_scxsON studentFOR DELETEASBEGINDECLARE xsxx CHAR(10)SELECT xsxx=s_name FROM

31、DELETEDPRINT xsxx +同學(xué)信息已被你成功刪除！ END 創(chuàng)建觸發(fā)器后，刪除一條記錄，驗證該觸發(fā)器，代碼如下： Use stuGoDelete student where s_num='0806010101'執(zhí)行以上刪除記錄語句后，結(jié)果如圖10-10所示。圖10-10 刪除記錄激活觸發(fā)器返回信息【提示】·雖然DML觸發(fā)器可以引用臨時表，但不能對臨時表或系統(tǒng)表創(chuàng)建DML觸發(fā)器。·對含有用DELETE或UPDATE操作定義的外鍵的表，不能定義INSTEAD OF DELETE和INSTEAD OF UPDATE觸發(fā)器。·TRUNCATE

32、TABLB 語句不會觸發(fā)DELETE觸發(fā)器，因為TRUNCATE TABLB語句沒有執(zhí)行記錄?！景咐?0-13】在stu數(shù)據(jù)庫中創(chuàng)建一個刪除觸發(fā)器tr_delxs,當(dāng)表student中的記錄要被刪除時，激活該觸發(fā)器，顯示“不能刪除本表中的數(shù)據(jù)！請與管理員聯(lián)系！”的提示信息。創(chuàng)建該觸發(fā)器代碼如下：USE stuGOCREATE TRIGGER tr_delxs ON studentINSTEAD OF DELETE ASPRINT 不能刪除本表中的數(shù)據(jù)！請與管理員聯(lián)系！GO創(chuàng)建該觸發(fā)器后，刪除一條記錄，驗證觸發(fā)器，代碼如下：Use stuGoDelete student where s_num=

33、'0602010102'執(zhí)行以上刪除記錄語句后，結(jié)果如圖10-11所示。再重新打開student表時發(fā)現(xiàn)學(xué)號為0602010102的學(xué)生記錄還在，沒有被刪除。圖10-11 要刪除記錄時激活觸發(fā)器并返回信息2使用SSMS創(chuàng)建觸發(fā)器【案例10-14】為“stu”數(shù)據(jù)庫的stucourse表創(chuàng)建一個更新觸發(fā)器tr_upsc，當(dāng)更新了該表中的X條記錄信息時，顯示“你已經(jīng)成功更新的記錄信息有X條！”。操作步驟如下：（1）啟動SQL Server Management Studio，在【對象資源管理器】中依次展開【數(shù)據(jù)庫】【stu】【表】節(jié)點。（2）展開stucourse表，右鍵單擊【觸發(fā)

34、器】，選擇【新建觸發(fā)器】，如圖10-12所示。（3）打開【新建觸發(fā)器】模板文檔窗口，根據(jù)相應(yīng)提示輸入創(chuàng)建觸發(fā)器的文本，創(chuàng)建代碼如下。（4）執(zhí)行創(chuàng)建觸發(fā)器的語句，語句成功執(zhí)行后，則創(chuàng)建好觸發(fā)器。創(chuàng)建該觸發(fā)器代碼如下： USE stuGOCREATE TRIGGER tr_upsc ON stucourse AFTER UPDATEAS BEGINdeclare num tinyintselect num=count(*) from insertedprint /*你已經(jīng)成功更新的記錄信息有'+ltrim(str(num)+'條！*/ENDGO使用UPDATE語句更新表stucou

35、rse中學(xué)號為0501010103的學(xué)生成績，每門成績都減少5分，驗證該觸發(fā)器的功能，如圖10-13所示。圖10-12 選擇新建觸發(fā)器圖10-13 更新信息激發(fā)觸發(fā)器返回信息10.2.4 查看觸發(fā)器信息1.使用系統(tǒng)存儲過程查看觸發(fā)器使用系統(tǒng)存儲過程sp_helptrigger和sp_helptext可以查看觸發(fā)器，但作用有所差異：使用sp_helptrigger返回的是觸發(fā)器的類型，而使用sp_helptext則顯示觸發(fā)器的定義文本。使用系統(tǒng)存儲過程sp_helptrigger查看觸發(fā)器的基本語句格式如下：sp_helptrigger 表名 ,觸發(fā)器類型使用系統(tǒng)存儲過程sp_helptext查

36、看觸發(fā)器的基本語句格式如下：sp_helptext 觸發(fā)器名【案例10-15】查看student表中所有觸發(fā)器的相關(guān)信息，同時也顯示觸發(fā)器tr_delxs的定義文本。代碼如下：Use stugosp_helptrigger studentGOsp_helptext tr_delxsGO2 使用SSMS查看觸發(fā)器信息使用SSMS查看觸發(fā)器的相關(guān)信息的步驟如下。(1) 啟動SQL Server Management Studio，在【對象資源管理器】窗口中，依次展開【數(shù)據(jù)庫】【stu】表（如student表）【觸發(fā)器】節(jié)點。(2) 在【觸發(fā)器】節(jié)點中，右擊需要查看的觸發(fā)器，在快捷菜單中選擇【查看依

37、賴關(guān)系】命令，在【對象依賴關(guān)系】對話框中，可以查看該觸發(fā)器和相關(guān)表的依賴關(guān)系。在快捷菜單中選擇【修改】命令，可以查看觸發(fā)器的定義文本信息。【提示】在第（2）步驟的右鍵快捷菜單中選擇“修改”命令，也可以對觸發(fā)器重新修改定義；選擇“刪除”命令可以刪除該觸發(fā)器。10.2.5 修改觸發(fā)器1. 使用T-SQL語言修改觸發(fā)器T-SQL語言使用ALTER TRIGGER命令修改DML觸發(fā)器，基本語句格式如下。ALTER TRIGGER 觸發(fā)器名ON 表| 視圖FOR | AFTER |INSTEAD OF INSERT |UPDATE |DELETEASSQL語句修改觸發(fā)器與創(chuàng)建觸發(fā)器的語法基本相同，只是將

38、創(chuàng)建觸發(fā)器的CREATE關(guān)鍵字換成了ALTER關(guān)鍵字而已,在這里不再舉例說明它的用法。2. 使用SSMS修改觸發(fā)器請參閱“使用SSMS查看觸發(fā)器信息”部分。10.2.6 禁用、啟用和刪除觸發(fā)器1.使用T-SQL語句禁用、啟用和刪除觸發(fā)器（1）禁用觸發(fā)器可以使用DLSABLE TRIGGER命令禁用DML觸發(fā)器，基本語句格式如下：DISABLE TRIGGER 觸發(fā)器名，n|ALLON 對象名 |數(shù)據(jù)庫 | 服務(wù)器【案例10-17】禁用student表上的觸發(fā)器tr_delxs。 代碼如下： Use stuGoDISABLE TRIGGER tr_delxs ON student【提示】

39、3;禁用觸發(fā)器不會刪除該觸發(fā)器，該觸發(fā)器仍然作為對象存在于當(dāng)前數(shù)據(jù)庫中。·禁用觸發(fā)器后，執(zhí)行相應(yīng)的T-SQL語句時，不會引發(fā)觸發(fā)器。（2）啟用觸發(fā)器可以使用ENABLE TRIGGER命令啟用DML觸發(fā)器，基本語句格式如下：ENABLE TRIGGER 觸發(fā)器名，n|ALLON 對象名 |數(shù)據(jù)庫 | 服務(wù)器ENABLE TRIGGER 的基本使用同DISABLE TRIGGER，但作用相反。（3）刪除觸發(fā)器可以使用DROP TRIGGER命令刪除DML觸發(fā)器，基本語句格式如下：DROP TRIGGER 觸發(fā)器，n【案例10-18】刪除student表中的tr_delxs觸發(fā)器。DRO

40、P TRIGGER tr_delxsGO【提示】:僅當(dāng)所有觸發(fā)器均使用相同的ON 子句創(chuàng)建時，才能使用一個DROP TRIGGER 語句刪除多個DDL觸發(fā)器。2使用SSMS禁用、啟用和刪除觸發(fā)器使用SQL Server Management Studio禁用、啟用和刪除觸發(fā)器的步驟如下：（1）啟動SQL Server Management Studio，在【對象資源管理器】中依次展開【數(shù)據(jù)庫】節(jié)點、觸發(fā)器所在的【數(shù)據(jù)庫】節(jié)點和【表】節(jié)點、【觸發(fā)器】節(jié)點。（2）右鍵單擊相應(yīng)的觸發(fā)器，彈出右鍵快捷菜單。（3）選擇【禁用】命令，即可禁用選定的觸發(fā)器；選擇【啟用】命令，即可恢復(fù)觸發(fā)器為活動應(yīng)用狀態(tài)；選

41、擇【刪除】命令,即刻刪除所選的觸發(fā)器。10.3案例應(yīng)用【提示】在各題案例中，在創(chuàng)建存儲過程或觸發(fā)器之前，可以先使用如下代碼檢測是否已存在該對象，如果存在則先刪除再創(chuàng)建，這里各題案例都是假設(shè)之前不存在的情況。刪除語法如下：IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME = 對象名AND TYPE = 類型)DROP PROCEDURETRIGGER 對象名對象名：創(chuàng)建的存儲過程名或觸發(fā)器名；類型：存儲過程是P，觸發(fā)器是TR。一、存儲過程綜合案例【案例10-19】在stu數(shù)據(jù)庫中，創(chuàng)建一個加密的存儲過程Sc_xs，通過該存儲過程查詢選修課成績都及格

42、的學(xué)生的信息。Use stuGoCreate proc sc_xsWith EncryptionAsSelect *From student where s_num not in /*成績及格的學(xué)生*/(Select s_num From stucourse where sc_grade <60) /*子查詢中是成績不資格的學(xué)號*/Go執(zhí)行以下代碼，驗證存儲過程。 Exec sc_xs /*執(zhí)行該存儲過程*/Sp_helptext sc_xs /*驗證存儲過程定義文本是否加密*/【案例10-20】在stu數(shù)據(jù)庫中，創(chuàng)建一存儲過程InfoByName，通過該存儲過程，當(dāng)輸入學(xué)生姓名（如“張

43、然”）時，如果該生存在即可查詢出該生的基本信息,經(jīng)檢測判斷不存在該學(xué)生姓名，顯示“對不起，你輸入的學(xué)生姓名不存在，請重新核對再輸入！”的提示性信息。Use stugoCreate procedure InfoByName Name char（10）ASIF (SELECT COUNT(*) FROM student WHERE s_name = Name)>0 BEGINSELECT * FROM studentWhere s_name = NameENDELSEprint '對不起，你輸入的學(xué)生姓名不存在，請重新核對再輸入！'【案例10-21】在stu數(shù)據(jù)庫中，創(chuàng)建一個存儲過程stu_xsc