輕松掌握設計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,deleteassql語句

作業：

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

cust_test: CustomerID char(5) PK; order_test:CustomerID char(5)--對應關系CustcityOrderid;PKCustnameOrderNamesCStatus;int;;OStatus;int --狀態Cstorageint;;Orders;;int --定購量和庫存量Cdate;;;date;Odate;;;date--日期

作業1：

在cust_test表中建立刪除觸發器，實現上述2表的級聯刪除。

作業2：

在order_test表建立insert觸發器，當向order_test表插入一行，如果cust_test表中對應

記錄status值為1，說明處于準備狀態不能寫入該數據。

答案1：use northwindgocreate trigger cust_orders_del1on Cust_testafter deleteas delete from order_testwhere CustomerID in(select CustomerID from deleted)go

答案2：use northwindgocreate trigger cust_orders_ins2on order_testafter insertasif (select cstatus from cust_test,inserted where

cust_test.customerid=inserted.customerid)=1beginprint 'The Goods is being processed'rollback transactionendgo

圖形化操作觸發器

11.3 查看觸發器情況

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

(1)sp_helptrigger 觸發器名

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

supdate,isdelete,isinsert,isafter,isinsteadof

(2)sp_helptext 觸發器名

查看文本信息

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

無效：use northwindalter table 表名disable trigger 觸發器名

重新有效：use northwindalter table 表名enable trigger 觸發器名

(4）刪除觸發器

use northwinddrop trigger 觸發器名，觸發器名

作業3：

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

作業4：

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

作業5：

要求訂購的物品一定要在倉庫中有的，并且數量足夠。

例6：

在order_test表上建立一個插入觸發器，同時插入多行數據時，要求訂購的物品一定要在倉庫中有的。

答案3：

use northwindgo create trigger cust_orders_ins3on order_testafter insertas update cust_test set cstorage=cstorage-inserted.ordersfrom cust_test,insertedwhere cust_test.customerid=inserted.customerid

答案4：

use northwindgocreate trigger orderdateupdateon order_testafter updateasif update (odate)beginraiserror('Error',10,1)rollback transactionend

答案5：

use northwindgocreate trigger order_insert5on order_testafter insertasbeginif(select count(*)from cust_test,insertedwhere cust_test.customerid=inserted.customerid)=0beginprint 'No entry in goods for your order'rollback transactionendif(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)beginprint 'No enough entry in goods for your order'rollback transactionendend

答案6：

use northwindgocreate trigger order_insert6on order_testafter insertasif (select count(*) from cust_test,insertedwhere cust_test.customerid=inserted.customerid)<>@@rowcount--可以在觸發器邏輯中使用 @@ROWCOUNT 函數以區分單行插入和多行插入。begindelete order_test from order_test,insertedwhere order_test.orderid=inserted.orderid andinserted.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 pubsGOSELECT count(*) AS CntFROM titles WHERE advance >= 5000GO

下面是結果集：

Cnt;;;;----------- 11;

(1 row(s) affected)

現在，將 ROWCOUNT 設置為 4，并更新預付款等于或大于 $5,000 的所有行。SET ROWCOUNT to 4.SET ROWCOUNT 4GOUPDATE titlesSET advance = 5000WHERE advance >= 5000GO