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

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

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

4、”|“,觸發器”,節點，單擊鼠標右鍵，在出現的快捷菜單中選擇“新建觸發器”,命令。,（,3,）出現一個新建觸發器編輯窗口，其中包含觸發器模板，,用戶可以參照模板在其中輸入觸發器的,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、）單擊工具欄中的“執行”按鈕，將該觸發器保存到,相關的系統表中。這樣就創建了觸發器,trigop,。,在觸發器,trigop,創建完畢，當對,student,表進行記錄插入、修改,或刪除操作時，觸發器,trigop,都會被自動執行。例如，執行以,下程序：,USE school,INSERT student V,ALUES(1,劉明,男,1991-12-12,1035),GO,當向,student,表中插入一個記錄時自動執行觸發器,trigop,輸,出其所有記錄，輸出結果如圖,15.1,所示，從中看到新記錄已經,插入到,student,表中了。,15.2.2,使用,T-SQL,語句創建,DML,

6、觸發器,創建,DML,觸發器可以使用,CREATE TRIGGER,語句，其基,本語法格式如下：,CREATE TRIGGER,觸發器名稱,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,】,在數據庫,test,中建立一個表,table10,，創建一個,觸發器,trigtest,，在,table10,表中插入、修改和刪除記錄時，自動,顯示表中的所有記錄。并用相關數據進行測試。,解：,創建表和觸發器的語句如下：,USE test,GO,CREATE TABLE table10,-,創建表,table10,(,c1 int,c2 char(30),),GO,CREATE TRIGGER trigtest,-,創建觸發器,trigtest,ON table10 AFTER INSERT,UPDA

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

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

10、行被插,入到,inserted,表中。,?,執行,DELETE,操作，從觸發器表中刪除的行被插入,到,deleted,表中。,?,執行,UPDATE,操作，先從觸發器表中刪除舊行，然,后再插入新行。其中被刪除的舊行被插入到,deleted,表中，插入的新行被插入到,inserted,表中。,【例,15.3,】,編寫一段,T-SQL,語句說明,inserted,表和,deleted,表的作用。,解：,創建觸發器,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,觸發器，則刪除之,GO,CREATE TRIGGER trigtest,-,創建觸發器,trigtest,ON table10 AFTER INSERT,UPDATE,DELETE,AS,PRINT inserted,表,:,SELECT * FROM inserted,PRINT deleted,表,:

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

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

14、：,USE school,GO,CREATE TRIGGER trigname,-,創建,trigname,觸發器,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,-,事務回滾,END,END,執行以下程序：,USE school,INSERT INTO student(,學號

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

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

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

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

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

20、,出生日期,sclass char(10),-,班號,),GO,CREATE TRIGGER trigcopy,-,創建觸發器,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,執行以下程序：,USE school,-,修改班號,UPDATE student,SET,班號,=1131,WHERE,班號,=1031,GO,-,恢復班號,UPDATE student,SET,班號,=1031,WHERE,班號,=1131,GO,執行上述程序，兩次修改,student,表中的班號，,student,表中,的記錄恢復成修改前的狀態，而,stbak,表中的記錄如圖,15.11,所,示，,15.4.3,使用,DELETE,觸發器,當執行,DELETE,觸發器時，表中原來的記錄被移動到,deleted,表中。,DELETE,觸發器通常用于

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

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

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

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

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

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

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

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