觸發(fā)器詳細介紹

1、觸發(fā)器1、 觸發(fā)器概述觸發(fā)器是一種特殊類型的存儲過程，不由用戶直接調(diào)用。創(chuàng)建觸發(fā)器時會對其進行定義，以便在對特定表或列作特定類型的數(shù)據(jù)修改時執(zhí)行。CREATE PROCEDURE 或 CREATE TRIGGER 語句不能跨越批處理。即存儲過程或觸發(fā)器始終只能在一個批處理中創(chuàng)建并編譯到一個執(zhí)行計劃中。用觸發(fā)器還可以強制執(zhí)行業(yè)務(wù)規(guī)則。Microsoft SQL Server提供了兩種主要機制來強制業(yè)務(wù)規(guī)則和數(shù)據(jù)完整性：約束和觸發(fā)器。觸發(fā)器是一種特殊類型的存儲過程，它在指定的表中的數(shù)據(jù)發(fā)生變化時自動生效。喚醒調(diào)用觸發(fā)器以響應(yīng) INSERT、UPDATE 或 DELETE 語句。觸發(fā)器可以查詢其它表

2、，并可以包含復(fù)雜的 Transact-SQL 語句。將觸發(fā)器和觸發(fā)它的語句作為可在觸發(fā)器內(nèi)回滾的單個事務(wù)對待。如果檢測到嚴重錯誤（例如，磁盤空間不足），則整個事務(wù)即自動回滾。二、觸發(fā)器的優(yōu)點觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改；不過，通過級聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改觸發(fā)器可以強制比用 CHECK 約束定義的約束更為復(fù)雜的約束。與 CHECK 約束不同的是，觸發(fā)器可以引用其它表中的列。例如，觸發(fā)器可以使用另一個表中的 SELECT 比較插入或更新的數(shù)據(jù)，以及執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義錯誤信息。觸發(fā)器也可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。一個表中的多個

3、同類觸發(fā)器（INSERT、UPDATE 或 DELETE）允許采取多個不同的對策以響應(yīng)同一個修改語句。 觸發(fā)器是自動的：它們在對表的數(shù)據(jù)作了任何修改（比如手工輸入或者應(yīng)用程序采取的操作）之后立即被激活。 觸發(fā)器可以通過數(shù)據(jù)庫中的相關(guān)表進行層疊更改。例如，可以在 titles 表的 title_id 列上寫入一個刪除觸發(fā)器，以使其它表中的各匹配行采取刪除操作。該觸發(fā)器用 title_id 列作為唯一鍵，在 titleauthor、sales 及 roysched 表中對各匹配行進行定位。 三、比較觸發(fā)器與約束 約束和觸發(fā)器在特殊情況下各有優(yōu)勢。觸發(fā)器的主要好處在于它們可以包含使用 Transac

4、t-SQL 代碼的復(fù)雜處理邏輯。因此，觸發(fā)器可以支持約束的所有功能；但它在所給出的功能上并不總是最好的方法。 實體完整性總應(yīng)在最低級別上通過索引進行強制，這些索引或是 PRIMARY KEY 和 UNIQUE 約束的一部分，或是在約束之外獨立創(chuàng)建的。假設(shè)功能可以滿足應(yīng)用程序的功能需求，域完整性應(yīng)通過 CHECK 約束進行強制，而引用完整性 (RI) 則應(yīng)通過 FOREIGN KEY 約束進行強制。 在約束所支持的功能無法滿足應(yīng)用程序的功能要求時，觸發(fā)器就極為有用。例如： 除非 REFERENCES 子句定義了級聯(lián)引用操作，否則 FOREIGN KEY 約束只能以與另一列中的值完全匹配的值來驗證

5、列值。 CHECK 約束只能根據(jù)邏輯表達式或同一表中的另一列來驗證列值。如果應(yīng)用程序要求根據(jù)另一個表中的列驗證列值，則必須使用觸發(fā)器。 約束只能通過標準的系統(tǒng)錯誤信息傳遞錯誤信息。如果應(yīng)用程序要求使用（或能從中獲益）自定義信息和較為復(fù)雜的錯誤處理，則必須使用觸發(fā)器。 觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實現(xiàn)級聯(lián)更改；不過，通過級聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改。 觸發(fā)器可以禁止或回滾違反引用完整性的更改，從而取消所嘗試的數(shù)據(jù)修改。當更改外鍵且新值與主鍵不匹配時，此類觸發(fā)器就可能發(fā)生作用。例如，可以在 titleauthor.title_id 上創(chuàng)建一個插入觸發(fā)器，使它在新值與 titles.t

6、itle_id 中的某個值不匹配時回滾一個插入。不過，通常使用 FOREIGN KEY 來達到這個目的。 如果觸發(fā)器表上存在約束，則在 INSTEAD OF 觸發(fā)器執(zhí)行后但在 AFTER 觸發(fā)器執(zhí)行前檢查這些約束。如果約束破壞，則回滾 INSTEAD OF 觸發(fā)器操作并且不執(zhí)行 AFTER 觸發(fā)器。四、創(chuàng)建一個簡單的觸發(fā)器觸發(fā)器是一種特殊的存儲過程，類似于事件函數(shù)，SQL Server 允許為 INSERT、UPDATE、DELETE 創(chuàng)建觸發(fā)器，即當在表中插入、更新、刪除記錄時，觸發(fā)一個或一系列 T-SQL語句。觸發(fā)器可以在查詢分析器里創(chuàng)建，也可以在表名上點右鍵->“所有任務(wù)”->

7、;“管理觸發(fā)器”來創(chuàng)建，不過都是要寫 T-SQL 語句的，只是在查詢分析器里要先確定當前操作的數(shù)據(jù)庫。創(chuàng)建觸發(fā)器用 CREATE TRIGGERCREATE TRIGGER 觸發(fā)器名稱ON 表名FOR INSERT、UPDATE 或 DELETEAS T-SQL 語句注意：觸發(fā)器名稱是不加引號的。如下是聯(lián)機叢書上的一個示例，當在 titles 表上更改記錄時，發(fā)送郵件通知 MaryM。CREATE TRIGGER reminderON titlesFOR INSERT, UPDATE, DELETE AS EXEC master.xp_sendmail 'MaryM', 

8、9;Don''t forget to print a report for the distributors.'五、刪除觸發(fā)器用查詢分析器刪除在查詢分析器中使用 drop trigger 觸發(fā)器名稱 來刪除觸發(fā)器。也可以同時刪除多個觸發(fā)器：drop trigger 觸發(fā)器名稱,觸發(fā)器名稱.注意：觸發(fā)器名稱是不加引號的。在刪除觸發(fā)器之前可以先看一下觸發(fā)器是否存在：if Exists(select name from sysobjects where name=觸發(fā)器名稱 and xtype='TR')用企業(yè)管理器刪除在企業(yè)管理器中，在表上點右鍵->“

9、所有任務(wù)”->“管理觸發(fā)器”，選中所要刪除的觸發(fā)器，然后點擊“刪除”。六、重命名觸發(fā)器用查詢分析器重命名exec sp_rename 原名稱, 新名稱sp_rename 是 SQL Server 自帶的一個存儲過程，用于更改當前數(shù)據(jù)庫中用戶創(chuàng)建的對象的名稱，如表名、列表、索引名等。用企業(yè)管理器重命名在表上點右鍵->“所有任務(wù)”->“管理觸發(fā)器”，選中所要重命名的觸發(fā)器，修改觸發(fā)器語句中的觸發(fā)器名稱，點擊“確定”。四、more.INSTEAD OF執(zhí)行觸發(fā)器語句，但不執(zhí)行觸發(fā)觸發(fā)器的 SQL 語句，比如試圖刪除一條記錄時，將執(zhí)行觸發(fā)器指定的語句，此時不再執(zhí)行 delete 語句

10、。例：create trigger fon tblinstead of deleteas insert into Logs.IF UPDATE(列名)檢查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：create trigger fon tblfor updateas if update(status) or update(title) sql_statement -更新了 status 或 title 列inserted、deleted這是兩個虛擬表，inserted 保存的是 insert 或 update 之后所影響的記錄形成的表，deleted 保

11、存的是 delete 或 update 之前所影響的記錄形成的表。例：create trigger tbl_deleteon tblfor deleteas declare title varchar(200) select title=title from deleted insert into Logs(logContent) values('刪除了 title 為：' + title + '的記錄')說明：如果向 inserted 或 deleted 虛擬表中取字段類型為 text、image 的字段值時，所取得的值將會是 null。七、查看數(shù)據(jù)庫中所有的

12、觸發(fā)器在查詢分析器中運行：use數(shù)據(jù)庫名goselect*fromsysobjectswherextype='TR'sysobjects 保存著數(shù)據(jù)庫的對象，其中 xtype 為 TR 的記錄即為觸發(fā)器對象。在 name 一列，我們可以看到觸發(fā)器名稱。八、sp_helptext 查看觸發(fā)器內(nèi)容用查詢分析器查看use數(shù)據(jù)庫名goexecsp_helptext'觸發(fā)器名稱'將會以表的樣式顯示觸發(fā)器內(nèi)容。除了觸發(fā)器外，sp_helptext 還可以顯示 規(guī)則、默認值、未加密的存儲過程、用戶定義函數(shù)、視圖的文本用企業(yè)管理器查看在表上點右鍵->“所有任務(wù)”->

13、“管理觸發(fā)器”，選擇所要查看的觸發(fā)器存儲過程九、sp_helptrigger 用于查看觸發(fā)器的屬性sp_helptrigger 有兩個參數(shù)：第一個參數(shù)為表名；第二個為觸發(fā)器類型，為 char(6) 類型，可以是 INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發(fā)器的屬性。例：use數(shù)據(jù)庫名goexecsp_helptriggertbl 十、遞歸、嵌套觸發(fā)器遞歸分兩種，間接遞歸和直接遞歸。我們舉例解釋如下，假如有表1、表2名稱分別為 T1、T2，在 T1、T2 上分別有觸發(fā)器 G1、G2。· 間接遞歸：對 T1 操作從而觸發(fā) G1，G1 對 T2 操作從而觸發(fā)

14、 G2，G2 對 T1 操作從而再次觸發(fā) G1.· 直接遞歸：對 T1 操作從而觸發(fā) G1，G1 對 T1 操作從而再次觸發(fā) G1.1、嵌套觸發(fā)器類似于間接遞歸，間接遞歸必然要形成一個環(huán)，而嵌套觸發(fā)器不一定要形成一個環(huán)，它可以 T1->T2->T3.這樣一直觸發(fā)下去，最多允許嵌套 32 層。2、設(shè)置直接遞歸默認情況下是禁止直接遞歸的，要設(shè)置為允許有兩種方法：· T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true· EM：數(shù)據(jù)庫上點右鍵->屬性-&g

15、t;選項。3、設(shè)置間接遞歸、嵌套默認情況下是允許間接遞歸、嵌套的，要設(shè)置為禁止有兩種方法：· T-SQL：exec sp_configure 'nested triggers', 0 -第二個參數(shù)為 1 則為允許· EM：注冊上點右鍵->屬性->服務(wù)器設(shè)置。 十一、觸發(fā)器回滾我們看到許多注冊系統(tǒng)在注冊后都不能更改用戶名，但這多半是由應(yīng)用程序決定的， 如果直接打開數(shù)據(jù)庫表進行更改，同樣可以更改其用戶名，在觸發(fā)器中利用回滾就可以巧妙地實現(xiàn)無法更改用戶名。use 數(shù)據(jù)庫名gocreate trigger tron 表名for updateas if u

16、pdate(userName) rollback tran關(guān)鍵在最后兩句，其解釋為：如果更新了 userName 列，就回滾事務(wù)。十二、禁用、啟用觸發(fā)器禁用：alter table 表名 disable trigger 觸發(fā)器名稱啟用：alter table 表名 enable trigger 觸發(fā)器名稱如果有多個觸發(fā)器，則各個觸發(fā)器名稱之間用英文逗號隔開。如果把“觸發(fā)器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發(fā)器。十三、觸發(fā)器限制CREATE TRIGGER 必須是批處理中的第一條語句，并且只能應(yīng)用到一個表中。觸發(fā)器只能在當前的數(shù)據(jù)庫中創(chuàng)建，不過觸發(fā)器可以引用當前數(shù)據(jù)庫的外部對象。

17、如果指定觸發(fā)器所有者名稱以限定觸發(fā)器，請以相同的方式限定表名。在同一條 CREATE TRIGGER 語句中，可以為多種用戶操作（如 INSERT 和 UPDATE）定義相同的觸發(fā)器操作。如果一個表的外鍵在 DELETE/UPDATE 操作上定義了級聯(lián)，則不能在該表上定義 INSTEAD OF DELETE/UPDATE 觸發(fā)器。在觸發(fā)器內(nèi)可以指定任意的 SET 語句。所選擇的 SET 選項在觸發(fā)器執(zhí)行期間有效，并在觸發(fā)器執(zhí)行完后恢復(fù)到以前的設(shè)置。與使用存儲過程一樣，當觸發(fā)器激發(fā)時，將向調(diào)用應(yīng)用程序返回結(jié)果。若要避免由于觸發(fā)器激發(fā)而向應(yīng)用程序返回結(jié)果，請不要包含返回結(jié)果的 SELECT 語句，

18、也不要包含在觸發(fā)器中進行變量賦值的語句。包含向用戶返回結(jié)果的 SELECT 語句或進行變量賦值的語句的觸發(fā)器需要特殊處理；這些返回的結(jié)果必須寫入允許修改觸發(fā)器表的每個應(yīng)用程序中。如果必須在觸發(fā)器中進行變量賦值，則應(yīng)該在觸發(fā)器的開頭使用 SET NOCOUNT 語句以避免返回任何結(jié)果集。DELETE 觸發(fā)器不能捕獲 TRUNCATE TABLE 語句。盡管 TRUNCATE TABLE 語句實際上是沒有 WHERE 子句的 DELETE（它刪除所有行），但它是無日志記錄的，因而不能執(zhí)行觸發(fā)器。因為 TRUNCATE TABLE 語句的權(quán)限默認授予表所有者且不可轉(zhuǎn)讓，所以只有表所有者才需要考慮無意

19、中用 TRUNCATE TABLE 語句規(guī)避 DELETE 觸發(fā)器的問題。無論有日志記錄還是無日志記錄，WRITETEXT 語句都不激活觸發(fā)器。觸發(fā)器中不允許以下 Transact-SQL 語句：ALTER DATABASE CREATE DATABASE DISK INITDISK RESIZE DROP DATABASE LOAD DATABASELOAD LOG RECONFIGURE RESTORE DATABASERESTORE LOG說明 由于 SQL Server 不支持系統(tǒng)表中的用戶定義觸發(fā)器，因此建議不要在系統(tǒng)表中創(chuàng)建用戶定義觸發(fā)器。十四、多個觸發(fā)器SQL Server 允許

20、為每個數(shù)據(jù)修改事件（DELETE、INSERT 或 UPDATE）創(chuàng)建多個觸發(fā)器。例如，如果對已有 UPDATE 觸發(fā)器的表執(zhí)行 CREATE TRIGGER FOR UPDATE，則將創(chuàng)建另一個更新觸發(fā)器。在早期版本中，在每個表上，每個數(shù)據(jù)修改事件（INSERT、UPDATE 或 DELETE）只允許有一個觸發(fā)器。說明 如果觸發(fā)器名稱不同，則 CREATE TRIGGER（兼容級別為 70）的默認行為是在現(xiàn)有的觸發(fā)器中添加其它觸發(fā)器。如果觸發(fā)器名稱相同，則 SQL Server 返回一條錯誤信息。但是，如果兼容級別等于或小于 65，則使用 CREATE TRIGGER 語句創(chuàng)建的新觸發(fā)器將替

21、換同一類型的任何現(xiàn)有觸發(fā)器，即使觸發(fā)器名稱不同。有關(guān)更多信息，請參見 sp_dbcmptlevel。十五、遞歸觸發(fā)器當在 sp_dboption 中啟用 recursive triggers 設(shè)置時，SQL Server 還允許觸發(fā)器的遞歸調(diào)用。遞歸觸發(fā)器允許發(fā)生兩種類型的遞歸：* 間接遞歸* 直接遞歸使用間接遞歸時，應(yīng)用程序更新表 T1，從而激發(fā)觸發(fā)器 TR1，該觸發(fā)器更新表 T2。在這種情況下，觸發(fā)器 T2 將激發(fā)并更新 T1。使用直接遞歸時，應(yīng)用程序更新表 T1，從而激發(fā)觸發(fā)器 TR1，該觸發(fā)器更新表 T1。由于表 T1 被更新，觸發(fā)器 TR1 再次激發(fā)，依此類推。下例既使用了間接觸發(fā)器

22、遞歸，又使用了直接觸發(fā)器遞歸。假定在表 T1 中定義了兩個更新觸發(fā)器 TR1 和 TR2。觸發(fā)器 TR1 遞歸地更新表 T1。UPDATE 語句使 TR1 和 TR2 各執(zhí)行一次。而 TR1 的執(zhí)行將觸發(fā) TR1（遞歸）和 TR2 的執(zhí)行。給定觸發(fā)器的 inserted 和 deleted 表只包含與喚醒調(diào)用觸發(fā)器的 UPDATE 語句相對應(yīng)的行。說明 只有啟用 sp_dboption 的 recursive triggers 設(shè)置，才會發(fā)生上述行為。對于為給定事件定義的多個觸發(fā)器，并沒有確定的執(zhí)行順序。每個觸發(fā)器都應(yīng)是自包含的。禁用 recursive triggers 設(shè)置只能禁止直接遞歸。若要也禁用間接遞歸，請使用 sp_configure 將 nested triggers 服務(wù)器選項設(shè)置為 0。如果任一觸發(fā)器執(zhí)行了 ROLLBACK TRANSACTION 語句，則無論嵌套級是多少，都不會進一步執(zhí)行其它觸發(fā)器。十六、嵌套觸發(fā)器觸發(fā)器最多可以嵌套 32 層。如果一個觸發(fā)器更改了