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

1、第五章第五章 存儲過程、觸發(fā)器和數(shù)據(jù)完整性存儲過程、觸發(fā)器和數(shù)據(jù)完整性 （ SQLSQL的高級功能）的高級功能） l 存儲過程存儲過程 l 觸發(fā)器觸發(fā)器 l 數(shù)據(jù)完整性數(shù)據(jù)完整性 任務(wù)任務(wù) l掌握存儲過程，觸發(fā)器的概念和使用方法；掌握存儲過程，觸發(fā)器的概念和使用方法； l掌握運(yùn)用掌握運(yùn)用T-SQL編寫基本的存儲過程、觸發(fā)器。編寫基本的存儲過程、觸發(fā)器。 （ ） l理解存儲過程、觸發(fā)器的用途；理解存儲過程、觸發(fā)器的用途； l進(jìn)一步理解數(shù)據(jù)完整性的含義（進(jìn)一步理解數(shù)據(jù)完整性的含義（） l了解數(shù)據(jù)完整性的規(guī)則、默認(rèn)值等使用。了解數(shù)據(jù)完整性的規(guī)則、默認(rèn)值等使用。 5.1 存儲過程存儲過程 l5.1.1

2、 存儲過程的基本概念存儲過程的基本概念 l5.1.2 存儲過程的優(yōu)點(diǎn)存儲過程的優(yōu)點(diǎn) l5.1.3 存儲過程的分類存儲過程的分類 l5.1.4 存儲過程的使用方法存儲過程的使用方法 5.1 存儲過程存儲過程 l傳統(tǒng)的數(shù)據(jù)庫結(jié)構(gòu)傳統(tǒng)的數(shù)據(jù)庫結(jié)構(gòu) l管理數(shù)據(jù)等共享資源，管理數(shù)據(jù)等共享資源， l所有的應(yīng)用程序都在用戶端，都與用戶實(shí)際運(yùn)行的應(yīng)所有的應(yīng)用程序都在用戶端，都與用戶實(shí)際運(yùn)行的應(yīng) 用程序捆綁在一起；用程序捆綁在一起； l客戶客戶/服務(wù)器數(shù)據(jù)庫服務(wù)器數(shù)據(jù)庫 l管理數(shù)據(jù)等共享資源管理數(shù)據(jù)等共享資源 l承擔(dān)一些應(yīng)用邏輯，完成來自客戶端的一些處理請求，承擔(dān)一些應(yīng)用邏輯，完成來自客戶端的一些處理請求， 在

3、數(shù)據(jù)庫中還可以存放程序，即存儲過程。在數(shù)據(jù)庫中還可以存放程序，即存儲過程。 5.1.1 存儲過程的基本概念存儲過程的基本概念 l是什么是什么：是事先編好的、存儲在服務(wù)器端的數(shù)據(jù)：是事先編好的、存儲在服務(wù)器端的數(shù)據(jù) 庫中的程序庫中的程序（預(yù)編譯的（預(yù)編譯的SQL集合），這些程序用集合），這些程序用 來完成對數(shù)據(jù)庫的指定操作。來完成對數(shù)據(jù)庫的指定操作。 l怎么使用怎么使用：這些程序可以由應(yīng)用程序的調(diào)用啟動，：這些程序可以由應(yīng)用程序的調(diào)用啟動， 或由數(shù)據(jù)完整性規(guī)則和觸發(fā)器調(diào)用。或由數(shù)據(jù)完整性規(guī)則和觸發(fā)器調(diào)用。 l怎么理解怎么理解：存儲過程是用戶可以簡單地將其作為：存儲過程是用戶可以簡單地將其作為 一

4、個函數(shù)來調(diào)用，無須重復(fù)執(zhí)行存儲過程的一個函數(shù)來調(diào)用，無須重復(fù)執(zhí)行存儲過程的SQL 語句。它包含一組經(jīng)常執(zhí)行的、邏輯完整的語句。它包含一組經(jīng)常執(zhí)行的、邏輯完整的SQL 語句。語句。 5.1.2 存儲過程的優(yōu)存儲過程的優(yōu)點(diǎn)點(diǎn) l減輕程序編寫的工作量減輕程序編寫的工作量：可以在各個程序中反復(fù)調(diào)用定：可以在各個程序中反復(fù)調(diào)用定 義好的存儲過程。義好的存儲過程。 l存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度存儲過程能夠?qū)崿F(xiàn)較快的執(zhí)行速度 ：因?yàn)榇鎯^程是預(yù)因?yàn)榇鎯^程是預(yù) 編譯的，而批處理編譯的，而批處理的的T- SQL 語句在每次運(yùn)行時都要進(jìn)語句在每次運(yùn)行時都要進(jìn) 行編譯和優(yōu)化，因此速度相對要慢一些。行編譯和優(yōu)化

5、，因此速度相對要慢一些。 l 存儲過程能夠減少網(wǎng)絡(luò)流量存儲過程能夠減少網(wǎng)絡(luò)流量 ： 對于同一個針對數(shù)據(jù)庫對于同一個針對數(shù)據(jù)庫 對象的操作，所涉及到的對象的操作，所涉及到的 T-SQL 語句被組織成一存儲語句被組織成一存儲 過程，當(dāng)在客戶端調(diào)用該存儲過程時，過程，當(dāng)在客戶端調(diào)用該存儲過程時，網(wǎng)絡(luò)中傳送的只網(wǎng)絡(luò)中傳送的只 是該調(diào)用語句是該調(diào)用語句 ，降低網(wǎng)絡(luò)負(fù)載，降低網(wǎng)絡(luò)負(fù)載 。 l存儲過程增加安全機(jī)制：存儲過程增加安全機(jī)制：系統(tǒng)管理員通過對執(zhí)行某一存系統(tǒng)管理員通過對執(zhí)行某一存 儲過程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)訪儲過程的權(quán)限進(jìn)行限制，從而能夠?qū)崿F(xiàn)對相應(yīng)的數(shù)據(jù)訪 問權(quán)限的限制，避免非授

6、權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù)問權(quán)限的限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問，保證數(shù) 據(jù)的安全。據(jù)的安全。 客戶端應(yīng)用客戶端應(yīng)用 （不使用存儲過程）（不使用存儲過程） 客戶端應(yīng)用客戶端應(yīng)用 （使用存儲過程）（使用存儲過程） . Start transaction . INSERT data UPDATE data DELETE data End transaction . . Start transaction . Call Stored procedure End transaction . DBMS Server DBMS Server Procedure: INSERT data UPDATE d

7、ata DELETE data (a) (b) 不使用存儲不使用存儲 過程時，所有的過程時，所有的 數(shù)據(jù)處理都在客數(shù)據(jù)處理都在客 戶端完成；而使戶端完成；而使 用存儲過程時，用存儲過程時， 可以使數(shù)據(jù)處理可以使數(shù)據(jù)處理 在服務(wù)器端完成。在服務(wù)器端完成。 5.1.3 儲存過程的分類儲存過程的分類 l系統(tǒng)存儲過程系統(tǒng)存儲過程 lSQL Server本身提供了一些存儲過程，用于管理本身提供了一些存儲過程，用于管理 SQL Server和顯示有關(guān)數(shù)據(jù)庫和用戶的信息，我們和顯示有關(guān)數(shù)據(jù)庫和用戶的信息，我們 稱之為系統(tǒng)存儲過程。系統(tǒng)存儲過程都以稱之為系統(tǒng)存儲過程。系統(tǒng)存儲過程都以“sp_” 開頭，存儲在開

8、頭，存儲在master數(shù)據(jù)庫中。數(shù)據(jù)庫中。 l用戶存儲過程用戶存儲過程 l用戶也可以編寫自己的存儲過程，并把它存放在數(shù)用戶也可以編寫自己的存儲過程，并把它存放在數(shù) 據(jù)庫中。這樣安排的主要目的就是要充分發(fā)揮數(shù)據(jù)據(jù)庫中。這樣安排的主要目的就是要充分發(fā)揮數(shù)據(jù) 庫服務(wù)器的功能，盡量減少網(wǎng)絡(luò)庫服務(wù)器的功能，盡量減少網(wǎng)絡(luò)上的堵塞。上的堵塞。 5.1.4 存儲過程的使用方法存儲過程的使用方法 l創(chuàng)建存儲過程創(chuàng)建存儲過程 l執(zhí)行存儲過程執(zhí)行存儲過程 l修改存儲過程修改存儲過程 l刪除存儲過程刪除存儲過程 創(chuàng)建存儲過程創(chuàng)建存儲過程 CREATE PROCedure procedure_name ; number

9、 parameter data_type = default , AS sql_statement s s procedure_name：給出存儲過程名；：給出存儲過程名； s s number：對同名的存儲過程指定一個序號；：對同名的存儲過程指定一個序號； s s parameter：給出參數(shù)名；：給出參數(shù)名； s s data_type：指出參數(shù)的數(shù)據(jù)類型；：指出參數(shù)的數(shù)據(jù)類型； s s = default：給出參數(shù)的默認(rèn)值；：給出參數(shù)的默認(rèn)值； s s sql_statement：存儲過程所要執(zhí)行的：存儲過程所要執(zhí)行的SQL語句，它可以語句，它可以 是一組是一組SQL語句，可以包含流程控

10、制語句等。語句，可以包含流程控制語句等。 例：創(chuàng)建一個最簡單的存儲過程（無參數(shù)調(diào)用）：例：創(chuàng)建一個最簡單的存儲過程（無參數(shù)調(diào)用）： CREATE PROCedure sp_getemp;1 AS SELECT * FROM 職工 說明：創(chuàng)建存儲過程說明：創(chuàng)建存儲過程sp_getemp;1，要求查詢職工信息，要求查詢職工信息 例：帶參數(shù)的存儲過程：例：帶參數(shù)的存儲過程： CREATE PROCedure sp_getemp;2 (salary int) AS SELECT * FROM 職工 WHERE 工資 salary 說明：創(chuàng)建存儲過程說明：創(chuàng)建存儲過程sp_getemp;2，要求查詢工資

11、值大，要求查詢工資值大 于給定值的職工信息于給定值的職工信息 說明：說明：存儲過程一般用來完成數(shù)據(jù)查詢和數(shù)據(jù)處理操作， 所以在存儲過程中不可以使用創(chuàng)建數(shù)據(jù)庫對象的語句， 即在存儲過程中一般不能含有以下語句： CREATE TABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE 執(zhí)行存儲過程執(zhí)行存儲過程 EXECute = =| 例：執(zhí)行帶參數(shù)的例：執(zhí)行帶參數(shù)的sp_getemp;2存儲過程存儲過程 Execute sp_getemp;2 1240 說明：執(zhí)行存儲過程說明：執(zhí)行存儲過程sp_gete

12、mp;2，要求查詢工資值大，要求查詢工資值大 于于1240元的職工信息元的職工信息 存儲過程的返回值和狀態(tài)信息存儲過程的返回值和狀態(tài)信息 無論什么時候執(zhí)行存儲過程，總要返回 一個結(jié)果碼，用以指示存儲過程的執(zhí)行狀態(tài)。 如果存儲過程執(zhí)行成功，返回的結(jié)果碼是0； 如果存儲過程執(zhí)行失敗，返回的結(jié)果碼一般 是一個負(fù)數(shù)，它和失敗的類型有關(guān)。我們在 創(chuàng)建存儲過程時，也可以定義自己的狀態(tài)碼 和錯誤信息。 常用的存儲過程返回狀態(tài)表常用的存儲過程返回狀態(tài)表 0 過程成功執(zhí)行 1 對象丟失 2 發(fā)生數(shù)據(jù)類型錯誤 3 處理過程被死鎖 4 發(fā)生權(quán)限錯誤 5 發(fā)生語法錯誤 6 發(fā)生惡意用戶錯誤 7 發(fā)生資源錯誤 8 遭遇

13、非致命的內(nèi)部錯誤 9 遭遇系統(tǒng)限制 10 發(fā)生致命的內(nèi)部不穩(wěn)定性 12 表或索引被破壞 13 數(shù)據(jù)庫被破壞 14 發(fā)生硬盤錯誤 通常用全局變量ERROR 返回最后執(zhí)行的SQL 語句的錯誤代碼。 CREATE PROCedure sp_getemp;3 (salary int =NULL) AS IF salary IS NULL BEGIN PRINT 必須提供一個數(shù)值作參數(shù)！ RETURN 13 END IF NOT EXISTS (SELECT * FROM 職工 WHERE 工資 salary) BEGIN PRINT 沒有滿足條件的記錄！ RETURN -103 END SELECT

14、* FROM 職工 WHERE 工資 salary RETURN 0 例：帶參數(shù)和返回狀態(tài)值的存儲過程。 利用全局變量修改剛才的例子利用全局變量修改剛才的例子 CREATE PROCedure sp_getemp;3 (salary int =NULL) AS SELECT * FROM 職工 WHERE 工資 salary IF ERROR = 0 RETURN 0 ELSE RETURN -1 例：執(zhí)行以上存儲過程。 DECLARE status int EXECUTE status=sp_getemp;3 salary 1200 print status 存儲過程的修改和刪除存儲過程的修

15、改和刪除 l修改存儲過程的語句是（一般格式）： ALTER PROCedure procedure_name ; number parameter data_type = default , AS sql_statement l刪除存儲過程的語句是： DROP PROCedure procedure_name 注意：刪除存儲過程的語句中不能指定序號。也就是說，該語 句將同時刪除同名的所有存儲過程。 小結(jié)：要用好存儲過程小結(jié)：要用好存儲過程 存儲過程是客戶/服務(wù)器機(jī)制的一個重要 組成部分，如果使用客戶/服務(wù)器機(jī)制的數(shù)據(jù) 庫管理系統(tǒng)，但是不理解存儲過程或沒有充 分利用存儲過程，那將使客戶/服務(wù)器機(jī)

16、制的 功能大打折扣，使系統(tǒng)的整體性能可能降低 很多。 5.2 觸發(fā)器觸發(fā)器 l5.2.1 觸發(fā)器的基本概念觸發(fā)器的基本概念 l5.2.2 觸發(fā)器的用途觸發(fā)器的用途 l5.2.3 觸發(fā)器與存儲過程的比較觸發(fā)器與存儲過程的比較 l5.2.4 觸發(fā)器的使用方法觸發(fā)器的使用方法 5.2.1 觸發(fā)器的基本概念觸發(fā)器的基本概念 l觸發(fā)器可以看作是一類特殊的存儲過程，它在 滿足某個特定條件時自動觸發(fā)執(zhí)行。 l觸發(fā)器是為表上的更新、插入、刪除操作定義 的，也就是說當(dāng)表上發(fā)生更新、插入或刪除操 作時觸發(fā)器將執(zhí)行。 l因此，相應(yīng)的，觸發(fā)器根據(jù)觸發(fā)類型分為 insert，delete，update觸發(fā)器。 5.2.

17、2 觸發(fā)器的用途觸發(fā)器的用途 l觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由主鍵和外 鍵所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一 致性。 l除此之外，觸發(fā)器還有以下的功能： l觸發(fā)器可以通過級聯(lián)的方式對相關(guān)的表進(jìn)行修改。 比如，對父表的修改，可以引起對子孫表的一系列 修改，從而保證數(shù)據(jù)的一致性和完整性。 l觸發(fā)器可以禁止或撤消違反參照完整性的修改。 l觸發(fā)器可以強(qiáng)制比用CHECK約束定義更加復(fù)雜的 限制。 5.2.3 觸發(fā)器與存儲過程的比較觸發(fā)器與存儲過程的比較 l聯(lián)系： l1、觸發(fā)器也是存儲過程。 l2、它們都是提高數(shù)據(jù)庫服務(wù)器性能的工具。 l 區(qū)別： l1、執(zhí)行方法不同。觸發(fā)器主要是通過事件進(jìn)行觸 發(fā)而

18、被執(zhí)行的，存儲過程可以通過存儲過程名字而 被直接調(diào)用。 l2、建立方法不同。 觸發(fā)器是依附于表的數(shù)據(jù)庫對象觸發(fā)器是依附于表的數(shù)據(jù)庫對象 lCREATE TRIGGER 語句必須是批處理的第一個語句語句必須是批處理的第一個語句 l表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限，表的所有者不能表的所有者具有創(chuàng)建觸發(fā)器的缺省權(quán)限，表的所有者不能 把該權(quán)限傳給其它用戶。把該權(quán)限傳給其它用戶。 l觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合命名規(guī)則。觸發(fā)器是數(shù)據(jù)庫對象，所以其命名必須符合命名規(guī)則。 l盡管在觸發(fā)器的盡管在觸發(fā)器的SQL 語句中可以參照其它數(shù)據(jù)庫中的對語句中可以參照其它數(shù)據(jù)庫中的對 象，但是觸發(fā)器只能創(chuàng)建在

19、當(dāng)前數(shù)據(jù)庫中。象，但是觸發(fā)器只能創(chuàng)建在當(dāng)前數(shù)據(jù)庫中。 l雖然觸發(fā)器可以參照視圖或臨時表，但不能在視圖或臨時雖然觸發(fā)器可以參照視圖或臨時表，但不能在視圖或臨時 表上創(chuàng)建觸發(fā)器，而只能在基表或在創(chuàng)建視圖的表上創(chuàng)建表上創(chuàng)建觸發(fā)器，而只能在基表或在創(chuàng)建視圖的表上創(chuàng)建 觸發(fā)器。觸發(fā)器。 l一個觸發(fā)器只能對應(yīng)一個表，這是由觸發(fā)器的機(jī)制決定的一個觸發(fā)器只能對應(yīng)一個表，這是由觸發(fā)器的機(jī)制決定的。 觸發(fā)器是依附于表的數(shù)據(jù)庫對象觸發(fā)器是依附于表的數(shù)據(jù)庫對象 l一個觸發(fā)器和三部分內(nèi)容有關(guān)：一個觸發(fā)器和三部分內(nèi)容有關(guān)： l定義觸發(fā)器的表定義觸發(fā)器的表 l激活觸發(fā)器的數(shù)據(jù)操作語句激活觸發(fā)器的數(shù)據(jù)操作語句 l觸發(fā)器要采取

20、的動作觸發(fā)器要采取的動作 不能在觸發(fā)器中使用的不能在觸發(fā)器中使用的sql語句語句 lCreate database和create table l所有drop語句 l數(shù)據(jù)庫修改語句alter table,alter database l對象權(quán)限語句grant和revoke 5.2.4 觸發(fā)器的使用方法觸發(fā)器的使用方法 l創(chuàng)建觸發(fā)器的語句 l觸發(fā)器的使用原理-理解兩個視圖并掌握它們 的使用 l插入類觸發(fā)器 l刪除類觸發(fā)器 l更新類觸發(fā)器 l觸發(fā)器的相關(guān)操作 l修改刪除觸發(fā)器 建立觸發(fā)器的語句建立觸發(fā)器的語句 CREATE TRIGGER trigger_name ON table FOR INSE

21、RT | UPDATE | DELETE AS IF UPDATE(column) AND | OR UPDATE(column) sql_statement s trigger_name：給出了觸發(fā)器的名稱； s table：說明了定義觸發(fā)器的表或視圖； s FOR INSERT | UPDATE | DELETE ：說明了激活觸發(fā)器的數(shù)據(jù) 操作語句； s IF UPDATE(column)：對應(yīng)于UPDATE類觸發(fā)器，說明如果更新 某（些）列則做如何處理； s sql_statement：觸發(fā)器所要執(zhí)行的SQL語句，它可以是一組SQL 語句，可以包含流程控制語句等。 例：建立一個簡單的觸發(fā)

22、器。例：建立一個簡單的觸發(fā)器。 CREATE TRIGGER wh_trigger ON 倉庫 FOR INSERT AS PRINT 插入了一個倉庫元組 說明：當(dāng)在說明：當(dāng)在“倉庫倉庫”表中插入一行元組，則輸出表中插入一行元組，則輸出“插入了一個倉庫元組插入了一個倉庫元組”。 觸發(fā)器的原理觸發(fā)器的原理 兩個特殊的視圖兩個特殊的視圖 l每個觸發(fā)器有兩個特殊的視圖：每個觸發(fā)器有兩個特殊的視圖：插入視圖（插入視圖（inserted） 和刪除視圖（和刪除視圖（deleted） 。 l它們是邏輯表且是由系統(tǒng)管理的，存儲在內(nèi)存中，不允許用它們是邏輯表且是由系統(tǒng)管理的，存儲在內(nèi)存中，不允許用 戶直接對其修

23、改，結(jié)構(gòu)與原表有相同的表結(jié)構(gòu)。戶直接對其修改，結(jié)構(gòu)與原表有相同的表結(jié)構(gòu)。 l當(dāng)觸發(fā)器工作完成，這兩個視圖也被刪除。當(dāng)觸發(fā)器工作完成，這兩個視圖也被刪除。 l它們主要保存因用戶操作（存放剛插入的新記錄和存放剛刪它們主要保存因用戶操作（存放剛插入的新記錄和存放剛刪 除的舊記錄）而被影響到的原數(shù)據(jù)值或新數(shù)據(jù)值。除的舊記錄）而被影響到的原數(shù)據(jù)值或新數(shù)據(jù)值。 l它們是只讀的，即用戶不能向這兩個表寫入內(nèi)容它們是只讀的，即用戶不能向這兩個表寫入內(nèi)容,但可以引但可以引 用表中的數(shù)據(jù)。用表中的數(shù)據(jù)。 觸發(fā)器的原理觸發(fā)器的原理 這兩個視圖與數(shù)據(jù)操作的關(guān)系這兩個視圖與數(shù)據(jù)操作的關(guān)系 l一旦對表執(zhí)行了插入操作，一旦對

24、表執(zhí)行了插入操作，插入視圖插入視圖就是用來存就是用來存 儲向原表插入的內(nèi)容。儲向原表插入的內(nèi)容。 l一旦對表執(zhí)行了刪除操作，則將所有的刪除行存一旦對表執(zhí)行了刪除操作，則將所有的刪除行存 放至放至刪除視圖刪除視圖中。中。 l更新操作包括兩個部分即先將更新的內(nèi)容去掉然更新操作包括兩個部分即先將更新的內(nèi)容去掉然 后將新值插入，因此對一個定義了更新類型觸發(fā)后將新值插入，因此對一個定義了更新類型觸發(fā) 器的表來講，在器的表來講，在刪除視圖刪除視圖中存放了舊值，然后在中存放了舊值，然后在 插入視圖插入視圖中存放新值。中存放新值。 插入類觸發(fā)器插入類觸發(fā)器 插入類觸發(fā)器就是當(dāng)表上發(fā)生插入 操作時所觸發(fā)執(zhí)行的程

25、序。 例：例：對職工表的插入操作定義一個觸發(fā)器，使得當(dāng)插入職對職工表的插入操作定義一個觸發(fā)器，使得當(dāng)插入職 工記錄時，檢查相應(yīng)的倉庫元組是否存在，如果不存在則工記錄時，檢查相應(yīng)的倉庫元組是否存在，如果不存在則 撤消所做的插入操作。撤消所做的插入操作。 CREATE TRIGGER e_ins_trigger ON 職工 FOR INSERT AS 如果如果 插入的職工元組的倉庫號在倉庫表中存插入的職工元組的倉庫號在倉庫表中存 在在 ,那么插入成功，職工表增加一行元組。那么插入成功，職工表增加一行元組。 如果如果 插入的職工元組的倉庫號在倉庫表中不插入的職工元組的倉庫號在倉庫表中不 存在存在,則

26、插入操作不成功，給出相應(yīng)的提示，并則插入操作不成功，給出相應(yīng)的提示，并 且事務(wù)回滾到插入操作之前。且事務(wù)回滾到插入操作之前。 IF (SELECT COUNT(*) FROM 倉庫 w , inserted i WHERE w.倉庫號 = i.倉庫號) = 0 BEGIN RAISERROR (非法倉庫號！,1,1) ROLLBACK TRANSACTION END 思考：思考：如果上題如果上題要求改為在職工表的插入操作定義一個觸要求改為在職工表的插入操作定義一個觸 發(fā)器，使得當(dāng)插入職工記錄時，檢查實(shí)體完整性，如果不發(fā)器，使得當(dāng)插入職工記錄時，檢查實(shí)體完整性，如果不 滿足實(shí)體完整性則撤消所做的

27、插入操作。滿足實(shí)體完整性則撤消所做的插入操作。 CREATE TRIGGER e_ins_trigger2 ON 職工 FOR INSERT AS 如果如果 插入的職工元組的職工號在原職工表中插入的職工元組的職工號在原職工表中 不存在不存在,那么插入成功，職工表增加一行元組。那么插入成功，職工表增加一行元組。 如果如果 插入的職工元組的職工號在插入的職工元組的職工號在原職工表原職工表中中 已存在已存在,則插入操作不成功，給出相應(yīng)的提示，則插入操作不成功，給出相應(yīng)的提示， 并且事務(wù)回滾到插入操作之前。并且事務(wù)回滾到插入操作之前。 IF (SELECT COUNT(*) FROM 職工 w , i

28、nserted i WHERE w.職工號 = i.職工號) 0 BEGIN RAISERROR (重復(fù)的職工號！,1,1) ROLLBACK TRANSACTION END 刪除類觸發(fā)器刪除類觸發(fā)器 刪除類觸發(fā)器就是當(dāng)表上發(fā)生刪除操作時 所觸發(fā)執(zhí)行的程序。 例：定義一個觸發(fā)器，例：定義一個觸發(fā)器，使得當(dāng)刪除倉庫記錄時，同時使得當(dāng)刪除倉庫記錄時，同時 將所屬所有職工記錄的倉庫號字段值置為空值將所屬所有職工記錄的倉庫號字段值置為空值NULL： CREATE TRIGGER w_del_trigger ON 倉庫 FOR DELETE AS UPDATE 職工 SET 倉庫號=NULL WHERE

29、 倉庫號 = (SELECT 倉庫號 FROM deleted) 思考：若改為定義一個觸發(fā)器，思考：若改為定義一個觸發(fā)器，使得當(dāng)刪除倉使得當(dāng)刪除倉 庫記錄時，同時將所屬所有職工記錄刪除：庫記錄時，同時將所屬所有職工記錄刪除： CREATE TRIGGER w_del_trigger2 ON 倉庫 FOR DELETE AS DELETE FROM 職工 FROM deleted WHERE 職工.倉庫號 = deleted.倉庫號 更新類觸發(fā)器更新類觸發(fā)器 更新類觸發(fā)器就是當(dāng)表上發(fā)生更新操作時 所觸發(fā)執(zhí)行的程序。 例：例：對職工表的更新操作定義一個觸發(fā)器，使得當(dāng)職工對職工表的更新操作定義一個觸

30、發(fā)器，使得當(dāng)職工 變換所屬倉庫時，檢查相應(yīng)的倉庫元組是否存在，如果變換所屬倉庫時，檢查相應(yīng)的倉庫元組是否存在，如果 不存在則撤消所做的更新操作，如果新的倉庫號是不存在則撤消所做的更新操作，如果新的倉庫號是WH2 則將工資提高則將工資提高10%。 定義語句（注意表名，哪一類的觸發(fā)器）定義語句（注意表名，哪一類的觸發(fā)器） 當(dāng)改變職工所屬的倉庫號時（倉庫號當(dāng)改變職工所屬的倉庫號時（倉庫號a-倉庫號倉庫號 b，不確定，怎么寫語句？），不確定，怎么寫語句？） 如果倉庫號如果倉庫號b在倉庫表中不存在，事務(wù)回滾，撤在倉庫表中不存在，事務(wù)回滾，撤 銷更新職工表的倉庫號字段，維持原來的元組。銷更新職工表的倉庫號

31、字段，維持原來的元組。 如果倉庫號如果倉庫號b在倉庫表中存在，則進(jìn)行下面的工在倉庫表中存在，則進(jìn)行下面的工 作作 如果更新的倉庫號如果更新的倉庫號b為為WH2，則給這個職工，則給這個職工 增加增加10%工資工資 如果更新的倉庫號如果更新的倉庫號b不是不是WH2，而是其它合，而是其它合 法的倉庫號，則只是修改該職工所屬的倉庫法的倉庫號，則只是修改該職工所屬的倉庫 號（即用戶的更新操作成功）號（即用戶的更新操作成功） WH2 1331 WH5 CREATE TRIGGER e_upd_trigger ON 職工 FOR UPDATE AS DECLARE wh_no CHAR(4) IF UPDA

32、TE(倉庫號) BEGIN IF (SELECT COUNT(*) FROM 倉庫 w , inserted i WHERE w.倉庫號 = i.倉庫號) = 0 BEGIN RAISERROR (非法倉庫號！, 16, 1) ROLLBACK TRANSACTION END ELSE BEGIN SELECT wh_no = 倉庫號 FROM inserted IF wh_no = WH2 UPDATE 職工 SET 工資 = 工資*1.10 WHERE 職工號 = (SELECT 職工號 FROM inserted) END END 定義語句定義語句 當(dāng)更新操作，生成兩張視圖當(dāng)更新操作，生

33、成兩張視圖 如果倉庫號如果倉庫號b在倉庫表中不在倉庫表中不 存在，不符合參照完整性，存在，不符合參照完整性， 事務(wù)回滾。事務(wù)回滾。 如果倉庫號如果倉庫號b在倉庫表中存在，在倉庫表中存在， 且為且為WH2則增加工資則增加工資 使用系統(tǒng)存儲過程查看觸發(fā)器使用系統(tǒng)存儲過程查看觸發(fā)器 l系統(tǒng)存儲過程sp_help， sp_helptext 和 sp_depends 分別提供有關(guān)觸發(fā)器的不同信息。 lsp_help，通過該系統(tǒng)過程可以了解觸發(fā)器的 一般信息如觸發(fā)器的名字屬性類型創(chuàng)建時間 l使用sp_help 系統(tǒng)過程的命令格式是 sp_help 觸發(fā)器名字 lsp_helptext 通過sp_helpt

34、ext 能夠查看觸發(fā)器的正文信息，其語法 格式為 sp_helptext 觸發(fā)器名 lsp_depends 通過sp_depends 能夠查看指定觸發(fā)器所引用的表或指 定的表涉及到的所有觸發(fā)器，其語法形式如下 sp_depends 觸發(fā)器名字 sp_depends 表名 修改刪除觸發(fā)器修改刪除觸發(fā)器 l可以修改觸發(fā)器的名字和正文 l使用sp_rename 命令修改觸發(fā)器的名字，其語法格式為 sp_rename oldname,newname l用Alert trigger 命令修改觸發(fā)器正文 l刪除已創(chuàng)建的觸發(fā)器有兩種方法： l用系統(tǒng)命令DROP TRIGGER 刪除指定的觸發(fā)器，其語法形式

35、如下 DROP TRIGGER 觸發(fā)器名字 l刪除觸發(fā)器所在的表時， 將自動刪除與該表相關(guān)的觸發(fā)器。 5.3 數(shù)據(jù)完整性數(shù)據(jù)完整性 l在第3章已經(jīng)介紹了在關(guān)系數(shù)據(jù)模型上數(shù) 據(jù)完整性的概念和規(guī)則；在前一章介紹了 CREATE TABLE語句中可以實(shí)現(xiàn)的一些 完整性約束。這里介紹與數(shù)據(jù)完整性有關(guān) 的 其 他 一 些 內(nèi) 容 。 . 5.3.1 規(guī)則規(guī)則 l在在CREATE TABLE語句中可以使用語句中可以使用CHECK子句實(shí)子句實(shí) 現(xiàn)一些用戶定義完整性或域完整性約束。另外還可以現(xiàn)一些用戶定義完整性或域完整性約束。另外還可以 通過通過“規(guī)則規(guī)則”（RULE）來實(shí)現(xiàn)用戶定義完整性或域）來實(shí)現(xiàn)用戶定義

36、完整性或域 完整性。完整性。 lCHECK約束固定在一個表的一個列上，它只在指定約束固定在一個表的一個列上，它只在指定 的列上起作用。如果在不同的列上有相同的約束條件，的列上起作用。如果在不同的列上有相同的約束條件， 則可以使用規(guī)則，一個規(guī)則可以綁定在多個列上。則可以使用規(guī)則，一個規(guī)則可以綁定在多個列上。 l規(guī)則是一種獨(dú)立的數(shù)據(jù)庫對象，它可以綁定到一個列規(guī)則是一種獨(dú)立的數(shù)據(jù)庫對象，它可以綁定到一個列 上來約束該列的取值范圍等。上來約束該列的取值范圍等。 規(guī)則的用法規(guī)則的用法 l定義規(guī)則 l綁定到相應(yīng)的列上 建立規(guī)則的命令是：建立規(guī)則的命令是： CREATE RULE rule AS condi

37、tion_expression lrule：給出新建規(guī)則的名稱； lcondition_expression：定義規(guī)則的條件，可以是任何 有效的表達(dá)式，并且可以包含諸如算術(shù)運(yùn)算符、關(guān)系 運(yùn)算符和謂詞（如IN、LIKE、BETWEEN）之類的元 素。 注意： l規(guī)則不能引用列或其它數(shù)據(jù)庫對象，規(guī)則可以包含不引用 數(shù)據(jù)庫對象的內(nèi)置函數(shù)； lcondition_expression需要包含一個變量，變量的前面有一 個前綴； l該表達(dá)式引用通過 UPDATE或INSERT語句輸入或傳遞的 字段值。 例如，規(guī)定某類數(shù)值對象的取值范圍是例如，規(guī)定某類數(shù)值對象的取值范圍是 10003000，則可以定義規(guī)則：

38、，則可以定義規(guī)則： CREATE RULE range_rule AS range=1000 AND range=3000 規(guī)則的綁定規(guī)則的綁定 l規(guī)則是獨(dú)立的數(shù)據(jù)庫對象，要通過系統(tǒng)存儲過程 sp_bindrule把規(guī)則綁定到數(shù)據(jù)列上，該系統(tǒng)存儲過程 的格式是： sp_bindrule rulename , objname , futureonly lrulename是用CREATE RULE命令建立的規(guī)則名； lobjname指出要綁定的表和列或用戶定義的數(shù)據(jù)類型； lfutureonly，當(dāng)綁定規(guī)則到用戶定義的數(shù)據(jù)類型時可以選用此 項(xiàng)，該選項(xiàng)是禁止已經(jīng)存在的、用用戶定義數(shù)據(jù)類型定義的 列遵

39、循新的規(guī)則。 例如，將規(guī)則例如，將規(guī)則range_rule綁定到職工表的工資列上綁定到職工表的工資列上 sp_bindrule range_rule,職工.工資 l使用CREATE RULE命令創(chuàng)建的規(guī)則對象，可以綁定到多 個數(shù)據(jù)列上，即一個規(guī)則可以反復(fù)使用。 綁定的消除和規(guī)則的刪除綁定的消除和規(guī)則的刪除 l綁定到數(shù)據(jù)列上的規(guī)則可以去除，相應(yīng)的系統(tǒng) 存儲過程是sp_unbindrule。例如，取消綁定在 職工表工資列上的規(guī)則可以使用如下語句： sp_unbindrule 職工.工資 l規(guī)則可以刪除，刪除規(guī)則的命令是DROP RULE，但是刪除規(guī)則之前，必須首先解除所 有的綁定。 5.3.2 默

40、認(rèn)值默認(rèn)值 l在CREATE TABLE命令中可以使用DEFAULT約 束為數(shù)據(jù)列定義默認(rèn)值。這里介紹另外一種方法： 使用CREATE DEFAULT命令創(chuàng)建默認(rèn)值對象。 CREATE DEFAULT命令的格式命令的格式 CREATE DEFAULT default AS constant_expression l default：是建立的默認(rèn)值對象名； l constant_expression：定義默認(rèn)值的常量表達(dá)式。 例如，定義一個值為例如，定義一個值為“北京北京”的默認(rèn)值對象的默認(rèn)值對象val_bj CREATE DEFAULT val_bj AS 北京 綁定默認(rèn)值綁定默認(rèn)值 l默認(rèn)值

41、是獨(dú)立的數(shù)據(jù)庫對象，它要作用于某個數(shù)據(jù)對 象，則也和綁定規(guī)則一樣，需要用類似的系統(tǒng)存儲過 程把默認(rèn)值綁定到列，綁定默認(rèn)值的系統(tǒng)存儲過程是 sp_bindefault，具體格式是： sp_bindefault defname , objname , futureonly ldefname：是用CREATE DEFAULT命令建立的默認(rèn)值 對象名； lobjname：指出要綁定的表和列或用戶定義的數(shù)據(jù)類 型； lfutureonly，當(dāng)綁定默認(rèn)值到用戶定義的數(shù)據(jù)類型時可 以選用此項(xiàng)，該選項(xiàng)是禁止已經(jīng)存在的、用用戶定義 數(shù)據(jù)類型定義的列遵循新的默認(rèn)值約定。 例如，將定義的默認(rèn)值對象例如，將定義的默認(rèn)值對象val_bj綁定到倉庫關(guān)系綁定到倉庫關(guān)系 的城市列上和供應(yīng)商關(guān)系的地址列上：的城市列上和供應(yīng)商關(guān)系的