SQLServer 觸發器詳解(10條)

SQLServer 觸發器詳解(10條)

一.不能在觸發器中使用的語句 觸發器中可以使用大多數 T-SQL 陳述式，但如下一些語句是不能在觸發器中使用的。

 

• CREATE 語句，如：CREATE DATABASE、CREATE TABLE、CREATE INDEX 等。
• ALTER 語句，如：ALTER DATABASE、ALTER TABLE、ALTER INDEX 等。
• DROP 語句，如：DROP DATABASE、DROP TABLE、DROP INDEX 等。
• DISK 語句，如：DISK INIT、DISK RESIZE。
• LOAD 語句，如：LOAD DATABASE、LOAD LOG。
• RESTORE 語句，如：RESTORE DATABASE、RESTORE LOG。
• RECONFIGURE
• 說明：有人說不能用 TRUNCATE TABLE 語句，其實是可以的。

 

二.建立觸發器用 CREATE TRIGGER

CREATE TRIGGER 觸發器名稱

ON 表名

FOR INSERT、UPDATE 或 DELETE

AS

    T-SQL 陳述式

注意：觸發器名稱是不加引號的。

如下是聯機叢書上的一個樣本，當在 titles 表上更改記錄時，發送郵件通知 MaryM。

CREATE TRIGGER reminder

ON titles

FOR INSERT, UPDATE, DELETE

AS

   EXEC master..xp_sendmail 'MaryM',

      'Don''t forget to print a report for the distributors.'

三.刪除觸發器

drop trigger 觸發器名稱,觸發器名稱...

注意：觸發器名稱是不加引號的。在刪除觸發器之前可以先看一下觸發器是否存在：

if Exists(select name from sysobjects where name=觸發器名稱 and xtype='TR')

用企業管理器刪除

在企業管理器中，在表上點右鍵->“所有任務”->“管理觸發器”，選中所要刪除的觸發器，然後點擊“刪除”。

四.重新命名觸發器

exec sp_rename 原名稱, 新名稱

sp_rename 是 SQL Server 內建的一個預存程序，用於更改當前資料庫中使用者建立的對象的名稱，如表名、列表、索引名等。

用企業管理器重新命名

在表上點右鍵->“所有任務”->“管理觸發器”，選中所要重新命名的觸發器，修改觸發器語句中的觸發器名稱，點擊“確定”。

五.複雜觸發器

INSTEAD OF

執行觸發器語句，但不執行觸發觸發器的 SQL 陳述式，比如試圖刪除一條記錄時，將執行觸發器指定的語句，此時不再執行 delete 語句。例：

create trigger f

on tbl

instead of delete

as

    insert into Logs...

IF UPDATE(列名)

檢查是否更新了某一列，用於 insert 或 update，不能用於 delete。例：

create trigger f

on tbl

for update

as

    if update(status) or update(title)

        sql_statement --更新了 status 或 title 列

inserted、deleted

這是兩個虛擬表，inserted 儲存的是 insert 或 update 之後所影響的記錄形成的表，deleted 儲存的是 delete 或 update 之前所影響的記錄形成的表。例：

create trigger tbl_delete

on tbl

for delete

as

    declare @title varchar(200)

    select @title=title from deleted

    insert into Logs(logContent) values('刪除了 title 為：' + title + '的記錄')

說明：如果向 inserted 或 deleted 虛擬表中取欄位類型為 text、image 的欄位值時，所取得的值將會是 null。

六. 查看當前資料庫中的觸發器

use 資料庫名

go

select * from sysobjects where xtype='TR'

sysobjects 儲存著資料庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。在 name 一列，我們可以看到觸發器名稱。

七. 查看觸發器內容

use 資料庫名

go

exec sp_helptext '觸發器名稱'

將會以表的樣式顯示觸發器內容。

除了觸發器外，sp_helptext 還可以顯示 規則、預設值、未加密的預存程序、使用者定義函數、視圖的文本

用企業管理器查看

在表上點右鍵->“所有任務”->“管理觸發器”，選擇所要查看的觸發器

八. 觸發器的擴充

1.預存程序 sp_helptrigger

預存程序 sp_helptrigger 用於查看觸發器的屬性。

sp_helptrigger 有兩個參數：第一個參數為表名；第二個為觸發器類型，為 char(6) 類型，可以是 INSERT、UPDATE、DELETE，如果省略則顯示指定表中所有類型觸發器的屬性。

例：

use 資料庫名

go

exec sp_helptrigger tbl

2.多個觸發器

觸發器的名稱不同，觸發事件相同（INSERT、UPDATE、DELETE），我們稱為多個觸發器。

多個觸發器之間的執行順序並不確定。

我個人認為應該避免使用多個觸發器，因為它不利於維護。

3.遞迴和嵌套觸發器

遞迴觸發器

遞迴分兩種，間接遞迴和直接遞迴。我們舉例解釋如下，假如有表1、表2名稱分別為 T1、T2，在 T1、T2 上分別有觸發器 G1、G2。

• 間接遞迴：對 T1 操作從而觸發 G1，G1 對 T2 操作從而觸發 G2，G2 對 T1 操作從而再次觸發 G1...
• 直接遞迴：對 T1 操作從而觸發 G1，G1 對 T1 操作從而再次觸發 G1...

 

嵌套觸發器

類似於間接遞迴，間接遞迴必然要形成一個環，而嵌套觸發器不一定要形成一個環，它可以 T1->T2->T3...這樣一直觸發下去，最多允許嵌套 32 層。

設定直接遞迴

預設情況下是禁止直接遞迴的，要設定為允許有兩種方法：

• T-SQL：exec sp_dboption 'dbName', 'recursive triggers', true
• EM：資料庫上點右鍵->屬性->選項。

 

設定間接遞迴、嵌套

預設情況下是允許間接遞迴、嵌套的，要設定為禁止有兩種方法：

• T-SQL：exec sp_configure 'nested triggers', 0 --第二個參數為 1 則為允許
• EM：註冊上點右鍵->屬性->伺服器設定

 

九.禁用啟用觸發器

禁用：alter table 表名 disable trigger 觸發器名稱

啟用：alter table 表名 enable trigger 觸發器名稱

如果有多個觸發器，則各個觸發器名稱之間用英文逗號隔開。

如果把“觸發器名稱”換成“ALL”，則表示禁用或啟用該表的全部觸發器。

十.觸發器復原

我們看到許多註冊系統在註冊後都不能更改使用者名稱，但這多半是由應用程式決定的， 如果直接開啟資料庫表變更，同樣可以更改其使用者名稱，在觸發器中利用復原就可以巧妙地實現無法更改使用者名稱。

use 資料庫名

go

create trigger tr

on 表名

for update

as

    if update(userName)

        rollback tran

關鍵在最後兩句，其解釋為：如果更新了 userName 列，就復原事務。

補充：

觸發器功能強大，輕鬆可靠地實現許多複雜的功能，為什麼又要慎用呢。 觸發器本身沒有過錯，但由於我們的濫用會造成資料庫及應用程式的維護困難。

在資料庫操作中，我們可以通過關係、觸發器、預存程序、應用程式等來實現資料操作，比如刪除 T1 表記錄時期望刪除 T2 表相關的記錄，此時可以建立串聯刪除的關係，也可以為 T1 表建立觸發器使同時刪除 T2 表相關記錄，也可以自訂預存程序刪除 T1 和 T2 表的記錄，也可以在應用程式中使用兩個 SQL 陳述式來刪除……到底用哪一種好呢，應該說我們建立通過建立關係來實現串聯刪除是最好的，除非更有高的需求。

觸發器還有一個用途可以用來保障資料的完整性，但同時規則、約束、預設值也可以保障資料完整性，到底哪一個好呢，一般說來，較為簡單的完整性要求我們不應該使用觸發器。兩者在運行機制上也是有區別的，像規則、約束、預設值這些是在資料更改之前進行資料驗證，而觸發器是在資料更改之後進行驗證（如果交易回復，該表將不會產生變化）。

總之，如果我們對觸發器過分的依賴，就會造成遍地是程式的情況，因為觸發器本身就需要別的程式給它一個觸發條件，也就是說至少在兩個地方存在著程式，同時我們拋棄了約束、預設值等而選用觸發器，勢必影響資料庫的結構。