1、第第9 9章章 觸發(fā)器的操作與管理觸發(fā)器的操作與管理 觸發(fā)器概述觸發(fā)器是一種特殊的存儲過程，它在執(zhí)行語言事件時自動生效。sql server2005 包括兩大類觸發(fā)器：dml 觸發(fā)器和 ddl 觸發(fā)器。 （1）dml 觸發(fā)器在數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (dml) 事件時將啟用。dml 事件包括在指定表或視圖中修改數(shù)據(jù)的 insert 語句、update 語句或 delete 語句。dml 觸發(fā)器可以查詢其他表，還可以包含復雜的 transact-sql 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內回滾的單個事務對待。如果檢測到錯誤（例如，磁盤空間不足），則整個事務即自動回滾。 （2）ddl 觸

2、發(fā)器是 sql server 2005 的新增功能。當服務器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言 (ddl) 事件時將調用這些觸發(fā)器。 9.1 dml觸發(fā)器的創(chuàng)建和應用 當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言 (dml) 事件時將調用 dml 觸發(fā)器。從而確保對數(shù)據(jù)的處理必須符合由這些sql語句所定義的規(guī)則。 dml 觸發(fā)器的主要優(yōu)點如下：（1）dml 觸發(fā)器可通過數(shù)據(jù)庫中的相關表實現(xiàn)級聯(lián)更改。例如，可以在 titles 表的 title_id 列上寫入一個刪除觸發(fā)器，以使其他表中的各匹配行采取刪除操作。該觸發(fā)器用 title_id 列作為惟一鍵，在 titleauthor、sales 及 roysched 表中對

3、各匹配行進行定位。（2）dml 觸發(fā)器可以防止惡意或錯誤的 insert、update 以及 delete 操作，并強制執(zhí)行比 check 約束定義的限制更為復雜的其他限制。與 check 約束不同，dml 觸發(fā)器可以引用其他表中的列。（3）dml 觸發(fā)器可以評估數(shù)據(jù)修改前后表的狀態(tài)，并根據(jù)該差異采取措施。 9.1.1 dml觸發(fā)器創(chuàng)建 當創(chuàng)建一個觸發(fā)器時必須指定如下選項： （1）名稱；（2）在其上定義觸發(fā)器的表；（3）觸發(fā)器將何時激發(fā)；（4）激活觸發(fā)器的數(shù)據(jù)修改語句，有效選項為 insert、update 或 delete，多個數(shù)據(jù)修改語句可激活同一個觸發(fā)器；（5）執(zhí)行觸發(fā)操作的編程語句。

4、9.1.1 dml觸發(fā)器創(chuàng)建 dml 觸發(fā)器使用 deleted 和 inserted 邏輯表。它們在結構上和觸發(fā)器所在的表的結構相同，sql server會自動創(chuàng)建和管理這些表。可以使用這兩個臨時的駐留內存的表測試某些數(shù)據(jù)修改的效果及設置觸發(fā)器操作的條件。 deleted表用于存儲delete，update語句所影響的行的副本。在執(zhí)行delete或update語句時，行從觸發(fā)器表中刪除，并傳輸?shù)絛eleted表中。 inserted表用于存儲insert或update語句所影響的行的副本，在一個插入或更新事務處理中，新建的行被同時添加到inserted表和觸發(fā)器表中。inserted表中的行

5、是觸發(fā)器表中新行的副本。 9.1.1 dml觸發(fā)器創(chuàng)建使用使用sql server管理平臺創(chuàng)建觸發(fā)器的過程如下：管理平臺創(chuàng)建觸發(fā)器的過程如下：在sql server管理平臺中，展開指定的服務器和數(shù)據(jù)庫項，然后展開表，選擇并展開要在其上創(chuàng)建觸發(fā)器的表，如圖9-1所示，右擊觸發(fā)器選項，從彈出的快捷菜單中選擇“新建觸發(fā)器”選項，則會出現(xiàn)觸發(fā)器創(chuàng)建窗口，如圖9-2所示。最后，單擊“執(zhí)行”按鈕，即可成功創(chuàng)建觸發(fā)器。 圖9-1 新建觸發(fā)器對話框 圖9-2 新建觸發(fā)器窗口 9.1.1 dml觸發(fā)器創(chuàng)建使用使用create trigger命令創(chuàng)建命令創(chuàng)建dml觸發(fā)器的語法形式如觸發(fā)器的語法形式如下：下： cr

6、eate trigger schema_name.trigger_name on table|view with encryption execute as clause,.n for|after|instead of insert , update , delete with append not for replication as sql_statement ; .n|external name := assembly_name.class_name.method_name 9.1.1 dml觸發(fā)器創(chuàng)建例例9-1示例說明示例說明inserted，deleted表表的作用。執(zhí)行結果如右圖。

7、的作用。執(zhí)行結果如右圖。程序清單如下：程序清單如下：create table sc (sno char(10), cno char(2), score real)gocreate trigger tr1on scfor insert, update, delete as print inserted表：表：select * from insertedprint deleted表：表：select * from deletedgo圖9-3 觸發(fā)器的執(zhí)行結果 9.1.1 dml觸發(fā)器創(chuàng)建例例9-2創(chuàng)建一個觸發(fā)器，在創(chuàng)建一個觸發(fā)器，在 s 表上創(chuàng)建一個插入、表上創(chuàng)建一個插入、更新類型的觸發(fā)器。更新類

8、型的觸發(fā)器。程序清單如下：程序清單如下：create trigger tr_son sfor insert, update as begindeclare bh varchar(6)select bh =inserted.sno from inserted /*獲取獲取插入或更新操作時的新值（學號）插入或更新操作時的新值（學號）*/end 9.1.2 dml觸發(fā)器的應用 1. 使用使用insert觸發(fā)器觸發(fā)器insert觸發(fā)器通常被用來更新時間標記字段，或者驗證被觸發(fā)器監(jiān)控的字觸發(fā)器通常被用來更新時間標記字段，或者驗證被觸發(fā)器監(jiān)控的字段中數(shù)據(jù)滿足要求的標準，以確保數(shù)據(jù)的完整性。段中數(shù)據(jù)滿足要求

9、的標準，以確保數(shù)據(jù)的完整性。例例9-3建立一個觸發(fā)器，當向建立一個觸發(fā)器，當向sc表中添加數(shù)據(jù)時，如果添加的數(shù)據(jù)與表中添加數(shù)據(jù)時，如果添加的數(shù)據(jù)與s表中的表中的數(shù)據(jù)不匹配（沒有對應的學號），則將此數(shù)據(jù)刪除。數(shù)據(jù)不匹配（沒有對應的學號），則將此數(shù)據(jù)刪除。程序清單如下：程序清單如下：create trigger sc_ins on sc for insert asbegindeclare bh char(5)select bh=inserted.sno from insertedif not exists(select sno from s where s.sno=bh)delete sc whe

10、re sno=bhend 9.1.2 dml觸發(fā)器的應用 1. 使用使用insert觸發(fā)器觸發(fā)器例例9-4創(chuàng)建一個觸發(fā)器，當插入或更新成績列時，該觸發(fā)器檢查插入的創(chuàng)建一個觸發(fā)器，當插入或更新成績列時，該觸發(fā)器檢查插入的數(shù)據(jù)是否處于設定的范圍內。數(shù)據(jù)是否處于設定的范圍內。程序清單如下：程序清單如下：create trigger sc_insupdon scfor insert, updateasdeclare cj int,select cj=inserted.score from insertedif （cj 100） begin raiserror （成績的取值必須在成績的取值必須在0到到1

11、00之間之間, 16, 1） rollback transactionend 9.1.2 dml觸發(fā)器的應用 2. 使用使用update觸發(fā)器觸發(fā)器 當在一個有當在一個有update觸發(fā)器的表中修改記錄時，表中原來的記錄被移動到觸發(fā)器的表中修改記錄時，表中原來的記錄被移動到刪除表中，修改過的記錄插入到了插入表中，觸發(fā)器可以參考刪除表和插入刪除表中，修改過的記錄插入到了插入表中，觸發(fā)器可以參考刪除表和插入表以及被修改的表，以確定如何完成數(shù)據(jù)庫操作。表以及被修改的表，以確定如何完成數(shù)據(jù)庫操作。例例9-5創(chuàng)建一個修改觸發(fā)器，該觸發(fā)器防止用戶修改表創(chuàng)建一個修改觸發(fā)器，該觸發(fā)器防止用戶修改表s的入學成績

12、。的入學成績。程序清單如下：程序清單如下：create trigger tri_s_updon sfor updateasif update（escore）beginraiserror（不能修改入學成績不能修改入學成績,16,10）rollback transactionend go 9.1.2 dml觸發(fā)器的應用 2.使用使用update觸發(fā)觸發(fā)例9-6 das數(shù)據(jù)庫由存放實時數(shù)據(jù)的數(shù)據(jù)表以及存放歷史數(shù)據(jù)的歷史表組成。由于存放實時數(shù)據(jù)的數(shù)據(jù)表不斷更新，為了保存更新過的數(shù)據(jù)，在實時表和歷史表之間建立了觸發(fā)器。程序清單如下：create trigger dasd_utrigger on dasd

13、 for update as beginif update(tv) /*數(shù)據(jù)更新*/begin update dasd set ut=getdate() /*更新時間*/ from dasd,inserted where dasd.id=inserted.id insert dasdhis(id,tv,ut) select inserted.id,inserted.tv,dasd.ut from dasd,inserted where dasd.id=inserted.id /*將更新過的數(shù)據(jù)送入歷史庫*/endend 9.1.2 dml觸發(fā)器的應用 3. 使用使用delete觸發(fā)器觸發(fā)器 d

14、elete觸發(fā)器通常用于兩種情況，第一種情況是為了防止那些確實需要刪除但會引起數(shù)據(jù)一致性問題的記錄的刪除，第二種情況是執(zhí)行可刪除主記錄的子記錄的級聯(lián)刪除操作。例9-8 建立一個與s表結構一樣的表s1，當刪除表s中的記錄時，自動將刪除掉的記錄存放到s1表中。程序清單如下：create trigger tr_del on s /*建立觸發(fā)器for delete/*對表刪除操作as insert s1 select * from deleted /*將刪除掉的數(shù)據(jù)送入表s1中*/go9.1.2 dml觸發(fā)器的應用 3. 使用使用delete觸發(fā)器觸發(fā)器 例9-9當刪除表s中的記錄時，自動刪除表sc中

15、對應學號的記錄。程序清單如下：create trigger tr_del_s on s for deletebegindeclare bh char(5)select bh=deleted.sno from deleteddelete sc where sno=bhend 9.2 ddl觸發(fā)器的創(chuàng)建和應用 ddl 觸發(fā)器會為響應多種數(shù)據(jù)定義語言觸發(fā)器會為響應多種數(shù)據(jù)定義語言 (ddl) 語句語句而激發(fā)。這些語句主要是以而激發(fā)。這些語句主要是以 create、alter 和和 drop 開頭的語句。開頭的語句。ddl 觸發(fā)器可用于管理任務，例觸發(fā)器可用于管理任務，例如審核和控制數(shù)據(jù)庫操作。如審核

16、和控制數(shù)據(jù)庫操作。ddl 觸發(fā)器一般用于以下目的：觸發(fā)器一般用于以下目的： （1）防止對數(shù)據(jù)庫架構進行某些更改；）防止對數(shù)據(jù)庫架構進行某些更改；（2）希望數(shù)據(jù)庫中發(fā)生某種情況以響應數(shù)據(jù)庫架構中）希望數(shù)據(jù)庫中發(fā)生某種情況以響應數(shù)據(jù)庫架構中的更改；的更改； （3）要記錄數(shù)據(jù)庫架構中的更改或事件。）要記錄數(shù)據(jù)庫架構中的更改或事件。僅在運行觸發(fā)僅在運行觸發(fā) ddl 觸發(fā)器的觸發(fā)器的 ddl 語句后，語句后，ddl 觸觸發(fā)器才會激發(fā)。發(fā)器才會激發(fā)。ddl 觸發(fā)器無法作為觸發(fā)器無法作為 instead of 觸發(fā)器使用。觸發(fā)器使用。 9.2.1 創(chuàng)建ddl觸發(fā)器 使用create trigger命令創(chuàng)建d

17、dl觸發(fā)器的語法形式如下： create trigger trigger_name on all server|databasewith ,.n for|after event_type|event_group,.n as sql_statement; .n|external name ; 其中： :=encryption execute as clause := assembly_name.class_name.method_name 9.2.2 ddl觸發(fā)器的應用 在響應當前數(shù)據(jù)庫或服務器中處理的 transact-sql 事件時，可以激發(fā) ddl 觸發(fā)器。觸發(fā)器的作用域取決于事件。 例9

18、-11 使用 ddl 觸發(fā)器來防止數(shù)據(jù)庫中的任一表被修改或刪除。 程序清單如下： create trigger safety on database for drop_table, alter_table as print you must disable trigger safety to drop or alter tables! rollback 9.2.2 ddl觸發(fā)器的應用例9-12 使用 ddl 觸發(fā)器來防止在數(shù)據(jù)庫中創(chuàng)建表。 程序清單如下： create trigger safety on database for create_table as print create tab

19、le issued. select eventdata().value(/event_instance/tsqlcommand/commandtext)1,nvarchar(max) raiserror (new tables cannot be created in this database., 16, 1) rollback 9.3 ddl查看、修改和刪除觸發(fā)器9.3.1 查看觸發(fā)器 9.3.2 修改觸發(fā)器 9.3.3 刪除觸發(fā)器 9.3.1 查看觸發(fā)器 如果要顯示作用于表上的觸發(fā)器究竟對表有哪些操作，必須查看觸發(fā)器信息。在sql server中，有多種方法可以查看觸發(fā)器信息，其中最常用

20、的有如下兩種：（1）使用sql server管理平臺查看觸發(fā)器信息； （2）使用系統(tǒng)存儲過程查看觸發(fā)器。 9.3.1 查看觸發(fā)器（1）使用sql server管理平臺查看觸發(fā)器信息。在sql server管理平臺中，展開服務器和數(shù)據(jù)庫，選擇并展開表，然后展開觸發(fā)器選項，右擊需要查看的觸發(fā)器名稱，如圖9-4所示，從彈出的快捷菜單中，選擇“編寫觸發(fā)器腳本為create到新查詢編輯器窗口”，則可以看到觸發(fā)器的源代碼。 圖9-4 查看觸發(fā)器 9.3.1 查看觸發(fā)器（2）使用系統(tǒng)存儲過程查看觸發(fā)器。系統(tǒng)存儲過程sp_help、sp_helptext和sp_depends分別提供有關觸發(fā)器的不同信息。其具

21、體用途和語法形式如下。sp_help：用于查看觸發(fā)器的一般信息，如觸發(fā)器的名稱、屬性、類型和創(chuàng)建時間。sp_help 觸發(fā)器名稱sp_helptext：用于查看觸發(fā)器的正文信息。sp_helptext 觸發(fā)器名稱sp_depends：用于查看指定觸發(fā)器所引用的表或者指定的表涉及到的所有觸發(fā)器。sp_depends 觸發(fā)器名稱sp_depends 表名 9.3.2 修改觸發(fā)器通過sql server管理平臺、存儲過程，可以修改觸發(fā)器的正文和名稱。1使用sql server管理平臺修改觸發(fā)器正文。 在管理平臺中，展開指定的表，右擊要修改的觸發(fā)器，從彈出的快捷菜單中選擇“修改”選項，則會出現(xiàn)觸發(fā)器修

22、改窗口，如圖9-5所示。在文本框中修改觸發(fā)器的sql語句，單擊“語法檢查”按鈕，可以檢查語法是否正確，單擊“執(zhí)行”按鈕，可以成功修改此觸發(fā)器。 圖9-5 觸發(fā)器修改窗口 9.3.2 修改觸發(fā)器修改修改dml觸發(fā)器的語法形式如下：觸發(fā)器的語法形式如下： alter trigger schema_name.trigger_name on (table|view) with ,.n (for|after|instead of) delete,insert,update not for replication as sql_statement;.n|external name ; :=encrypti

23、on&lexecute as clause :=assembly_name.class_name.method_name修改修改ddl觸發(fā)器的語法形式如下：觸發(fā)器的語法形式如下： alter trigger trigger_name on database|all serverwith ,.n for|afterevent_type,.n|event_group as sql_statement;|external name ; :=encryption&lexecute as clause :=assembly_name.class_name.method_name 9.3.2 修改觸發(fā)器例例9-13 修改觸發(fā)器。修改觸發(fā)器。 程序清單如下：程序清單如下： create trigger s_reminder on s with encryption afte