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

1、7.17.2存存 儲(chǔ)儲(chǔ) 過過 程程觸觸 發(fā)發(fā) 器器使用存儲(chǔ)過程的優(yōu)點(diǎn)如下：（1）存儲(chǔ)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。（2）存儲(chǔ)過程執(zhí)行一次后，就駐留在高速緩沖存儲(chǔ)器，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，提高了系統(tǒng)性能。（3）使用存儲(chǔ)過程可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制對(duì)數(shù)據(jù)庫信息訪問的權(quán)限，確保數(shù)據(jù)庫的安全。（4）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程可以在SQL Server啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，大大方便了用戶的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。在SQL Server 2008中有下列幾種類型的存儲(chǔ)過程。（1）系統(tǒng)

2、存儲(chǔ)過程。系統(tǒng)存儲(chǔ)過程是由SQL Server提供的存儲(chǔ)過程，可以作為命令執(zhí)行。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是“sp_”，例如，常用的顯示系統(tǒng)對(duì)象信息的sp_help系統(tǒng)存儲(chǔ)過程，為檢索系統(tǒng)表的信息提供了方便快捷的方法。（2）擴(kuò)展存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程是指在SQL Server 2008環(huán)境之外，使用編程語言（如C+語言）創(chuàng)建的外部例程形成的動(dòng)態(tài)鏈接庫（DLL）。使用時(shí)，先將DLL加載到SQL Server 2008系統(tǒng)中，并且按照使用系統(tǒng)存儲(chǔ)過程的方法執(zhí)行。擴(kuò)展存儲(chǔ)過程在 SQL Server 實(shí)例地址空間中運(yùn)行。但因?yàn)閿U(kuò)展存儲(chǔ)過程不易撰寫，而且可能會(huì)引發(fā)安全性問題，所

3、以微軟可能會(huì)在未來的SQL Server中刪除這一功能，本書將不詳細(xì)介紹擴(kuò)展存儲(chǔ)過程。（3）用戶存儲(chǔ)過程。在SQL Server 2008中，用戶存儲(chǔ)過程可以使用T-SQL語言編寫，也可以使用CLR方式編寫。在本書中，T-SQL存儲(chǔ)過程就稱為存儲(chǔ)過程。1使用命令方式創(chuàng)建存儲(chǔ)過程使用命令方式創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程的語句是CREATE PROCEDURE或CREATE PROC，兩者同義。語法格式：CREATE PROC | PROCEDURE 架構(gòu)名. 過程名 ; 組號(hào) /*定義過程名*/ 參數(shù) 類型架構(gòu)名. 數(shù)據(jù)類型 /*定義參數(shù)的類型*/ VARYING = default OUT | O

4、UTPUT READONLY/*定義參數(shù)的屬性*/ , . WITH , . /*定義存儲(chǔ)過程的處理方式*/ FOR REPLICATION AS ; . /*執(zhí)行的操作*/ | EXTERNAL NAME 程序集名.類名.方法名 ; 1）命令主體）命令主體CREATE PROCEDURE命令主體結(jié)構(gòu)說明如下：（1）過程名：用于指定存儲(chǔ)過程名，必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫及所在架構(gòu)必須唯一。（2）;組號(hào)：為可選的整數(shù)，用于對(duì)同名的存儲(chǔ)過程進(jìn)行分組，以便使用一條DROP PROCEDURE語句就可刪除一組存儲(chǔ)過程（3）參數(shù)：為存儲(chǔ)過程的形參，符號(hào)作為第一個(gè)字符來指定參數(shù)名稱。（4）數(shù)據(jù)類型：

5、用于指定形參的數(shù)據(jù)類型，形參可為SQL Server 2008支持的任何類型，但cursor類型只能用于OUTPUT參數(shù)，如果指定參數(shù)的數(shù)據(jù)類型為cursor，則必須同時(shí)指定VARYING和OUTPUT關(guān)鍵字，OUT與OUTPUT關(guān)鍵字意義相同。（5）VARYING：指定作為輸出參數(shù)支持的結(jié)果集。（6）default：指定存儲(chǔ)過程輸入?yún)?shù)的默認(rèn)值，默認(rèn)值必須是常量或NULL。（7）OUTPUT：指示參數(shù)為輸出參數(shù)，輸出參數(shù)可以從存儲(chǔ)過程返回信息。（8）READONLY：指定不能在存儲(chǔ)過程的主體中更新或修改參數(shù)。（9）：用于定義存儲(chǔ)過程的處理方式。（10）FOR REPLICATION：用于說明

6、不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程，如果指定了FOR REPLICATION，則無法聲明參數(shù)。（11）SQL語句：代表過程體包含的T-SQL語句，存儲(chǔ)過程體中可以包含一條或多條T-SQL語句，除了DCL、DML與DDL命令外，還能包含過程式語句，如變量的定義與賦值、流程控制語句等。（12）EXTERNAL NAME：指定.NET Framework程序集的方法，以便CLR存儲(chǔ)過程引用。2）過程選項(xiàng)）過程選項(xiàng)的具體格式為： := ENCRYPTION RECOMPILE EXECUTE AS 子句 （1）ENCRYPTION：指定SQL Server對(duì)CREATE PROCEDURE語句

7、的原始文本進(jìn)行加密。對(duì)于CLR存儲(chǔ)過程，這個(gè)選項(xiàng)不可用。（2）RECOMPILE：指定數(shù)據(jù)庫引擎不緩存該過程的計(jì)劃，該過程在運(yùn)行時(shí)編譯。（3）EXECUTE AS子句：指定在其中執(zhí)行存儲(chǔ)過程的安全上下文。3）注意事項(xiàng)）注意事項(xiàng)（1）用戶定義的存儲(chǔ)過程只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建（臨時(shí)存儲(chǔ)過程除外，它總是在系統(tǒng)數(shù)據(jù)庫tempdb中創(chuàng)建）。存儲(chǔ)過程名稱存儲(chǔ)在sysobjects系統(tǒng)表中，而語句的文本存儲(chǔ)在syscomments中。（2）SQL Server啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過程。這些存儲(chǔ)過程必須由系統(tǒng)管理員在master數(shù)據(jù)庫中創(chuàng)建，并在sysadmin固定服務(wù)器角色下作為后臺(tái)過程執(zhí)行。這

8、些過程不能有任何輸入?yún)?shù)。 （3）CREATE PROCEDURE的權(quán)限默認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner 和 db_ddladmin 固定數(shù)據(jù)庫角色成員。sysadmin 固定服務(wù)器角色成員和 db_owner 固定數(shù)據(jù)庫角色成員可以將CREATE PROCEDURE權(quán)限轉(zhuǎn)讓給其他用戶。（4）SQL語句的限制。 如下語句必須使用對(duì)象的架構(gòu)名對(duì)數(shù)據(jù)庫對(duì)象進(jìn)行限定：CREATE TABLE、ALTER TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS及DBCC語句。 如下語

9、句不能出現(xiàn)在CREATE PROCEDURE定義中：SET PARSEONLY、SET SHOWPLAN_TEXT、SET SHOWPLAN_XML、SET SHOWPLAN_ALL、CREATE SCHEMA、CREATE FUNCTION、ALTER FUNCTION、CREATE PROCEDURE、ALTER PROCEDURE、CREATE TRIGGER、ALTER TRIGGER、CREATE VIEW、ALTER VIEW、USE 數(shù)據(jù)庫名等。2存儲(chǔ)過程的執(zhí)行存儲(chǔ)過程的執(zhí)行通過EXECUTE或EXEC命令可以執(zhí)行一個(gè)已定義的存儲(chǔ)過程，EXEC是EXECUTE的簡(jiǎn)寫。語法格式：

10、EXEC | EXECUTE 返回狀態(tài) = 模塊名 ;組號(hào) | 模塊名變量 參數(shù)名 = 值 | 變量 OUTPUT | DEFAULT , . WITH RECOMPILE ; 1）語句說明）語句說明（1）返回狀態(tài)：為可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)。EXECUTE語句使用該變量前，必須對(duì)其聲明。（2）模塊名：要調(diào)用的存儲(chǔ)過程或用戶定義標(biāo)量函數(shù)的完全限定或者不完全限定名稱。“組號(hào)”用于調(diào)用已定義的一組存儲(chǔ)過程中的某一個(gè)。（3）模塊名變量：局部定義的變量名，保存存儲(chǔ)過程或用戶定義函數(shù)的名稱。（4）參數(shù)名：為CREATE PROCEDURE或CREATE FUNCTION語句中定義的參數(shù)名，

11、“值”為實(shí)參。如果省略“參數(shù)名”，則后面的實(shí)參順序要與定義時(shí)參數(shù)的順序一致。（5）變量：為局部變量，用于保存OUTPUT參數(shù)返回的值。（6）DEFAULT：DEFAULT關(guān)鍵字表示不提供實(shí)參，而是使用對(duì)應(yīng)的默認(rèn)值。（7）WITH RECOMPILE：執(zhí)行模塊后，強(qiáng)制編譯、使用和放棄新計(jì)劃。2）注意事項(xiàng)）注意事項(xiàng)存儲(chǔ)過程的執(zhí)行要注意以下幾點(diǎn)：（1）如果存儲(chǔ)過程名的前綴為“sp_”，SQL Server會(huì)首先在master數(shù)據(jù)庫中尋找符合該名稱的系統(tǒng)存儲(chǔ)過程。如果沒能找到合法的過程名，SQL Server才會(huì)尋找架構(gòu)名稱為dbo的存儲(chǔ)過程。（2）在執(zhí)行存儲(chǔ)過程時(shí)，若語句是批處理中的第一個(gè)語句，則不

12、一定要指定EXECUTE關(guān)鍵字。3）舉例）舉例（1）設(shè)計(jì)簡(jiǎn)單的存儲(chǔ)過程?！纠?.1】 返回081101號(hào)學(xué)生的成績(jī)情況。該存儲(chǔ)過程不使用任何參數(shù)。USE PXSCJGOCREATE PROCEDURE student_info AS SELECT * FROM CJB WHERE 學(xué)號(hào)= 081101GO存儲(chǔ)過程定義后，執(zhí)行存儲(chǔ)過程student_info：EXECUTE student_info如果該存儲(chǔ)過程是批處理中的第一條語句，則可使用：student_info執(zhí)行結(jié)果如圖7.1所示。（2）使用帶參數(shù)的存儲(chǔ)過程。【例7.2】 從PXSCJ數(shù)據(jù)庫的三個(gè)表中查詢某人指定課程的成績(jī)和學(xué)分。該存

13、儲(chǔ)過程接收與傳遞參數(shù)精確匹配的值。USE PXSCJGOCREATE PROCEDURE student_info1 name char (8), cname char(16) AS SELECT a.學(xué)號(hào), 姓名, 課程名, 成績(jī), t.學(xué)分 FROM XSB a INNER JOIN CJB b ON a.學(xué)號(hào) = b.學(xué)號(hào) INNER JOIN KCB t ON b.課程號(hào)= t.課程號(hào) WHERE a.姓名=name and t.課程名=cnameGO執(zhí)行存儲(chǔ)過程student_info1：EXECUTE student_info1 王林, 計(jì)算機(jī)基礎(chǔ)執(zhí)行結(jié)果如圖7.2所示。以下命令的

14、執(zhí)行結(jié)果與上面的相同：EXECUTE student_info1 name=王林, cname=計(jì)算機(jī)基礎(chǔ)或者：DECLARE proc char(20)SET proc= student_info1EXECUTE proc name=王林, cname=計(jì)算機(jī)基礎(chǔ)（3）使用帶OUPUT參數(shù)的存儲(chǔ)過程?！纠?.3】 創(chuàng)建一個(gè)存儲(chǔ)過程do_insert，作用是向XSB表中插入一行數(shù)據(jù)。創(chuàng)建另外一個(gè)存儲(chǔ)過程do_action，在其中調(diào)用第一個(gè)存儲(chǔ)過程，并根據(jù)條件處理該行數(shù)據(jù)，處理后輸出相應(yīng)的信息。第一個(gè)存儲(chǔ)過程：CREATE PROCEDURE dbo.do_insert AS INSERT IN

15、TO XSB VALUES(091201, 陶偉, 1, 1990-03-05, 軟件工程,50, NULL);第二個(gè)存儲(chǔ)過程：CREATE PROCEDURE do_action X bit, STR CHAR(8) OUTPUT AS BEGIN EXEC do_insert IF X=0 BEGIN UPDATE XSB SET 姓名=劉英, 性別=0 WHERE 學(xué)號(hào)=091201 SET STR=修改成功 END ELSE IF X=1 BEGIN DELETE FROM XSB WHERE 學(xué)號(hào)=091201 SET STR=刪除成功 END END接下來執(zhí)行存儲(chǔ)過程do_acti

16、on來查看結(jié)果：DECLARE str char(8)EXEC dbo.do_action 0, str OUTPUTSELECT str;執(zhí)行結(jié)果如圖7.3所示。（4）使用帶有通配符參數(shù)的存儲(chǔ)過程。【例7.4】 從三個(gè)表的連接中返回指定學(xué)生的學(xué)號(hào)、姓名、所選課程名稱及該課程的成績(jī)。該存儲(chǔ)過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。CREATE PROCEDURE st_info name varchar(30) = 李% AS SELECT a.學(xué)號(hào),a.姓名,c.課程名,b.成績(jī) FROM XSB a INNER JOIN CJB b ON a.學(xué)號(hào) =b.學(xué)號(hào) IN

17、NER JOIN KCB c ON c.課程號(hào)= b.課程號(hào) WHERE 姓名 LIKE nameGO 執(zhí)行存儲(chǔ)過程：EXECUTE st_info /*參數(shù)使用默認(rèn)值*/或者:EXECUTE st_info 王% /*傳遞給name 的實(shí)參為王%*/（5）使用OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過程。OUTPUT游標(biāo)參數(shù)用于返回存儲(chǔ)過程的局部游標(biāo)?！纠?.5】 在 PXSCJ數(shù)據(jù)庫的XSB表上聲明并打開一個(gè)游標(biāo)。CREATE PROCEDURE st_cursor st_cursor cursor VARYING OUTPUT AS SET st_cursor = CURSOR FORWARD_ONL

18、Y STATIC FOR SELECT * FROM XSB OPEN st_cursor在如下的批處理中，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述存儲(chǔ)過程，并將游標(biāo)賦值給局部游標(biāo)變量，然后通過該游標(biāo)變量讀取記錄。DECLARE MyCursor cursorEXEC st_cursor st_cursor = MyCursor OUTPUT /*執(zhí)行存儲(chǔ)過程*/FETCH NEXT FROM MyCursorWHILE (FETCH_STATUS = 0)BEGIN FETCH NEXT FROM MyCursorENDCLOSE MyCursorDEALLOCATE MyCursor（6）使用WIT

19、H ENCRYPTION選項(xiàng)。WITH ENCRYPTION子句用于對(duì)用戶隱藏存儲(chǔ)過程的文本?！纠?.6】 創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲(chǔ)過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息。CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM XSB通過系統(tǒng)存儲(chǔ)過程sp_helptext可顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本。執(zhí)行如下語句：EXEC sp_helptext encrypt_this結(jié)果集為提示信息“對(duì)象encry

20、pt_this的文本已加密”。使用ALTER PROCEDURE命令可修改已存在的存儲(chǔ)過程并保留以前賦予的許可。語法格式：ALTER PROC | PROCEDURE 架構(gòu)名. 過程名 ; 組號(hào) 參數(shù) 類型架構(gòu)名. 數(shù)據(jù)類型 VARYING = default OUTPUT , . WITH , . FOR REPLICATION AS ; . | EXTERNAL NAME 程序集名.類名.方法名 ; 【例7.7】 對(duì)例7.2中創(chuàng)建的存儲(chǔ)過程student_info1進(jìn)行修改，將第一個(gè)參數(shù)改成學(xué)生的學(xué)號(hào)。USE PXSCJGOALTER PROCEDURE student_info1 num

21、ber char(6),cname char(16) AS SELECT 學(xué)號(hào), 課程名, 成績(jī) FROM CJB, KCB WHERE CJB.學(xué)號(hào)=number AND KCB.課程名=cnameGO【例7.8】 創(chuàng)建名為select_students的存儲(chǔ)過程，在默認(rèn)情況下，該存儲(chǔ)過程可查詢所有學(xué)生信息，隨后授予權(quán)限。當(dāng)該存儲(chǔ)過程需更改為能檢索計(jì)算機(jī)專業(yè)的學(xué)生信息時(shí)，用ALTER PROCEDURE命令重新定義該存儲(chǔ)過程。創(chuàng)建select_students存儲(chǔ)過程：CREATE PROCEDURE select_students /*創(chuàng)建存儲(chǔ)過程*/ AS SELECT * FROM X

22、SB ORDER BY 學(xué)號(hào)GO修改存儲(chǔ)過程select_students：ALTER PROCEDURE select_students WITH ENCRYPTION AS SELECT * FROM XSB WHERE 專業(yè)= 計(jì)算機(jī) ORDER BY 學(xué)號(hào)GO當(dāng)不再使用一個(gè)存儲(chǔ)過程時(shí)，就要把它從數(shù)據(jù)庫中刪除。使用DROP PROCEDURE語句可永久地刪除存儲(chǔ)過程。在此之前，必須確認(rèn)該存儲(chǔ)過程沒有任何依賴關(guān)系。語法格式：DROP PROC | PROCEDURE 架構(gòu)名. 過程 , . 說明：過程是指要?jiǎng)h除的存儲(chǔ)過程或存儲(chǔ)過程組的名稱。USE PXSCJGOIF EXISTS(SELE

23、CT name FROM sysobjects WHERE name=student_info) DROP PROCEDURE student_info1創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程例如，如果要通過圖形向?qū)Х绞蕉x一個(gè)存儲(chǔ)過程來查詢PXSCJ數(shù)據(jù)庫中每個(gè)同學(xué)各門功課的成績(jī)，那么其主要步驟如下：?jiǎn)?dòng)“SQL Server Management Studio”，在“對(duì)象資源管理器”中展開“數(shù)據(jù)庫”“PXSCJ”，選擇其中的“可編程性”，右擊“存儲(chǔ)過程”，在彈出的快捷菜單中選擇“新建存儲(chǔ)過程”菜單項(xiàng)，打開“存儲(chǔ)過程腳本編輯”窗口，如圖7.4所示。2執(zhí)行存儲(chǔ)過程執(zhí)行存儲(chǔ)過程在PXSCJ數(shù)據(jù)庫的“存儲(chǔ)過程”

24、目錄下選擇要執(zhí)行的存儲(chǔ)過程，如student_info1，右擊鼠標(biāo)，選擇“執(zhí)行存儲(chǔ)過程”菜單項(xiàng)。在彈出的“執(zhí)行過程”窗口中會(huì)列出存儲(chǔ)過程的參數(shù)形式，如果“輸出參數(shù)”欄為“否”，則表示該參數(shù)為輸入?yún)?shù)，用戶需要設(shè)置輸入?yún)?shù)的值，在“值”一欄中輸入即可，如圖7.5所示。3修改存儲(chǔ)過程修改存儲(chǔ)過程在“存儲(chǔ)過程”目錄下選擇要修改的存儲(chǔ)過程，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“修改”菜單項(xiàng)，打開“存儲(chǔ)過程腳本編輯”窗口，在該窗口中修改相關(guān)的T-SQL語句。修改完成后，執(zhí)行修改后的腳本，若執(zhí)行成功，則修改了存儲(chǔ)過程。4刪除存儲(chǔ)過程刪除存儲(chǔ)過程選擇要?jiǎng)h除的存儲(chǔ)過程，右擊鼠標(biāo)，在彈出的快捷菜單中選擇“刪除”菜單

25、項(xiàng)，根據(jù)提示刪除該存儲(chǔ)過程。7.2.1 觸發(fā)器的類型觸發(fā)器的類型1DML觸發(fā)器觸發(fā)器當(dāng)數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操縱語言（DML）事件時(shí)將調(diào)用DML觸發(fā)器。一般情況下，DML事件包括對(duì)表或視圖的INSERT語句、UPDATE語句和DELETE語句，因而DML觸發(fā)器也可分為三種類型：INSERT、UPDATE和DELETE。利用DML觸發(fā)器可以方便地保持?jǐn)?shù)據(jù)庫中數(shù)據(jù)的完整性。例如，對(duì)于PXSCJ數(shù)據(jù)庫有XSB表、CJB表和KCB表，當(dāng)插入某一學(xué)號(hào)的學(xué)生某一課程的成績(jī)時(shí)，該學(xué)號(hào)應(yīng)是XSB表中已存在的，課程號(hào)應(yīng)是KCB表中已存在的，此時(shí)，可通過定義INSERT觸發(fā)器實(shí)現(xiàn)上述功能。通過DML觸發(fā)器可以實(shí)現(xiàn)多個(gè)表

26、間數(shù)據(jù)的一致性。2DDL觸發(fā)器觸發(fā)器DDL觸發(fā)器也是由相應(yīng)的事件觸發(fā)的，但DDL觸發(fā)器觸發(fā)的事件是數(shù)據(jù)定義語句（DDL）。這些語句主要是以CREATE、ALTER、DROP等關(guān)鍵字開頭的語句。DDL觸發(fā)器的主要作用是執(zhí)行管理操作，如審核系統(tǒng)、控制數(shù)據(jù)庫的操作等。通常情況下，DDL觸發(fā)器主要用于以下一些操作需求：防止對(duì)數(shù)據(jù)庫架構(gòu)進(jìn)行某些修改；希望數(shù)據(jù)庫中發(fā)生某些變化以利于相應(yīng)數(shù)據(jù)庫架構(gòu)中的更改；記錄數(shù)據(jù)庫架構(gòu)中的更改或事件。DDL觸發(fā)器只在響應(yīng)由T-SQL語法所指定的DDL事件時(shí)才會(huì)觸發(fā)。1創(chuàng)建創(chuàng)建DML觸發(fā)器觸發(fā)器語法格式：CREATE TRIGGER 架構(gòu)名. 觸發(fā)器名 ON 表 | 視圖

27、/*指定操作對(duì)象*/ WITH ENCRYPTION /*說明是否采用加密方式*/ FOR |AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION /*說明該觸發(fā)器不用于復(fù)制*/AS SQL語句 ; . | EXTERNAL NAME 程序集名.類名.方法名1）語句說明）語句說明（1）觸發(fā)器名：用于指定觸發(fā)器名，觸發(fā)器名必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫中必須唯一?！凹軜?gòu)名”是DML觸發(fā)器所屬架構(gòu)的名稱。對(duì)于DDL觸發(fā)器，無法指定架構(gòu)名。（2）表 | 視圖：指在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)

28、器表或觸發(fā)器視圖。（3）AFTER：用于說明觸發(fā)器在指定操作都成功執(zhí)行后觸發(fā)，如AFTER INSERT表示向表中插入數(shù)據(jù)時(shí)激活觸發(fā)器。不能在視圖上定義AFTER觸發(fā)器。（4）INSTEAD OF：指定用DML觸發(fā)器中的操作代替觸發(fā)語句的操作。（5） INSERT , UPDATE , DELETE ：指定激活觸發(fā)器的語句的類型，必須至少指定一個(gè)選項(xiàng)。在觸發(fā)器定義中允許使用上述選項(xiàng)的任意順序組合。（6）WITH APPEND：指定應(yīng)該再添加一個(gè)現(xiàn)有類型的觸發(fā)器。（7）SQL語句：觸發(fā)器的T-SQL語句，可以有一條或多條語句，指定DML觸發(fā)器觸發(fā)后將要執(zhí)行的動(dòng)作。2）觸發(fā)器說明）觸發(fā)器說明觸發(fā)器

29、有以下幾點(diǎn)說明：（1）觸發(fā)器中使用的特殊表。執(zhí)行觸發(fā)器時(shí)，系統(tǒng)創(chuàng)建了兩個(gè)特殊的臨時(shí)表inserted表和deleted表，下面介紹一下這兩個(gè)表的內(nèi)容。 inserted表：當(dāng)向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。 deleted表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個(gè)DELETE觸發(fā)器時(shí)，被刪除的記錄存放到deleted表中。（2）創(chuàng)建DML觸發(fā)器的說明。創(chuàng)建DML觸發(fā)器時(shí)主要有以下幾點(diǎn)說明： CREATE TRIGGER 語句必須是批處理中的第一條語句，并且只能應(yīng)用到一個(gè)表中。 DML觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫中創(chuàng)建，但可以引用當(dāng)前數(shù)據(jù)庫

30、的外部對(duì)象。 創(chuàng)建DML觸發(fā)器的權(quán)限默認(rèn)分配給表的所有者。 在同一CREATE TRIGGER語句中，可以為多種操作（如INSERT和UPDATE）定義相同的觸發(fā)器操作。 不能對(duì)臨時(shí)表或系統(tǒng)表創(chuàng)建DML觸發(fā)器。 對(duì)于含有DELETE或UPDATE操作定義的外鍵表，不能使用INSTEAD OF DELETE和INSTEAD OF UPDATE觸發(fā)器。 TRUNCATE TABLE語句雖然能夠刪除表中的記錄，但它不會(huì)觸發(fā)DELETE觸發(fā)器。 在觸發(fā)器內(nèi)可以指定任意的SET語句，所選擇的SET選項(xiàng)在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。 DML觸發(fā)器最大的用途是返回行級(jí)數(shù)據(jù)的完整性

31、，而不是返回結(jié)果，所以應(yīng)當(dāng)盡量避免返回任何結(jié)果集。 CREATE TRIGGER權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin固定服務(wù)器角色成員、db_owner和db_ddladmin固定數(shù)據(jù)庫角色成員，并且不可轉(zhuǎn)讓。 DML觸發(fā)器中不能包含以下語句：ALTER DATABASE、CREATE DATABASE、DROP DATABASE、LOAD DATABASE、LOAD LOG、RECONFIGURE、RESTORE DATABASE、RESTORE LOG。（3）創(chuàng)建INSERT觸發(fā)器。INSERT觸發(fā)器是當(dāng)對(duì)觸發(fā)器表執(zhí)行INSERT語句時(shí)就會(huì)激活的觸發(fā)器。INSERT觸發(fā)器可以

32、用來修改，甚至拒絕接收正在插入的記錄?！纠?.10】 創(chuàng)建一個(gè)表table1，其中只有一列a。在表上創(chuàng)建一個(gè)觸發(fā)器，每次插入操作時(shí)，將變量str的值設(shè)為“TRIGGER IS WORKING”并顯示。USE PXSCJGOCREATE TABLE table1(a int)GOCREATE TRIGGER table1_insert ON table1 AFTER INSERT AS BEGIN DECLARE str char(50) SET str=TRIGGER IS WORKING PRINT str END向table1中插入一行數(shù)據(jù)：INSERT INTO table1 VALUE

33、S(10)執(zhí)行結(jié)果如圖7.6所示?！纠?.11】 創(chuàng)建觸發(fā)器，當(dāng)向CJB表中插入一個(gè)學(xué)生的成績(jī)時(shí)，將XSB表中該學(xué)生的總學(xué)分加上添加的課程的學(xué)分。CREATE TRIGGER cjb_insert ON CJB AFTER INSERT AS BEGIN DECLARE num char(6), kc_num char(3) DECLARE xf int SELECT num=學(xué)號(hào), kc_num=課程號(hào) from inserted SELECT xf=學(xué)分 FROM KCB WHERE 課程號(hào)=kc_num UPDATE XSB SET 總學(xué)分=總學(xué)分+xf WHERE 學(xué)號(hào)=num PRI

34、NT 修改成功 END（4）創(chuàng)建UPDATE觸發(fā)器。UPDATE觸發(fā)器在對(duì)觸發(fā)器表執(zhí)行UPDATE語句后觸發(fā)。在執(zhí)行UPDATE觸發(fā)器時(shí)，將觸發(fā)器表的原記錄保存到deleted臨時(shí)表中，將修改后的記錄保存到inserted臨時(shí)表中。【例7.12】 創(chuàng)建觸發(fā)器，當(dāng)修改XSB表中的學(xué)號(hào)時(shí)，同時(shí)也要將CJB表中的學(xué)號(hào)修改成相應(yīng)的學(xué)號(hào)（假設(shè)XSB表和CJB表之間沒有定義外鍵約束）。CREATE TRIGGER xsb_update ON XSB AFTER UPDATE AS BEGIN DECLARE old_num char(6), new_num char(6) SELECT old_num=學(xué)

35、號(hào) FROM deleted SELECT new_num=學(xué)號(hào) FROM inserted UPDATE CJB SET 學(xué)號(hào)=new_num WHERE 學(xué)號(hào)=old_num END接著修改XSB表中的一行數(shù)據(jù)，并查看觸發(fā)器執(zhí)行結(jié)果：UPDATE XSB SET 學(xué)號(hào)=081120 WHERE 學(xué)號(hào)=081101GOSELECT * FROM CJB WHERE 學(xué)號(hào)=081120執(zhí)行結(jié)果如圖7.7所示。（5）創(chuàng)建DELETE觸發(fā)器?！纠?.13】 在刪除XSB表中的一條學(xué)生記錄時(shí)將CJB表中該學(xué)生的相應(yīng)記錄也刪除。CREATE TRIGGER xsb_delete ON XSB AFTE

36、R DELETE AS BEGIN DELETE FROM CJB WHERE 學(xué)號(hào) IN(SELECT 學(xué)號(hào) FROM deleted) END【例7.14】 在KCB表中創(chuàng)建UPDATE和DELETE觸發(fā)器，當(dāng)修改或刪除KCB表中的課程號(hào)字段時(shí)，同時(shí)修改或刪除CJB表中的該課程號(hào)。CREATE TRIGGER kcb_trig ON KCB AFTER UPDATE, DELETE AS BEGIN IF (UPDATE(課程號(hào)) UPDATE CJB SET 課程號(hào)=(SELECT 課程號(hào) FROM inserted) WHERE 課程號(hào)=(SELECT 課程號(hào) FROM deleted

37、) ELSE DELETE FROM CJB WHERE 課程號(hào) IN(SELECT 課程號(hào) FROM deleted) END（6）創(chuàng)建INSTEAD OF觸發(fā)器。AFTER觸發(fā)器是在觸發(fā)語句執(zhí)行后觸發(fā)的，與AFTER觸發(fā)器不同的是，INSTEAD OF觸發(fā)器觸發(fā)時(shí)只執(zhí)行觸發(fā)器內(nèi)部的SQL語句，而不執(zhí)行激活該觸發(fā)器的SQL語句。一個(gè)表或視圖中只能有一個(gè)INSTEAD OF觸發(fā)器?！纠?.15】 創(chuàng)建表table2，值包含一列a，在表中創(chuàng)建INSTEAD OF INSERT觸發(fā)器，當(dāng)向表中插入記錄時(shí)顯示相應(yīng)消息。USE PXSCJGOCREATE TABLE table2(a int)GOCR

38、EATE TRIGGER table2_insert ON table2 INSTEAD OF INSERT AS PRINT INSTEAD OF TRIGGER IS WORKING向表中插入一行數(shù)據(jù)：INSERT INTO table2 VALUES(10)執(zhí)行結(jié)果如圖7.8所示?！纠?.16】 在PXSCJ數(shù)據(jù)庫中創(chuàng)建視圖stu_view，包含學(xué)生學(xué)號(hào)、專業(yè)、課程號(hào)、成績(jī)。該視圖依賴于表XSB和CJB，是不可更新視圖?？梢栽谝晥D上創(chuàng)建INSTEAD OF觸發(fā)器，當(dāng)向視圖中插入數(shù)據(jù)時(shí)分別向表XSB和CJB插入數(shù)據(jù)，從而實(shí)現(xiàn)向視圖插入數(shù)據(jù)的功能。創(chuàng)建視圖：CREATE VIEW stu_v

39、iewAS SELECT XSB.學(xué)號(hào), 專業(yè), 課程號(hào), 成績(jī) FROM XSB, CJB WHERE XSB.學(xué)號(hào)=CJB.學(xué)號(hào)創(chuàng)建INSTEAD OF觸發(fā)器：CREATE TRIGGER InsteadTrig ON stu_view INSTEAD OF INSERT AS BEGIN DECLARE XH char(6), XM char(8), ZY char(12), KCH char(3), CJ int SET XM=佚名 SELECT XH=學(xué)號(hào), ZY=專業(yè), KCH=課程號(hào), CJ=成績(jī) FROM inserted INSERT INTO XSB(學(xué)號(hào), 姓名, 專業(yè))

40、 VALUES(XH, XM, ZY) INSERT INTO CJB VALUES(XH, KCH, CJ) END向視圖插入一行數(shù)據(jù)：INSERT INTO stu_view VALUES(091102, 計(jì)算機(jī), 101, 85 )查看數(shù)據(jù)是否插入：SELECT * FROM stu_view WHERE 學(xué)號(hào)= 091102執(zhí)行結(jié)果如圖7.9所示。查看與視圖關(guān)聯(lián)的XSB表的情況：SELECT * FROM XSB WHERE 學(xué)號(hào)= 091102執(zhí)行結(jié)果如圖7.10所示。2創(chuàng)建創(chuàng)建DDL觸發(fā)器觸發(fā)器語法格式：CREATE TRIGGER 觸發(fā)器名 ON ALL SERVER | DAT

41、ABASE WITH ENCRYPTION FOR | AFTER 事件類型 | 事件組 , . AS SQL語句 ; . | EXTERNAL NAME 程序集名.類名.方法名【例7.17】 創(chuàng)建PXSCJ數(shù)據(jù)庫作用域的DDL觸發(fā)器，當(dāng)刪除一個(gè)表時(shí)，提示禁止該操作，然后回滾刪除表的操作。USE PXSCJGOCREATE TRIGGER safety ON DATABASE AFTER DROP_TABLE AS PRINT 不能刪除該表 ROLLBACK TRANSACTION嘗試刪除表table1：DROP TABLE table1執(zhí)行結(jié)果如圖7.11所示?！纠?.18】 創(chuàng)建服務(wù)器作用

42、域的DDL觸發(fā)器，當(dāng)刪除一個(gè)數(shù)據(jù)庫時(shí)，提示禁止該操作并回滾刪除數(shù)據(jù)庫的操作。CREATE TRIGGER safety_server ON ALL SERVER AFTER DROP_DATABASE AS PRINT 不能刪除該數(shù)據(jù)庫 ROLLBACK TRANSACTION要修改觸發(fā)器執(zhí)行的操作，可以使用ALTER TRIGGER語句。（1）修改DML觸發(fā)器的語法格式：ALTER TRIGGER 架構(gòu)名.觸發(fā)器名 ON ( 表 | 視圖 ) WITH ENCRYPTION ( FOR | AFTER | INSTEAD OF ) DELETE , INSERT , UPDATE NOT FOR REPLICATION AS SQL語句 ; . | EXTERNAL NAME 程序集名.類名.方法名（2）修改DDL