Sql Server觸發器的原理及應用技巧

1、觸發器的 '本質'：

觸發器是一種特殊的預存程序，它不能被顯式地調用， 而是在往表中插入記錄、更改記錄或者刪除記錄時，當事件發生時，才被 自動地啟用。

2、這樣做帶來的 '功能'：

觸發器可以用來對錶實施複雜的完整性條件約束，保持資料的一致性，當觸發器所保護的資料發生改變時，觸發器會自動被啟用， 響應同時執行一定的操作（對其它相關表的操作），從而保證對資料的不完整性條件約束或不正確的修改。

觸發器可以查詢其它表，同時也可以執行複雜的T-SQL語句。觸發器和引發觸發器執行的命令被當作一次交易處理，因此就具備了事務的所有特徵。

注意： '事務具備什麼特徵？在觸發器中的作用？'如果發現引起觸發器執行的T-SQL語句執行了一個非法操作，比如關於其它表的相關性操作，探索資料丟失或需調用的資料不存在，那麼就復原到該事件執行前的SQL SERVER資料庫狀態。

3、觸發器的作用：

觸發器可以對資料庫進行級聯修改，這一點剛才已經說過了。

需要說明的是： '觸發器和約束的關係和區別'

(1)一般來說，使用約束比使用觸發器效率更高。

(2)同時，觸發器可以完成比CHECK約束更複雜的限制。

說明：

2.1 與CHECK約束不同，在觸發器中可以引用其它的表。

2.2 觸發器可以發現改變前後表中資料的不一致，並根據這些不同來進行相應的操作。

2.3 對於一個表不同的操作（INSERT、UPDATE、DELETE）可以採用不同的觸發器，即使是對相同的語句也可以調用不同的觸發器來完成不同的操作。

舉例1：在簽訂一份訂單時，貨物的庫存量應減少。

問？這應用了觸發器的什麼特徵？CHECK約束能解決嗎？

舉例2：進行中整理的貨物不能下訂單。

問？這應用了觸發器的什麼特徵？CHECK約束能解決嗎？

4、對觸發器3種操作的分析：

在SQL SERVER為每個觸發器都建立了兩個專用表：inserted表和deleted表。

這是兩個邏輯表，由系統來維護，在觸發執行時存在，在觸發結束時消失。

這樣有什麼用途？

帶著問題看，具體操作步驟和過程：

(1）deleted表存放由於執行delete或update語句而要從表中刪除的所有行。

在執行delete或update操作時，被刪除的行從啟用觸發器的表中被移動（move）到deleted表，這兩個表不會有共同的行。

(2）inserted表存放由於執行insert或update語句而要向表中插入的所有行。

在執行insert或update事物時，新的行同時添加到啟用觸發器的表中和inserted表中，inserted表的內容是啟用觸發器的表中新行的拷貝。

說明：update事務可以看作是先執行一個delete操作，再執行一個insert操作，舊的行首先被移動到deleted表，讓後新行同時添加到啟用觸發器的表中和inserted表中。

11.1.3 instead of 和 after 觸發器

主要包括定義和應用範圍條件，操作執行時機；

11.2 建立觸發器

1、考慮為什麼要設計出發器，為解決什麼問題而設計？

2、應制定的內容：為什麼，大家思考一下？不知道，看下面的例子，全看完！

T-SQL語句建立觸發器

文法結構：

以下是引用片段：
create trigger 觸發器名
on 表或視圖
for|after|instead of --操作時機
insert,update,delete
as
sql語句

作業：

(要求：在northwind表中建立2個表：cust_test和order_test)

以下是引用片段：
cust_test: CustomerID char(5) PK   order_test:
CustomerID char(5)--對應關係
Custcity                            Orderid     PK
Custname                            OrderNames
CStatus     int                      OStatus     int --狀態
Cstorage    int                      Orders      int --定購量和庫存量
Cdate       date                     Odate       date--日期

作業1：
在cust_test表中建立刪除觸發器，實現上述2表的串聯刪除。

作業2：
在order_test表建立insert觸發器，當向order_test表插入一行，如果cust_test表中對應記錄status值為1，說明處於準備狀態不能寫入該資料。

以下是引用片段：
答案1：
use northwind
go
create trigger cust_orders_del1
on Cust_test
after delete
as
delete from order_test
where CustomerID in
(select CustomerID from deleted)
go

答案2：
use northwind
go
create trigger cust_orders_ins2
on order_test
after insert
as
if (select cstatus from cust_test,inserted
where cust_test.customerid=inserted.customerid)=1
begin
print ’The Goods is being processed’
rollback transaction
end
go

圖形化操作觸發器

11.3 查看觸發器情況

圖形化操作結合T-SQL命令

(1)sp_helptrigger 觸發器名
查看觸發器的名稱，擁有者和五個布爾值

以下是引用片段：
supdate,isdelete,isinsert,isafter,isinsteadof

(2)sp_helptext 觸發器名
查看文本資訊

(3)設定某一觸發器的無效和重新有效

以下是引用片段：
無效：
use northwind
alter table 表名
disable trigger 觸發器名

重新有效：
use northwind
alter table 表名
enable trigger 觸發器名
(4）刪除觸發器

以下是引用片段：
use northwind
drop trigger 觸發器名，觸發器名

作業3：
在order_test表上建立一個插入觸發器，在添加一個訂單時，減少cust_test表的相應貨物的記錄的庫存量。

作業4：
在order_test表上建立一個插入觸發器，規定訂單日期(Odate)不能手工修改。

作業5：
要求訂購的物品一定要在倉庫中有的，並且數量足夠。

作業6：
在order_test表上建立一個插入觸發器，同時插入多行資料時，要求訂購的物品一定要在倉庫中有的。

答案3：

以下是引用片段：
use northwind
go
create trigger cust_orders_ins3
on order_test
after insert
as
update cust_test
set cstorage=cstorage-inserted.orders
from cust_test,inserted
where cust_test.customerid=inserted.customerid

答案4：

以下是引用片段：
use northwind
go
create trigger orderdateupdate
on order_test
after update
as
if update (odate)
begin
raiserror(’Error’,10,1)
rollback transaction
end

答案5：

以下是引用片段：
use northwind
go
create trigger order_insert5
on order_test
after insert
as
begin
if(select count(*)
from cust_test,inserted
where cust_test.customerid=inserted.customerid)=0
begin
print ’No entry in goods for your order’
rollback transaction
end
if(select cust_test.cstorage from cust_test,inserted
where cust_test.customerid=inserted.customerid)<
(select inserted.orders from cust_test,inserted
where cust_test.customerid=inserted.customerid)
begin
print ’No enough entry in goods for your order’
rollback transaction
end
end

答案6：

以下是引用片段：
use northwind
go
create trigger order_insert6
on order_test
after insert
as
if
(select count(*) from cust_test,inserted
where cust_test.customerid=inserted.customerid)<>@@rowcount
--可以在觸發器邏輯中使用 @@ROWCOUNT 函數以區分單行插入和多行插入。
begin
delete order_test from order_test,inserted
where order_test.orderid=inserted.orderid and
inserted.customerid not in (select customerid from cust_test)
end

print @@rowcount

Transact-SQL 參考
SET ROWCOUNT
使 Microsoft? SQL Server? 在返回指定的行數之後停止處理查詢。
文法
以下是引用片段：
SET ROWCOUNT { number | @number_var }

參數
以下是引用片段：
number | @number_var

是在停止給定查詢之前要處理的行數（整數）。
注釋
建議將當前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 語句重新編寫為使用 TOP 文法。有關更多資訊，請參見 DELETE、INSERT 或 UPDATE。
對於在遠端資料表和本地及遠端資料分割視圖上執行的 INSERT、UPDATE 和 DELETE 語句，忽略 SET ROWCOUNT 選項設定。
若要關閉該選項（以便返回所有的行），請將 SET ROWCOUNT 指定為 0。
說 明 設定 SET ROWCOUNT 選項將使大多數 Transact-SQL 陳述式在已受指定數目的行影響後停止處理。這包括觸發器和 INSERT、UPDATE 及 DELETE 等資料修改語句。ROWCOUNT 選項對動態資料指標無效，但限制鍵集的行集和非感應式資料指標。使用該選項時應謹慎，它主要與 SELECT 語句一起使用。
如果行數的值較小，則 SET ROWCOUNT 替代 SELECT 語句 TOP 關鍵字。
SET ROWCOUNT 的設定是在執行或運行時設定，而不是在分析時設定。
許可權
SET ROWCOUNT 許可權預設授予所有使用者。
樣本
SET ROWCOUNT 在指定的行數後停止處理。在下例中，注意有 x 行滿足預付款少於或等於 $5,000 的條件；但是，從更新所返回的行數中可以看出並非所有的行都得到處理。ROWCOUNT 影響所有的 Transact-SQL 陳述式。
以下是引用片段：
USE pubs
GO
SELECT count(*) AS Cnt
FROM titles
WHERE advance >= 5000
GO

下面是結果集：
以下是引用片段：
Cnt       
-----------
11          

(1 row(s) affected)

現在，將 ROWCOUNT 設定為 4，並更新預付款等於或大於 $5,000 的所有行。
SET ROWCOUNT to 4.
SET ROWCOUNT 4
GO
UPDATE titles
SET advance = 5000
WHERE advance >= 5000
GO