第6章 存儲(chǔ)過程、觸發(fā)器.ppt

1、第6章 存儲(chǔ)過程、觸發(fā)器,6.1 存儲(chǔ)過程,6.2 觸發(fā)器,6.1.1存儲(chǔ)過程的類型,(1) 系統(tǒng)存儲(chǔ)過程 系統(tǒng)存儲(chǔ)過程是由系統(tǒng)提供的存儲(chǔ)過程，可以作為命令執(zhí)行各種操作。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫(kù)master中，其前綴是sp_，例如常用的顯示系統(tǒng)對(duì)象信息的sp_help存儲(chǔ)過程，它們?yōu)闄z索系統(tǒng)表的信息提供了方便快捷的方法。 系統(tǒng)存儲(chǔ)過程允許系統(tǒng)管理員執(zhí)行修改系統(tǒng)表的數(shù)據(jù)庫(kù)管理任務(wù)，可以在任何一個(gè)數(shù)據(jù)庫(kù)中執(zhí)行。常用的系統(tǒng)存儲(chǔ)過程，請(qǐng)見附錄。 (2) 本地存儲(chǔ)過程 本地存儲(chǔ)過程是指在用戶數(shù)據(jù)庫(kù)中創(chuàng)建的存儲(chǔ)過程，這種存儲(chǔ)過程完成特定數(shù)據(jù)庫(kù)操作任務(wù)，其名稱不能以sp_為前綴。 (3) 臨時(shí)存儲(chǔ)過程

2、臨時(shí)存儲(chǔ)過程屬于本地存儲(chǔ)過程。如果本地存儲(chǔ)過程的名稱前面有一個(gè)“#”，該存儲(chǔ)過程就稱為局部臨時(shí)存儲(chǔ)過程，這種存儲(chǔ)過程只能在一個(gè)用戶會(huì)話中使用。 如果本地存儲(chǔ)過程的名稱前有兩個(gè)“#”，該過程就是全局臨時(shí)存儲(chǔ)過程，這種存儲(chǔ)過程可以在所有用戶會(huì)話中使用。 (4) 遠(yuǎn)程存儲(chǔ)過程 遠(yuǎn)程存儲(chǔ)過程指從遠(yuǎn)程服務(wù)器上調(diào)用的存儲(chǔ)過程。 (5) 擴(kuò)展存儲(chǔ)過程 在SQL Server2000環(huán)境之外執(zhí)行的動(dòng)態(tài)鏈接庫(kù)稱為擴(kuò)展存儲(chǔ)過程，其前綴是sp_。使用時(shí)需要先加載到SQL Server2000系統(tǒng)中，并且按照使用存儲(chǔ)過程的方法執(zhí)行。,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,用戶存儲(chǔ)過程只能定義在當(dāng)前數(shù)據(jù)庫(kù)中，可以使用SQ

3、L命令語(yǔ)句或SQL Server的企業(yè)管理器創(chuàng)建存儲(chǔ)過程。缺省情況下，用戶創(chuàng)建的存儲(chǔ)過程歸數(shù)據(jù)庫(kù)所有者擁有，數(shù)據(jù)庫(kù)的所有者可以把許可授權(quán)給其他用戶。 1創(chuàng)建存儲(chǔ)過程 語(yǔ)法格式: CREATE PROC EDURE procedure_name ; number /*定義過程名*/ parameter data_type /*定義參數(shù)的類型*/ VARYING = default OUTPUT /*定義參數(shù)的屬性*/ ,.n1 WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION /*定義存儲(chǔ)過程的處理方式*/ FOR REPLICATION

4、AS sql_statement .n2 /*執(zhí)行的操作*/ 說(shuō)明： 參數(shù)procedure_name用于指定存儲(chǔ)過程名，必須符合標(biāo)識(shí)符規(guī)則，且對(duì)于數(shù)據(jù)庫(kù)及其所有者必須唯一；創(chuàng)建局部臨時(shí)過程，可以在 procedure_name 前面加一個(gè)“#”；創(chuàng)建全局臨時(shí)過程，可以在 procedure_name 前加“#”。 參數(shù)number為可選的整數(shù)，用于區(qū)分同名的存儲(chǔ)過程，以便用一條 DROP PROCEDURE 語(yǔ)句刪除一組存儲(chǔ)過程；,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,FOR REPLICATION用于說(shuō)明不能在訂閱服務(wù)器上執(zhí)行為復(fù)制創(chuàng)建的存儲(chǔ)過程，該選項(xiàng)不能和 WITH RECOMPILE一

5、起使用。參數(shù)sql_statement代表過程體包含的T-SQL 語(yǔ)句，參數(shù)n2說(shuō)明一個(gè)存儲(chǔ)過程可以包含多條 T-SQL 語(yǔ)句。 對(duì)于存儲(chǔ)過程要注意下列幾點(diǎn)： (1) 用戶定義的存儲(chǔ)過程只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建（臨時(shí)過程除外，臨時(shí)過程總是在 tempdb 中創(chuàng)建）。 (2) 成功執(zhí)行 CREATE PROCEDURE 語(yǔ)句后，過程名稱存儲(chǔ)在 sysobjects 系統(tǒng)表中，而 CREATE PROCEDURE 語(yǔ)句的文本存儲(chǔ)在 syscomments 中。 (3) 自動(dòng)執(zhí)行存儲(chǔ)過程 QL Server 啟動(dòng)時(shí)可以自動(dòng)執(zhí)行一個(gè)或多個(gè)存儲(chǔ)過程。這些存儲(chǔ)過程必須由系統(tǒng)管理員在 master 數(shù)據(jù)庫(kù)中創(chuàng)

6、建，并在 sysadmin 固定服務(wù)器角色下作為后臺(tái)過程執(zhí)行。這些過程不能有任何輸入?yún)?shù)。 (4) sql_statement的限制 除了 SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL外，其它SET 語(yǔ)句均可在存儲(chǔ)過程內(nèi)使用。,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,如下語(yǔ)句必須使用對(duì)象所有者名對(duì)數(shù)據(jù)庫(kù)對(duì)象進(jìn)行限定： CREATE TABLE 、ALTER TABLE、DROP TABLE 、TRUNCATE TABLE 、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 及DBCC語(yǔ)句。 權(quán)限。CREATE PROCEDURE的權(quán)限默

7、認(rèn)授予sysadmin固定服務(wù)器角色成員、db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員。sysadmin 固定服務(wù)器角色成員和 db_owner 固定數(shù)據(jù) 庫(kù)角色成員可以將 CREATE PROCEDURE 權(quán)限轉(zhuǎn)讓給其他用戶。 注意：存儲(chǔ)過程的定義不能跨越批處理。 2存儲(chǔ)過程的執(zhí)行 通過EXEC命令可以執(zhí)行一個(gè)已定義的存儲(chǔ)過程。 語(yǔ)法格式： EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.

8、n WITH RECOMPILE ,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,說(shuō)明： 參數(shù)return_status為可選的整型變量，保存存儲(chǔ)過程的返回狀態(tài)， EXECUTE語(yǔ)句使用該變量前，必須對(duì)其定義。參數(shù)procedure_name 和number用于調(diào)用已定義的一組存儲(chǔ)過程中的某一個(gè)，procedure_name代表了存儲(chǔ)過程的組名，number用于指定組中的存儲(chǔ)過程。定義存儲(chǔ)過程組的目的是以便用一條DROP PROCEDURE 語(yǔ)句刪除一組存儲(chǔ)過程，對(duì)過程分組后，不能刪除組中的單個(gè)過程。參數(shù)procedure_name_var代表存儲(chǔ)過程名。parameter為CREATE PROCEDU

9、RE 語(yǔ)句中定義的參數(shù)名； value為存儲(chǔ)過程的實(shí)參；variable為變量，用于保存OUTPUT參數(shù)返回的值。DEFAULT關(guān)鍵字表示不提供實(shí)參，而是使用對(duì)應(yīng)的默認(rèn)值。n：表示實(shí)參可有多個(gè)。關(guān)鍵字WITH RECOMPILE指定強(qiáng)制編譯。 存儲(chǔ)過程的執(zhí)行要注意下列幾點(diǎn)： (1) 如果存儲(chǔ)過程名的前三個(gè)字符為 sp_，SQL Server 會(huì)在 Master 數(shù)據(jù)庫(kù)中尋找該過程。如果沒能找到合法的過程名，SQL Server 會(huì)尋找所有者名稱為 dbo 的過程。 (2) 參數(shù)可以通過 value 或 parameter_name = value 提供。 (3) 執(zhí)行存儲(chǔ)過程時(shí)，若語(yǔ)句是批處理

10、中的第一個(gè)語(yǔ)句，則不一定要指定EXECUTE 關(guān)鍵字。,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,3存儲(chǔ)過程的幾種情況 (1) 不使用任何參數(shù)的存儲(chǔ)過程 【例6.1】從XSCJ數(shù)據(jù)庫(kù)的三個(gè)表中查詢，返回學(xué)生學(xué)號(hào)、姓名、課程名、成績(jī)、 學(xué)分。該存儲(chǔ)過程不使用任何參數(shù)。 /*創(chuàng)建存儲(chǔ)過程*/ CREATE PROCEDURE student_info AS SELECT a.學(xué)號(hào), 姓名, 課程名, 成績(jī), t.學(xué)分 FROM XS a INNER JOIN XS_KC b ON a.學(xué)號(hào) = b.學(xué)號(hào) INNER JOIN KC t ON b.課程號(hào)= t.課程號(hào) student_info 存儲(chǔ)過程可

11、以通過以下方法執(zhí)行： EXECUTE student_info 或者 EXEC student_info 如果該過程是批處理中的第一條語(yǔ)句，則可使用： student_info,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,(2) 使用帶參數(shù)的存儲(chǔ)過程 【例6.2】從XSCJ數(shù)據(jù)庫(kù)的三個(gè)表中查詢某人指定課程的成績(jī)和學(xué)分。該存儲(chǔ)過程接受與傳遞參數(shù)精確匹配的值。 CREATE PROCEDURE student_info1 name char (8),cname char(16) AS SELECT a.學(xué)號(hào), 姓名, 課程名, 成績(jī), 學(xué)分 FROM XS a INNER JOIN XS_KC b ON

12、a.學(xué)號(hào) = b.學(xué)號(hào) INNER JOIN KC t ON b.課程號(hào)= t.課程號(hào) WHERE a.姓名=name and t.課程名=cname student_info1 存儲(chǔ)過程有多種執(zhí)行方式，下面列出了一部分： EXECUTE student_info1 王林,計(jì)算機(jī)基礎(chǔ) 或者 EXECUTE student_info1 name=王林, cname=計(jì)算機(jī)基礎(chǔ) 或者 EXECUTE student_info1 cname=計(jì)算機(jī)基礎(chǔ), name=王林 或者 EXEC student_info1 王林, 計(jì)算機(jī)基礎(chǔ) 或者 EXEC au_info cname=計(jì)算機(jī)基礎(chǔ), name

13、=王林 ,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,(3) 使用帶有通配符參數(shù)的存儲(chǔ)過程 【例6.3】從三個(gè)表的聯(lián)接中返回指定學(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 XS a INNER JOIN XS_KC b ON a.學(xué)號(hào) =b.學(xué)號(hào) INNER JOIN KC c ON c.課程號(hào)= b.課程號(hào) WHERE 姓名 LIKE name st_info

14、存儲(chǔ)過程可以有多種執(zhí)行形式，下面列出了一部分： EXECUTE st_info /*參數(shù)使用默認(rèn)值*/ 或者 EXECUTE st_info 王% /*傳遞給name 的實(shí)參為王%*/ 或者 EXECUTE st_info 王張% (4) 使用帶OUTPUT參數(shù)的存儲(chǔ)過程,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,【例6.4】用于計(jì)算指定學(xué)生的總學(xué)分，存儲(chǔ)過程中使用了一個(gè)輸入?yún)?shù)和一個(gè)輸出參數(shù)。 CREATE PROCEDURE totalcredit name varchar(40), total int OUTPUT AS SELECT total= SUM(KC.學(xué)分) FROM XS,XS_

15、KC,KC WHERE 姓名=name AND XS.學(xué)號(hào)= XS_KC.學(xué)號(hào) and XS_KC.課程號(hào)=KC.課程號(hào) GROUP BY XS.學(xué)號(hào) 注意：OUTPUT 變量必須在創(chuàng)建表和使用該變量時(shí)都進(jìn)行定義。 定義時(shí)的參數(shù)名和調(diào)用時(shí)的變量名不一定要匹配，不過數(shù)據(jù)類型和參數(shù)位置必須匹。 DECLARE t_credit char(20),total int EXECUTE totalcredit 王林, total OUTPUT SELECT 王林, total (5) 使用OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過程 OUTPUT 游標(biāo)參數(shù)用于返回存儲(chǔ)過程的局部游標(biāo)。 【例6.5】在 XSCJ數(shù)據(jù)庫(kù)的

16、XS表上聲明并打開一個(gè)游標(biāo)。 CREATE PROCEDURE st_cursor st_cursor CURSOR VARYING OUTPUT AS SET st_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT * FROM XS OPEN st_cursor,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,在如下的批處理中，聲明一局部游標(biāo)變量，執(zhí)行上述存儲(chǔ)過程過程并將游標(biāo)賦值給局部游標(biāo)變量，然后通過該游標(biāo)變量讀取記錄。 DECLARE MyCursor CURSOR EXEC st_cursor st_cursor = MyCursor OUTPUT W

17、HILE (FETCH_STATUS = 0) BEGIN FETCH NEXT FROM MyCursor END CLOSE MyCursor DEALLOCATE MyCursor (6) 使用 WITH ENCRYPTION 選項(xiàng) WITH ENCRYPTION 子句對(duì)用戶隱藏存儲(chǔ)過程的文本。 【例6.6】創(chuàng)建加密過程，使用 sp_helptext 系統(tǒng)存儲(chǔ)過程獲取關(guān)于加密過程的信息，然后嘗試直接從 syscomments 表中獲取關(guān)于該過程的信息。 CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM XS,6.

18、1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,通過系統(tǒng)存儲(chǔ)過程sp_helptext可顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本。 執(zhí)行如下語(yǔ)句： EXEC sp_helptext encrypt_this 結(jié)果集為提示信息：對(duì)象備注已加密。 (7) 創(chuàng)建用戶定義的系統(tǒng)存儲(chǔ)過程 【例6.7】創(chuàng)建一個(gè)過程，顯示表名以 xs 開頭的所有表及其對(duì)應(yīng)的索引。如果沒有指定參數(shù)，該過程將返回表名以 kc 開頭的所有表及對(duì)應(yīng)的索引。 USE master GO CREATE PROCEDURE sp_showtable TABLE varchar(30) = kc% AS SELECT tab.

19、name AS TABLE_NAME, AS INDEX_NAME, indid AS INDEX_ID FROM sysindexes inx INNER JOIN sysobjects tab ON tab.id = inx.id WHERE LIKE TABLE GO USE XSCJ EXEC sp_showtable xs% GO,6.1.2用戶存儲(chǔ)過程的創(chuàng)建與執(zhí)行,4利用企業(yè)管理器編輯用戶存儲(chǔ)過程 如果要通過企業(yè)管理器界面定義一個(gè)存儲(chǔ)過程查詢XSCJ數(shù)據(jù)庫(kù)中每個(gè)同學(xué)各門功課的成績(jī)，步驟如下： 第1步 在SQL Server 企業(yè)管理器窗口中，選擇

20、相應(yīng)的服務(wù)器、數(shù)據(jù)庫(kù)和存儲(chǔ)過程圖標(biāo)（本例選擇XSCJ數(shù)據(jù)庫(kù)），單擊鼠標(biāo)右鍵，出現(xiàn)如圖6.1所示的快捷菜單。 第2步 選擇新建存儲(chǔ)過程，則出現(xiàn)編輯存儲(chǔ)過程的屬性窗口，在窗口中輸入定義的存儲(chǔ)過程，如圖6.2所示，然后選擇“確定”按鈕。,圖6.1 存儲(chǔ)過程的快捷菜單 圖6.2 編輯存儲(chǔ)過程的屬性窗口,6.1.3用戶存儲(chǔ)過程的修改,使用ALTER PROCEDURE命令可修改已存在的存儲(chǔ)過程并保留以前賦予的許可。 語(yǔ)法格式： ALTER PROC EDURE procedure_name ; number parameter data_type VARYING 0= default OUTPUT ,.

21、n1 WITH RECOMPILE | ENCRYPTION| RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n2 說(shuō)明： 各參數(shù)含義與CREATE PROCEDURE相同。 如果原來(lái)的過程定義是用 WITH ENCRYPTION 或 WITH RECOMPILE 創(chuàng)建的，那么只有在 ALTER PROCEDURE 中也包含這些選項(xiàng)時(shí)，這些選項(xiàng)才有效。 ALTER PROCEDURE 權(quán)限默認(rèn)授予 sysadmin 固定服務(wù)器角色成員、db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員和過程的所有者且不可轉(zhuǎn)讓。,

22、6.1.3用戶存儲(chǔ)過程的修改,用 ALTER PROCEDURE 更改后，過程的權(quán)限和啟動(dòng)屬性保持不變。 【例6.8】對(duì)存儲(chǔ)過程student_info1進(jìn)行修改。 USE XSCJ GO ALTER PROCEDURE student_info1 name char(8),cname char(16) AS SELECT a.學(xué)號(hào), 姓名, 課程名, 成績(jī), t.學(xué)分 FROM XS a INNER join XS_KC b ON a.學(xué)號(hào) = b.學(xué)號(hào) INNER JOIN KC t ON b.課程號(hào)= t.課程號(hào) WHERE a.姓名=name and t.課程名=cname GO 【例

23、6.9】創(chuàng)建名為 select_students 的存儲(chǔ)過程，默認(rèn)情況下，該過程可查詢所有學(xué)生信息，隨后授予權(quán)限。當(dāng)該過程需更改為能檢索計(jì)算機(jī)專業(yè)的學(xué)生信息時(shí)，用 ALTER PROCEDURE 重新定義該存儲(chǔ)過程。,6.1.3用戶存儲(chǔ)過程的修改,USE XSCJ GO CREATE PROCEDURE select_students /*創(chuàng)建存儲(chǔ)過程*/ AS SELECT * FROM XS ORDER BY 學(xué)號(hào) GO 修改存儲(chǔ)過程select_students ALTER PROCEDURE select_students WITH ENCRYPTION AS SELECT * FRO

24、M XS WHERE 專業(yè)名= 計(jì)算機(jī) ORDER BY 學(xué)號(hào) GO,6.1.4用戶存儲(chǔ)過程的刪除,當(dāng)不再使用一個(gè)存儲(chǔ)過程時(shí)，就要把它從數(shù)據(jù)庫(kù)中刪除。使用DROP PROCEDURE 語(yǔ)句可永久地刪除存儲(chǔ)過程。在此之前，必須確認(rèn)該存儲(chǔ)過程沒有任何依賴關(guān)系。 語(yǔ)法格式： DROP PROCEDURE procedure ,.n 說(shuō)明： procedure指要?jiǎng)h除的存儲(chǔ)過程或存儲(chǔ)過程組的名稱；n：表示可以指定多個(gè)存儲(chǔ)過程同時(shí)刪除。默認(rèn)情況下，將 DROP PROCEDURE 權(quán)限授予過程所有者，該權(quán)限不可轉(zhuǎn)讓。 db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員和 sysadmin

25、固定服務(wù)器角色成員可以通過在 DROP PROCEDURE 內(nèi)指定所有者刪除任何對(duì)象。 若要查看過程名列表，可使用 sp_help系統(tǒng)存儲(chǔ)過程。若要顯示過程定義（存儲(chǔ)在 syscomments 系統(tǒng)表內(nèi)），可使用 sp_helptext。 【例6.10】刪除 XSCJ數(shù)據(jù)庫(kù)中的student_info1 存儲(chǔ)過程。 USE XSCJ GO DROP PROCEDURE student_info1,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,SQL命令創(chuàng)建觸發(fā)器 語(yǔ)法格式： CREATE TRIGGER trigger_name ON table | view /*指定操作對(duì)象*/ WITH ENCR

26、YPTION /*說(shuō)明是否采用加密方式*/ FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION /*說(shuō)明該觸發(fā)器不用于復(fù)制*/ AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n /*兩個(gè)IF子句用于說(shuō)明觸發(fā)器執(zhí)行的條件*/ sql_statemen

27、t .n /*一條或若干條SQL語(yǔ)句*/ ,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,說(shuō)明： 參數(shù)trigger_name用于指定觸發(fā)器名。觸發(fā)器名必須符合標(biāo)識(shí)符規(guī)則，并且在數(shù)據(jù)庫(kù)中必須唯一，可以包含觸發(fā)器所有者名。Table | view指在其上執(zhí)行觸發(fā)器的表或視圖，有時(shí)稱為觸發(fā)器表或觸發(fā)器視圖?？梢园砘蛞晥D的所有者名。使用關(guān)鍵字WITH ENCRYPTION可防止將觸發(fā)器作為 SQL Server 復(fù)制的一部分發(fā)布。 bitwise_operator為用于比較運(yùn)算的位運(yùn)算符。updated_bitmask為整型的位屏蔽碼，與實(shí)際更新或插入的列對(duì)應(yīng)，例如：表t包含列 C0、C1、C2、C3

28、和 C4。假定該表上有 UPDATE 觸發(fā)器，若要檢查列 C0、C2 、C4是否都有更新，可指定updated_bitmask的值為00010101=0 x15；若要檢查是否只有列 C1 有更新，可指定updated_bitmask的值為000000102。 comparison_operator為比較運(yùn)算符；column_bitmask為列屏蔽碼，用來(lái)檢查是否已更新或插入了對(duì)應(yīng)列。參數(shù)sql_statement為觸發(fā)器的T-SQL語(yǔ)句，當(dāng)執(zhí)行DELETE、INSERT 或 UPDATE操作時(shí)，對(duì)應(yīng)的觸發(fā)器操作將生效。n表示觸發(fā)器中可以包含多條 T-SQL 語(yǔ)句。 2觸發(fā)器中使用的特殊表 執(zhí)行

29、觸發(fā)器時(shí)，系統(tǒng)創(chuàng)建了兩個(gè)特殊的邏輯表inserted表和deleted表，下面介紹一下這兩個(gè)表的內(nèi)容。 inserted 邏輯表：當(dāng)向表中插入數(shù)據(jù)時(shí)，INSERT觸發(fā)器觸發(fā)執(zhí)行，新的記錄插入到觸發(fā)器表和inserted表中。,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,deleted邏輯表：用于保存已從表中刪除的記錄，當(dāng)觸發(fā)一個(gè)DELETE觸發(fā)器時(shí)，被刪除的記錄存放到deleted邏輯表中。 修改一條記錄等于插入一新記錄，同時(shí)刪除舊記錄。當(dāng)對(duì)定義了UPDATE觸發(fā)器的表記錄修改時(shí)，表中原記錄移到deleted表中，修改過的記錄插入到inserted表中。觸發(fā)器可檢查deleted表、inserted

30、表及被修改的表。 例如，若要檢索 deleted、inserted 表中的所有記錄，可使用如下語(yǔ)句： SELECT * FROM deleted SELECT * FROM inserted deleted、inserted 邏輯表的查詢方法與數(shù)據(jù)庫(kù)表的查詢方法相同。 3使用觸發(fā)器的限制 使用觸發(fā)器有下列限制： (1) CREATE TRIGGER 必須是批處理中的第一條語(yǔ)句，并且只能應(yīng)用到一個(gè)表中。 (2) 觸發(fā)器只能在當(dāng)前的數(shù)據(jù)庫(kù)中創(chuàng)建，但觸發(fā)器可以引用當(dāng)前數(shù)據(jù)庫(kù)的外部對(duì)象。 (3) 如果指定觸發(fā)器所有者名限定觸發(fā)器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER語(yǔ)句

31、中，可以為多種操作（如 INSERT 和 UPDATE）定義相同的觸發(fā)器操作。,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,(5) 如果一個(gè)表的外鍵在 DELETE、UPDATE 操作上定義了級(jí)聯(lián)，則不能在該表上定義 INSTEAD OF DELETE、INSTEAD OF UPDATE 觸發(fā)器。 (7) 在觸發(fā)器內(nèi)可以指定任意的 SET 語(yǔ)句，所選擇的 SET 選項(xiàng)在觸發(fā)器執(zhí)行期間有效 ，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。 (8) 觸發(fā)器中不允許包含以下 T-SQL 語(yǔ)句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATAB

32、ASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、DISK INIT、DISK RESIZE和RECONFIGURE (9) 觸發(fā)器不能返回任何結(jié)果，為了阻止從觸發(fā)器返回結(jié)果，不要在觸發(fā)器定義中包含 SELECT 語(yǔ)句或變量賦值。如果必須在觸發(fā)器中進(jìn)行變量賦值，則應(yīng)該在觸發(fā)器的開頭使用 SET NOCOUNT 語(yǔ)句以避免返回任何結(jié)果集。 4權(quán)限 CREATE TRIGGER 權(quán)限默認(rèn)授予定義觸發(fā)器的表所有者、sysadmin 固定服務(wù)器角色成員、db_owner 和 db_ddladmin 固定數(shù)據(jù)庫(kù)角色成員，并且不可轉(zhuǎn)讓。,6.2.1 利用SQL命令創(chuàng)建觸發(fā)

33、器,【例6.11】對(duì)于XSCJ數(shù)據(jù)庫(kù)，如果在XS表中添加或更改數(shù)據(jù)，則將向客戶端顯示一條信息。 /*使用帶有提示消息的觸發(fā)器*/ USE XSCJ GO CREATE TRIGGER reminder ON XS FOR INSERT, UPDATE AS RAISERROR (4008, 16, 10) GO 消息 4008 是sysmessages 中的用戶定義消息。有關(guān)創(chuàng)建用戶定義消息的方法請(qǐng)參考附錄的sp_addmessage存儲(chǔ)過程。,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,【例6.12】在數(shù)據(jù)庫(kù)XSCJ中創(chuàng)建一觸發(fā)器，當(dāng)向XS_KC表插入一記錄時(shí)，檢查該記錄的學(xué)號(hào)在XS表是否存在，檢

34、查課程號(hào)在KC表中是否存在，若有一項(xiàng)為否，則不允許插入。 USE XSCJ GO CREATE TRIGGER check_trig ON XS_KC FOR INSERT AS SELECT * FROM inserted a WHERE a.學(xué)號(hào) NOT IN (SELECT b.學(xué)號(hào) FROM XS b) OR a.課程號(hào) NOT IN (SELECT c.課程號(hào) FROM KC c) BEGIN RAISERROR (違背數(shù)據(jù)的一致性., 16, 1) ROLLBACK TRANSACTION END,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,【例6.13】在XSCJ數(shù)據(jù)庫(kù)的XS_KC表上

35、創(chuàng)建一觸發(fā)器，若對(duì)學(xué)號(hào)列和課程號(hào)列修改，則給出提示信息，并取消修改操作。 通過調(diào)用 COLUMNS_UPDATED()函數(shù)，可快速測(cè)試對(duì)學(xué)號(hào)列和課程號(hào)列修改所做的更改。 USE XSCJ GO CREATE TRIGGER update_trig ON XS_KC FOR update AS /*檢查學(xué)號(hào)列（C0）和課程號(hào)列（C1）是否被修改，如果有某些列被修改了，則取消修改操作。*/ IF (COLUMNS_UPDATED() & 3) 0 BEGIN RAISERROR (違背數(shù)據(jù)的一致性., 16, 1) ROLLBACK TRANSACTION END GO,6.2.1 利用SQL命令

36、創(chuàng)建觸發(fā)器,5. INSTEAD OF觸發(fā)器的設(shè)計(jì) 如果視圖的數(shù)據(jù)來(lái)自于多個(gè)基表，則必須使用INSTEAD OF 觸發(fā)器支持引用表中數(shù)據(jù)的插入、更新和刪除操作。 例如，若在一個(gè)多表視圖上定義了 INSTEAD OF INSERT 觸發(fā)器，視圖各列的值可能允許為空也可能不允許為空，若視圖某列的值不允許為空，則 INSERT 語(yǔ)句必須為該列提供相應(yīng)的值。 如果視圖的列為以下幾種情況之一： （1）基表中的計(jì)算列。 （2）IDENTITY INSERT 為 OFF 的基表中的標(biāo)識(shí)列。 （3）具有 timestamp 數(shù)據(jù)類型的基表列。 該視圖的INSERT語(yǔ)句必須為這些列指定值，INSTEAD OF

37、觸發(fā)器在構(gòu)成將值插入基表的 INSERT 語(yǔ)句時(shí)會(huì)忽略指定的值。 【例6.14】在XSCJ數(shù)據(jù)庫(kù)中創(chuàng)建表、視圖和觸發(fā)器，以說(shuō)明INSTEAD OF INSERT觸發(fā)器的使用。,6.2.1 利用SQL命令創(chuàng)建觸發(fā)器,直接引用 books表的 INSERT 語(yǔ)句不能為BookKey字段和.ComputedCol.字段提供值，例如： -正確的INSERT語(yǔ)句 INSERT INTO books(BookName ,Color, Pages) VALUES (計(jì)算機(jī)輔助設(shè)計(jì), 紅色,100) -查看INSERT 語(yǔ)句的執(zhí)行結(jié)果: SELECT * FROM books -不正確的INSERT語(yǔ)句 IN

38、SERT INTO books VALUES (2, 計(jì)算機(jī)輔助設(shè)計(jì), 紅色, 綠色,100) 但對(duì)于引用 View2 視圖的 INSERT 語(yǔ)句視圖的每一列都指定值，例如： -對(duì)于視圖View2，正確的 INSERT 語(yǔ)句 INSERT INTO View2 (BookKey ,BookName ,Color, ComputedCol ,Pages) VALUES (4, 計(jì)算機(jī)輔助設(shè)計(jì), 紅色, 綠色,100) -查看INSERT 語(yǔ)句的執(zhí)行結(jié)果 SELECT * FROM View2 在執(zhí)行視圖的插入語(yǔ)句時(shí)，雖然將 BookKey和 ComputedCol字段的值傳遞到了InsteadTrig觸發(fā)器，但觸發(fā)器中的INSERT語(yǔ)句沒有選擇 inserted表BookKey和 ComputedCol字段的值。