數(shù)據(jù)庫原理與應(yīng)用(15)第15章觸發(fā)器

1、第,15,章,觸發(fā)器,15.1,概,述,15.1.1,觸發(fā)器的概念,觸發(fā)器是一種特殊類型的存儲過程，它在插入、刪除或,修改指定表中的數(shù)據(jù)時觸發(fā)執(zhí)行。觸發(fā)器通?？梢詮娭茍?zhí)行,一定的業(yè)務(wù)規(guī)則，以保持數(shù)據(jù)完整性、檢查數(shù)據(jù)有效性、實,現(xiàn)數(shù)據(jù)庫管理任務(wù)和一些附加的功能。,觸發(fā)器具有如下優(yōu)點：,?,觸發(fā)器是被自動執(zhí)行的，不需要顯式調(diào)用。,?,觸發(fā)器可以調(diào)用存儲過程。,?,觸發(fā)器可以強化數(shù)據(jù)條件約束。,?,觸發(fā)器可以禁止或回滾違反引用完整性的數(shù)據(jù),修改或刪除。,?,利用觸發(fā)器可以進行數(shù)據(jù)處理。,?,觸發(fā)器可以級聯(lián)、并行執(zhí)行。,?,在同一個表中可以設(shè)計多個觸發(fā)器。,15.1.2,觸發(fā)器的種類,SQL Serv

2、er 2005,提供了兩種類型的觸發(fā)器：,?,DML,觸發(fā)器,：在執(zhí)行數(shù)據(jù)操作語言事件時被調(diào)用的觸發(fā),器，其中數(shù)據(jù)操作語言事件包括：,INSERT,、,UPDATE,和,DELETE,語句。觸發(fā)器中可以包含復雜的,T-SQL,語句，,觸發(fā)器整體被看作一個事務(wù)，可以進行回滾。,?,DDL,觸發(fā)器,：與,DML,觸發(fā)器類似，它由相應(yīng)的事件觸發(fā),后執(zhí)行。與,DML,不同的是，它的觸發(fā)事件是由數(shù)據(jù)定義,語言引起的事件，包括,CREATE,、,ALTER,和,DROP,語句。,DDL,觸發(fā)器用于執(zhí)行數(shù)據(jù)庫管理任務(wù)，如調(diào)節(jié)和審計數(shù),據(jù)庫運轉(zhuǎn)。,DDL,觸發(fā)器只能在觸發(fā)事件發(fā)生后才會調(diào)用,執(zhí)行，即它只能是,

3、AFTER,類型的。,15.2,創(chuàng)建,DML,觸發(fā)器,15.2.1,使用,SQL Server,管理控制器創(chuàng)建,DML,觸發(fā)器,通過一個簡單的示例說明使用,SQL Server,管理控制器創(chuàng)建,觸發(fā)器的操作步驟。,【例,15.1,】,使用,SQL Server,管理控制器在,student,表上創(chuàng)建,一個觸發(fā)器,trigop,，其功能是在用戶插入、修改或刪除該表中,行中輸出所有的行。,解：,其操作步驟如下：,（,1,）啟動,SQL Server,管理控制器，在“對象資源管理器”,中展開“,LCB-,PC”,服務(wù)器節(jié)點。,（,2,）展開“數(shù)據(jù)庫”,|“school”|“,表”,|“student

4、”|“,觸發(fā)器”,節(jié)點，單擊鼠標右鍵，在出現(xiàn)的快捷菜單中選擇“新建觸發(fā)器”,命令。,（,3,）出現(xiàn)一個新建觸發(fā)器編輯窗口，其中包含觸發(fā)器模板，,用戶可以參照模板在其中輸入觸發(fā)器的,T-SQL,語句，這里輸入,的語句如下（其中紅字部分為主要輸入的,T-SQL,語句）：,SET ANSI_NULLS ON,GO,SET QUOTED_IDENTIFIER ON,GO,CREATE TRIGGER trigop,ON student AFTER INSERT,DELETE,UPDATE,AS,BEGIN,SET NOCOUNT ON,SELECT * FROM student,END,GO,（,4,

5、）單擊工具欄中的“執(zhí)行”按鈕，將該觸發(fā)器保存到,相關(guān)的系統(tǒng)表中。這樣就創(chuàng)建了觸發(fā)器,trigop,。,在觸發(fā)器,trigop,創(chuàng)建完畢，當對,student,表進行記錄插入、修改,或刪除操作時，觸發(fā)器,trigop,都會被自動執(zhí)行。例如，執(zhí)行以,下程序：,USE school,INSERT student V,ALUES(1,劉明,男,1991-12-12,1035),GO,當向,student,表中插入一個記錄時自動執(zhí)行觸發(fā)器,trigop,輸,出其所有記錄，輸出結(jié)果如圖,15.1,所示，從中看到新記錄已經(jīng),插入到,student,表中了。,15.2.2,使用,T-SQL,語句創(chuàng)建,DML,

6、觸發(fā)器,創(chuàng)建,DML,觸發(fā)器可以使用,CREATE TRIGGER,語句，其基,本語法格式如下：,CREATE TRIGGER,觸發(fā)器名稱,ON ,表名,|,視圖名,WITH ENCRYPTION, FOR | AFTER | INSTEAD OF INSERT , UPDATE,WITH APPEND,NOT FOR REPLICATION,AS, IF UPDATE ( column ), AND | OR UPDATE ( column ),n,| IF (COLUMNS_UPDATED() bitwise_operator updated_bitmask ), comparison_o

7、perator column_bitmask n, ,SQL,語句,n ,【例,15.2,】,在數(shù)據(jù)庫,test,中建立一個表,table10,，創(chuàng)建一個,觸發(fā)器,trigtest,，在,table10,表中插入、修改和刪除記錄時，自動,顯示表中的所有記錄。并用相關(guān)數(shù)據(jù)進行測試。,解：,創(chuàng)建表和觸發(fā)器的語句如下：,USE test,GO,CREATE TABLE table10,-,創(chuàng)建表,table10,(,c1 int,c2 char(30),),GO,CREATE TRIGGER trigtest,-,創(chuàng)建觸發(fā)器,trigtest,ON table10 AFTER INSERT,UPDA

8、TE,DELETE,AS,SELECT * FROM table10,GO,在執(zhí)行下面的語句時：,USE test,INSERT Table10 V,ALUES(1,Name1),GO,結(jié)果會顯示出,table10,表中的行如圖,15.2,所示。,在執(zhí)行下面的語句時：,USE test,UPDATE Table10 SET c2=Name2 WHERE c1=1,GO,結(jié)果會顯示出,table10,表中的記錄行如圖,15.3,所示。,15.3 inserted,表和,deleted,表,在觸發(fā)器執(zhí)行的時候，會產(chǎn)生兩個臨時表：,inserted,表和,deleted,表。它們的結(jié)構(gòu)和觸發(fā)器所在的

9、表的結(jié)構(gòu)相同。,deleted,表用于存儲,DELETE,和,UPDATE,語句所影響的行,的副本。在執(zhí)行,DELETE,或,UPDATE,語句時，行從觸發(fā)器表,中刪除，并傳輸?shù)?deleted,表中。,deleted,表和觸發(fā)器表通常沒,有相同的行。,inserted,表用于存儲,INSERT,和,UPDATE,語句所影響的行,的副本。在一個插入或更新事務(wù)處理中，新建行被同時添加,到,inserted,表和觸發(fā)器表中。,inserted,表中的行是觸發(fā)器表中,新行的副本。,在對具有觸發(fā)器的表（簡稱為觸發(fā)器表）進行操作時，,其操作過程如下：,?,執(zhí)行,INSERT,操作，插入到觸發(fā)器的表中的新

10、行被插,入到,inserted,表中。,?,執(zhí)行,DELETE,操作，從觸發(fā)器表中刪除的行被插入,到,deleted,表中。,?,執(zhí)行,UPDATE,操作，先從觸發(fā)器表中刪除舊行，然,后再插入新行。其中被刪除的舊行被插入到,deleted,表中，插入的新行被插入到,inserted,表中。,【例,15.3,】,編寫一段,T-SQL,語句說明,inserted,表和,deleted,表的作用。,解：,創(chuàng)建觸發(fā)器,trigtest,的語句如下：,USE test,GO,IF EXISTS(SELECT * FROM sysobjects WHERE name=table10 AND,type=U)

11、,DELETE table10,-,若存在,table10,表，則刪除其記錄,GO,IF EXISTS(SELECT * FROM sysobjects WHERE name=trigtest AND,type=TR),DROP TRIGGER trigtest,-,若存在,trigtest,觸發(fā)器，則刪除之,GO,CREATE TRIGGER trigtest,-,創(chuàng)建觸發(fā)器,trigtest,ON table10 AFTER INSERT,UPDATE,DELETE,AS,PRINT inserted,表,:,SELECT * FROM inserted,PRINT deleted,表,:

12、,SELECT * FROM deleted,GO,如果此時執(zhí)行下面的,INSERT,語句：,USE test,INSERT table10 V,ALUES(2,Name3),GO,其執(zhí)行結(jié)果如圖,15.4,所示。,如果此時接著執(zhí)行下面的,UPDATE,語句：,USE test,UPDATE table10 SET c2=Name4 WHERE c1=2,GO,其執(zhí)行結(jié)果如圖,15.5,所示。,如果此時接著執(zhí)行下面的,DELETE,語句：,USE test,DELETE table10 WHERE c1=2,GO,其執(zhí)行結(jié)果如如圖,15.6,所示。,15.4,使用,DML,觸發(fā)器,15.4.1

13、,使用,INSERT,觸發(fā)器,INSERT,觸發(fā)器通常被用來更新時間標記字段，或者驗證,被觸發(fā)器監(jiān)控的字段中數(shù)據(jù)滿足要求的標準，以確保數(shù)據(jù)的完,整性。當向數(shù)據(jù)庫中插入數(shù)據(jù)時，,INSERT,觸發(fā)器將被觸發(fā)執(zhí),行。,INSERT,觸發(fā)器被觸發(fā)時，新的記錄增加到觸發(fā)器的對應(yīng),表中，并且同時也添加到一個,inserted,表中。該,inserted,表是一,個邏輯表，以確定該觸發(fā)器的操作是否應(yīng)該執(zhí)行，以及如何去,執(zhí)行。,【例,15.4,】,建立一個觸發(fā)器,trigname,，當向,student,表中插,入數(shù)據(jù)時，如果出現(xiàn)姓名重復的情況，則回滾該事務(wù)。,解：,創(chuàng)建觸發(fā)器,trigname,的程序如下

14、：,USE school,GO,CREATE TRIGGER trigname,-,創(chuàng)建,trigname,觸發(fā)器,ON student AFTER INSERT,AS,BEGIN,DECLARE name char(10),SELECT name=inserted.,姓名,FROM inserted,IF EXISTS(SELECT,姓名,FROM student WHERE,姓名,=name),BEGIN,RAISERROR(,姓名重復,不能插入,16,1),ROLLBACK,-,事務(wù)回滾,END,END,執(zhí)行以下程序：,USE school,INSERT INTO student(,學號

15、,姓名,性別,) V,ALUES(102,王麗,女,),GO,出現(xiàn)如圖,15.7,所示的消息，提示插入的記錄出錯。,再打開,student,表，從中看到，由于進行了事務(wù)回滾，所,以并不會真正向,student,表中插入學號為,102,的新記錄。,【例,15.5,】,建立一個觸發(fā)器,trigsex,，當向,student,表中插入,數(shù)據(jù)時，如果出現(xiàn)性別不正確的情況，不回滾該事務(wù)，只提示,錯誤消息。,解：,創(chuàng)建觸發(fā)器,trignsex,的程序如下：,USE school,GO,CREATE TRIGGER trigsex,-,創(chuàng)建,trigsex,觸發(fā)器,ON student AFTER INSE

16、RT,AS,DECLARE s1 char(1),SELECT s1=,性別,FROM INSERTED,IF s1,男, OR s1,女,RAISERROR(,性別只能取男或女,16,1),-,發(fā)出一條錯誤消息,GO,當執(zhí)行以下程序：,USE school,INSERT student V,ALUES(2,許濤,M,1992-10-16,1035),GO,出現(xiàn)如圖,15.8,所示的消息，提示插入的記錄出錯。,再打開,student,表，從中看到，由于沒有進行事務(wù)回滾，,盡管要插入的記錄不正確，但仍然插入到,student,表中了，,其中學號為,1,的記錄是在例,15.1,中插入的。,15.4

17、.2,使用,UPDATE,觸發(fā)器,修改觸發(fā)器和插入觸發(fā)器的工作過程基本上一致，修改一,條記錄等于插入了一個新的記錄并且刪除一個舊的記錄。,當在一個有,UPDATE,觸發(fā)器的表中修改記錄時，表中原來,的記錄被移動到,deleted,表中，修改過的記錄插入到了插入表中，,觸發(fā)器可以參考,deleted,表和,inserted,表以及被修改的表，以確,定如何完成數(shù)據(jù)庫操作。,【例,15.6,】,建立一個修改觸發(fā)器,trigno,，該觸發(fā)器防止用,戶修改表,student,的學號。,解：,創(chuàng)建觸發(fā)器,trignno,的程序如下：,USE school,GO,CREATE TRIGGER trigno,

18、-,創(chuàng)建,trigno,觸發(fā)器,ON student,AFTER UPDATE,AS,IF UPDATE(,學號,),BEGIN,RAISERROR(,不能修改學號,16,2),ROLLBACK,圖,15.10,執(zhí)行觸發(fā)器,trigno,時提示的消息,END,GO,當執(zhí)行以下程序：,USE school,UPDATE student,SET,學號,=3,WHERE,學號,=1,GO,出現(xiàn)如圖,15.10,所示的消息，提示修改記錄時出錯，也并,沒有修改,student,表中學號為,1,的記錄。,【例,15.7,】,建立一個觸發(fā)器,trigcopy,，將,student,表中所有,被修改的數(shù)據(jù)保存

19、到,stbak,表中作為歷史記錄。,解：,創(chuàng)建觸發(fā)器,trigcopy,的程序如下：,USE school,GO,-,若存在,stbak,表，刪除之,否則創(chuàng)建表,stbak,IF EXISTS(SELECT name FROM sysobjects WHERE name=stbak,AND type=U),DROP TABLE stbak,CREATE TABLE stbak,-,創(chuàng)建,stbak,表,(,rq datetime,-,修改時間,sno char(10),-,學號,sname char(10),-,姓名,ssex char(2),-,性別,sbirthday datetime,-

20、,出生日期,sclass char(10),-,班號,),GO,CREATE TRIGGER trigcopy,-,創(chuàng)建觸發(fā)器,trigcopy,ON student AFTER UPDATE,AS,-,將當前日期和修改后的記錄插入到,stbak,表中,INSERT INTO stbak(rq,sno,sname,ssex,sbirthday,sclass),SELECT getdate(),inserted.,學號,inserted.,姓名,inserted.,性別,inserted.,出生日期,inserted.,班號,FROM student,inserted,WHERE student

21、.,學號,=inserted.,學號,GO,執(zhí)行以下程序：,USE school,-,修改班號,UPDATE student,SET,班號,=1131,WHERE,班號,=1031,GO,-,恢復班號,UPDATE student,SET,班號,=1031,WHERE,班號,=1131,GO,執(zhí)行上述程序，兩次修改,student,表中的班號，,student,表中,的記錄恢復成修改前的狀態(tài)，而,stbak,表中的記錄如圖,15.11,所,示，,15.4.3,使用,DELETE,觸發(fā)器,當執(zhí)行,DELETE,觸發(fā)器時，表中原來的記錄被移動到,deleted,表中。,DELETE,觸發(fā)器通常用于

22、為了防止刪除一些不能刪除的,數(shù)據(jù)和實現(xiàn)數(shù)據(jù)表級聯(lián)操作等情況。,【例,15.8,】,建立一個刪除觸發(fā)器,trigclass,，該觸發(fā)器防止,用戶刪除表,student,中所有,1031,班的學生記錄。,解：,創(chuàng)建觸發(fā)器,trigclass,的程序如下：,USE school,GO,CREATE TRIGGER trigclass,-,創(chuàng)建觸發(fā)器,trigsclass,ON student,AFTER DELETE,AS,IF EXISTS(SELECT * FROM deleted WHERE,班號,=1031),BEGIN,RAISERROR(,不能刪除,1031,班的學生記錄,16,2),R

23、OLLBACK,END,GO,執(zhí)行以下程序：,USE school,DELETE student,WHERE,班號,=1031,GO,在事務(wù)回滾，,出現(xiàn)如圖,15.12,student,所示的消息，提示修改記錄時出錯。由于存,表中的數(shù)據(jù)保持不變。,15.4.4,使用,INSTEAD OF,觸發(fā)器,執(zhí)行,INSTEAD OF,觸發(fā)器是為了替換那些初始化觸發(fā)器,的修改語句。,下面通過幾個例子說明,AFTER,觸發(fā)器和,INSTEAD OF,觸,發(fā)器的差別。,【例,15.10,】,在,teacher,表上創(chuàng)建一個,INSTEAD OF INSERT,觸發(fā)器,trigteacher,，當用戶插入數(shù)據(jù)時

24、顯示,teacher,表中所有數(shù),據(jù)。,解：,創(chuàng)建觸發(fā)器,trigteacher,的程序如下：,USE school,GO,CREATE TRIGGER trigteacher,-,創(chuàng)建觸發(fā)器,trigteacher,ON teacher INSTEAD OF INSERT,AS,SELECT * FROM teacher,GO,執(zhí)行以下程序：,USE school,INSERT INTO teacher(,編號,) V,ALUES(688),GO,出現(xiàn)如圖,15.13,所示的結(jié)果。,從結(jié)果看到，當向,teacher,表中插入記錄時，自動執(zhí)行,trigteacher,觸發(fā)器，用其中的,SELE

25、CT,語句替代該插入語句，,這樣被插入的記錄并沒有插入到,teacher,表中。這就是,INSERT,觸發(fā)器與,INSTEAD OF INSERT,觸發(fā)器的區(qū)別。,15.5,創(chuàng)建和使用,DDL,觸發(fā)器,DML,觸發(fā)器屬表級觸發(fā)器，而,DDL,觸發(fā)器屬數(shù)據(jù)庫級觸,發(fā)器。像,DML,觸發(fā)器一樣，,DDL,觸發(fā)器也是被自動執(zhí)行，但,與,DML,觸發(fā)器不同的是，它們不是響應(yīng)表或視圖的,INSERT,、,UPDATE,或,DELETE,等記錄操作語句，而是響應(yīng)數(shù)據(jù)定義語,句（,DDL,）操作，這些語句以,CREATE,、,ALTER,和,DROP,開,頭的語句。,DDL,觸發(fā)器可用于管理任務(wù)，例如，審核

26、和控制數(shù)據(jù)庫,操作。,15.5.1,創(chuàng)建,DDL,觸發(fā)器,使用,CREATE TRIGGER,命令創(chuàng)建,DDL,觸發(fā)器的基本語,法格式如下。,CREATE TRIGGER,觸發(fā)器名稱,ON ALL SERVER|DATABASE,FOR|AFTER event_type|event_group,n,AS SQL,語句,15.5.2 DDL,觸發(fā)器的應(yīng)用,在響應(yīng)當前數(shù)據(jù)庫或服務(wù)器中處理的,T-SQL,事件時，可以,激發(fā),DDL,觸發(fā)器。觸發(fā)器的作用域取決于事件。,例如，每當數(shù)據(jù)庫中發(fā)生,CREATE TABLE,事件時，都會觸,發(fā)為響應(yīng),CREATE TABLE,事件創(chuàng)建的,DDL,觸發(fā)器。每當

27、服務(wù),器中發(fā)生,CREATE LOGIN,事件時，都會觸發(fā)為響應(yīng),CREATE,LOGIN,事件創(chuàng)建的,DDL,觸發(fā)器。,【例,15.11,】,在,school,數(shù)據(jù)庫上創(chuàng)建一個,DDL,觸發(fā)器,safe,，,用來防止該數(shù)據(jù)庫中的任一表被修改或刪除。,解：,創(chuàng)建,DDL,觸發(fā)器的程序如下：,USE school,GO,CREATE TRIGGER safe,-,創(chuàng)建觸發(fā)器,safe,ON DATABASE AFTER DROP_TABLE,ALTER_TABLE,AS,BEGIN,RAISERROR(,不能修改表結(jié)構(gòu),16,2),ROLLBACK,END,GO,當執(zhí)行以下程序：,USE sch

28、ool,ALTER TABLE student ADD,民族,char(10),GO,出現(xiàn)如圖,15.14,所示的消息，提示修改,student,表結(jié)構(gòu)時出錯，,而且,student,表結(jié)構(gòu)保持不變。,15.6,觸發(fā)器的管理,15.6.1,查看觸發(fā)器,數(shù)據(jù)庫中創(chuàng)建的每個觸發(fā)器在,sys.triggers,表中對應(yīng)一個記,錄，例如，為了顯示本章前面在,school,數(shù)據(jù)庫上創(chuàng)建的觸發(fā)器，,可以使用以下程序：,其執(zhí)行結(jié)果如圖,15.16,所示，其中，,DDL,觸發(fā)器的,parent_class,列為,0,。,USE school,SELECT * FROM sys.triggers,1.,使用,S

29、QL Server,管理控制臺查看觸發(fā)器,通過一個簡單的示例說明使用,SQL Server,管理控制器查看,觸發(fā)器的操作步驟。,【例,15.13,】,使用,SQL Server,管理控制器查看,student,表上,的觸發(fā)器,trigop,（在例,15.1,中創(chuàng)建）。,解：,其操作步驟如下：,（,1,）啟動,SQL Server,管理控制器，在“對象資源管理器”,中展開“,LCB-,PC”,服務(wù)器節(jié)點。,（,2,）展開“數(shù)據(jù)庫”,|“school”|“,表”,|“student”|“,觸發(fā),器”,|“trigop”,節(jié)點，單擊鼠標右鍵，在出現(xiàn)的快捷菜單中選擇,“編寫觸發(fā)器腳本為,|CREATE,到,|,新查詢編輯器窗口”命令。,（,3,）出現(xiàn)如圖,15.17,所示的,trigop,觸發(fā)器編輯窗口，用戶,可以在其中查看,trigop,觸發(fā)器的源代碼。,2.,使用系統(tǒng)存儲過程查看觸發(fā)器,系統(tǒng)存儲過程,sp_help,、,sp_helptext,和,sp_depends,分別提,供有關(guān)觸發(fā)器的不同信息（這些系統(tǒng)存儲過程僅適合于,DML,觸發(fā)器）。,（,1,）,sp_help,用于查看觸發(fā)器