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

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

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

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

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

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

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

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

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

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

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

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

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

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

14、“stu”數(shù)據(jù)庫中，創(chuàng)建一個(gè)存儲(chǔ)過程“stu_softxs”,通過該存儲(chǔ)過程可以查詢軟件技術(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)建存儲(chǔ)過程。2.執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程的基本語句格式：EXEC

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

16、數(shù)之間用逗號(hào)分隔。所有數(shù)據(jù)類型均可以作為存儲(chǔ)過程的參數(shù)，一般情況下，參數(shù)的數(shù)據(jù)類型要與它相關(guān)的字段的數(shù)據(jù)類型一致。1. 帶有輸入?yún)?shù)的存儲(chǔ)過程【案例10-7】在“stu”數(shù)據(jù)庫中，創(chuàng)建存儲(chǔ)過程“stu_zymc”,該存儲(chǔ)過程帶有一個(gè)用于接收用戶輸入專業(yè)名稱的輸入?yún)?shù)。該存儲(chǔ)過程要求被執(zhí)行時(shí)，它將根據(jù)用戶輸入的專業(yè)名稱列出該專業(yè)的所有班級(jí)信息。代碼如下：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í)行該存儲(chǔ)過程“stu_zymc”，代碼如下：Use stuGoEXEC stu_zymc 軟件技術(shù)go執(zhí)行結(jié)果如圖10-7所示?！咎崾尽繄?zhí)行帶參數(shù)的存儲(chǔ)過程，有兩種方式。·按位置轉(zhuǎn)遞。在調(diào)用存儲(chǔ)過程時(shí)，直接給出參數(shù)值。如果多于一個(gè)參數(shù)，給出的參數(shù)值要與定義的參數(shù)順序一致。例如：EXEC stu_zymc 軟件技術(shù)。·使用參數(shù)名稱轉(zhuǎn)遞。在調(diào)用存儲(chǔ)過程時(shí)，按“參數(shù)名=參數(shù)值”的形式給出參數(shù)值。采用此方式，參數(shù)如果多于一個(gè)時(shí)，給出的參數(shù)順序可以與定義的參數(shù)的順序不一致。例如：EXEC stu_zymc zymc=軟件技術(shù)

18、。圖10-7 執(zhí)行存儲(chǔ)過程顯示出相應(yīng)專業(yè)的班級(jí)信息2. 帶有參數(shù)默認(rèn)值的存儲(chǔ)過程【案例10-8】如果要求上例中創(chuàng)建的存儲(chǔ)過程stu_zymc在被執(zhí)行時(shí)不給出參數(shù)值將默認(rèn)顯示軟件技術(shù)專業(yè)的班級(jí)信息，則創(chuàng)建該存儲(chǔ)過程的代碼為：/*變量參數(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í)行該存儲(chǔ)過程，代碼：EXEC stu_zymc3. 帶有輸

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

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

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

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

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

24、對(duì)某一表進(jìn)行諸如INSERT、UPDATE或DELETE操作時(shí)，如果在這些操作上定義了觸發(fā)器，SQL Server就會(huì)自動(dòng)執(zhí)行觸發(fā)器（執(zhí)行觸發(fā)器中所定義的SQL語句），從而確保對(duì)數(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ā)器可以偵測(cè)數(shù)據(jù)庫的操作，從而不允許數(shù)據(jù)庫中未經(jīng)許可的指定更新和變化。 級(jí)聯(lián)運(yùn)行：觸發(fā)器可以偵

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

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

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

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

29、發(fā)器表和Inserted表中。Inserted表就是用來存儲(chǔ)向原表插入的紀(jì)錄副本。Deleted表用于存儲(chǔ)DELETE和UPDATE語句所影響的行的副本。在執(zhí)行DELETE或UPDATE語句時(shí)，從觸發(fā)器表中刪除原記錄，并把刪除的記錄的副本臨時(shí)存放到daleted表中。這樣做的目的是：一旦觸發(fā)器遇到了強(qiáng)迫它中止的語句被執(zhí)行時(shí)，刪除的那些行可以從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)建一個(gè)觸發(fā)器tr_scxs，當(dāng)學(xué)生表student有記錄被刪除時(shí)，顯示“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ā)器后，刪除一條記錄，驗(yàn)證該觸發(fā)器，代碼如下： Use stuGoDelete student where s_num='0806010101'執(zhí)行以上刪除記錄語句后，結(jié)果如圖10-10所示。圖10-10 刪除記錄激活觸發(fā)器返回信息【提示】·雖然DML觸發(fā)器可以引用臨時(shí)表，但不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)建DML觸發(fā)器。·對(duì)含有用DELETE或UPDATE操作定義的外鍵的表，不能定義INSTEAD OF DELETE和INSTEAD OF UPDATE觸發(fā)器。·TRUNCATE

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

33、'0602010102'執(zhí)行以上刪除記錄語句后，結(jié)果如圖10-11所示。再重新打開student表時(shí)發(fā)現(xiàn)學(xué)號(hào)為0602010102的學(xué)生記錄還在，沒有被刪除。圖10-11 要?jiǎng)h除記錄時(shí)激活觸發(fā)器并返回信息2使用SSMS創(chuàng)建觸發(fā)器【案例10-14】為“stu”數(shù)據(jù)庫的stucourse表創(chuàng)建一個(gè)更新觸發(fā)器tr_upsc，當(dāng)更新了該表中的X條記錄信息時(shí)，顯示“你已經(jīng)成功更新的記錄信息有X條！”。操作步驟如下：（1）啟動(dòng)SQL Server Management Studio，在【對(duì)象資源管理器】中依次展開【數(shù)據(jù)庫】【stu】【表】節(jié)點(diǎn)。（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é)號(hào)為0501010103的學(xué)生成績(jī)，每門成績(jī)都減少5分，驗(yàn)證該觸發(fā)器的功能，如圖10-13所示。圖10-12 選擇新建觸發(fā)器圖10-13 更新信息激發(fā)觸發(fā)器返回信息10.2.4 查看觸發(fā)器信息1.使用系統(tǒng)存儲(chǔ)過程查看觸發(fā)器使用系統(tǒng)存儲(chǔ)過程sp_helptrigger和sp_helptext可以查看觸發(fā)器，但作用有所差異：使用sp_helptrigger返回的是觸發(fā)器的類型，而使用sp_helptext則顯示觸發(fā)器的定義文本。使用系統(tǒng)存儲(chǔ)過程sp_helptrigger查看觸發(fā)器的基本語句格式如下：sp_helptrigger 表名 ,觸發(fā)器類型使用系統(tǒng)存儲(chǔ)過程sp_helptext查

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

37、賴關(guān)系】命令，在【對(duì)象依賴關(guān)系】對(duì)話框中，可以查看該觸發(fā)器和相關(guān)表的依賴關(guān)系。在快捷菜單中選擇【修改】命令，可以查看觸發(fā)器的定義文本信息?！咎崾尽吭诘冢?）步驟的右鍵快捷菜單中選擇“修改”命令，也可以對(duì)觸發(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ā)器請(qǐng)參閱“使用SSMS查看觸發(fā)器信息”部分。10.2.6 禁用、啟用和刪除觸發(fā)器1.使用T-SQL語句禁用、啟用和刪除觸發(fā)器（1）禁用觸發(fā)器可以使用DLSABLE TRIGGER命令禁用DML觸發(fā)器，基本語句格式如下：DISABLE TRIGGER 觸發(fā)器名，n|ALLON 對(duì)象名 |數(shù)據(jù)庫 | 服務(wù)器【案例10-17】禁用student表上的觸發(fā)器tr_delxs。 代碼如下： Use stuGoDISABLE TRIGGER tr_delxs ON student【提示】

39、3;禁用觸發(fā)器不會(huì)刪除該觸發(fā)器，該觸發(fā)器仍然作為對(duì)象存在于當(dāng)前數(shù)據(jù)庫中。·禁用觸發(fā)器后，執(zhí)行相應(yīng)的T-SQL語句時(shí)，不會(huì)引發(fā)觸發(fā)器。（2）啟用觸發(fā)器可以使用ENABLE TRIGGER命令啟用DML觸發(fā)器，基本語句格式如下：ENABLE TRIGGER 觸發(fā)器名，n|ALLON 對(duì)象名 |數(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)建時(shí)，才能使用一個(gè)DROP TRIGGER 語句刪除多個(gè)DDL觸發(fā)器。2使用SSMS禁用、啟用和刪除觸發(fā)器使用SQL Server Management Studio禁用、啟用和刪除觸發(fā)器的步驟如下：（1）啟動(dòng)SQL Server Management Studio，在【對(duì)象資源管理器】中依次展開【數(shù)據(jù)庫】節(jié)點(diǎn)、觸發(fā)器所在的【數(shù)據(jù)庫】節(jié)點(diǎn)和【表】節(jié)點(diǎn)、【觸發(fā)器】節(jié)點(diǎn)。（2）右鍵單擊相應(yīng)的觸發(fā)器，彈出右鍵快捷菜單。（3）選擇【禁用】命令，即可禁用選定的觸發(fā)器；選擇【啟用】命令，即可恢復(fù)觸發(fā)器為活動(dòng)應(yīng)用狀態(tài)；選

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

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

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