如何制定自己的SQL Server觸發器

建立一個簡單的觸發器

觸發器是一種特殊的預存程序，類似於事件函數，SQL Server? 允許為 INSERT、UPDATE、DELETE 建立觸發器，即當在表中插入、更新、刪除記錄時，觸發一個或一系列 T-SQL語句。

觸發器可以在查詢分析器裡建立，也可以在表名上點右鍵->“所有任務”->“管理觸發器”來建立，不過都是要寫 T-SQL 陳述式的，只是在查詢分析器裡要先確定當前操作的資料庫。

建立觸發器用 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.'

重新命名觸發器

用查詢分析器重新命名：

exec sp_rename 原名稱, 新名稱

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

用企業管理器重新命名：

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

刪除觸發器

用查詢分析器刪除：

在查詢分析器中使用 drop trigger 觸發器名稱 來刪除觸發器。

也可以同時刪除多個觸發器：drop trigger 觸發器名稱,觸發器名稱...

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

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

用企業管理器刪除：

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

如何查看某個觸發器的內容

用查詢分析器查看:

use 資料庫名

go

exec sp_helptext '觸發器名稱'

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

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

用企業管理器查看：

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

如何查看當前資料庫中有哪些觸發器

在查詢分析器中運行：

use 資料庫名

go

select * from sysobjects where xtype='TR'

sysobjects

儲存著資料庫的對象，其中 xtype 為 TR 的記錄即為觸發器對象。

在 name 一列，我們可以看到觸發器名稱。

sp_helptrigger

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

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

例：

use 資料庫名

go

exec sp_helptrigger tbl

觸發器更多文法

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

create trigger tr

on 表名

for update

as

if update(userName)

rollback tran

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

遞迴、嵌套觸發器

遞迴觸發器:

遞迴分兩種，間接遞迴和直接遞迴。我們舉例解釋如下，假如有表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：註冊上點右鍵->屬性->伺服器設定。