觸發(fā)器

1、觸發(fā)器的 '本質(zhì)'：

觸發(fā)器是一種特殊的存儲(chǔ)過程，它不能被顯式地調(diào)用，

而是在往表中插入記錄、更改記錄或者刪除記錄時(shí)，當(dāng)事件發(fā)生時(shí)，才被

自動(dòng)地激活。

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

觸發(fā)器可以用來對(duì)表實(shí)施復(fù)雜的完整性約束，保持?jǐn)?shù)

據(jù)的一致性，當(dāng)觸發(fā)器所保護(hù)的數(shù)據(jù)發(fā)生改變時(shí)，觸發(fā)器會(huì)自動(dòng)被激活，

響應(yīng)同時(shí)執(zhí)行一定的操作（對(duì)其它相關(guān)表的操作），從而保證對(duì)數(shù)據(jù)的不完整

性約束或不正確的修改。

觸發(fā)器可以查詢其它表，同時(shí)也可以執(zhí)行復(fù)雜的T-SQL語句。觸發(fā)器和引

發(fā)觸發(fā)器執(zhí)行的命令被當(dāng)作一次事務(wù)處理，因此就具備了事務(wù)的所有特征。

注意： '事務(wù)具備什么特征？在觸發(fā)器中的作用？'

如果發(fā)現(xiàn)引起觸發(fā)器執(zhí)行的T-SQL語句執(zhí)行了一個(gè)非法操作，比如關(guān)于其它表的

相關(guān)性操作，發(fā)現(xiàn)數(shù)據(jù)丟失或需調(diào)用的數(shù)據(jù)不存在，那么就回滾到該事件執(zhí)行

前的SQL SERVER數(shù)據(jù)庫(kù)狀態(tài)。

3、觸發(fā)器的作用：

觸發(fā)器可以對(duì)數(shù)據(jù)庫(kù)進(jìn)行級(jí)聯(lián)修改，這一點(diǎn)剛才已經(jīng)說過了。

需要說明的是： '觸發(fā)器和約束的關(guān)系和區(qū)別'

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

(2)同時(shí)，觸發(fā)器可以完成比CHECK約束更復(fù)雜的限制。

說明：

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

2.2 觸發(fā)器可以發(fā)現(xiàn)改變前后表中數(shù)據(jù)的不一致，并根據(jù)這些不同來進(jìn)行相應(yīng)

的操作。

2.3 對(duì)于一個(gè)表不同的操作（INSERT、UPDATE、DELETE）可以采用不同的觸

發(fā)器，即使是對(duì)相同的語句也可以調(diào)用不同的觸發(fā)器來完成不同的操作。

舉例1：在簽訂一份訂單時(shí)，貨物的庫(kù)存量應(yīng)減少。

問？這應(yīng)用了觸發(fā)器的什么特征？CHECK約束能解決嗎？

舉例2：正在進(jìn)行整理的貨物不能下訂單。

問？這應(yīng)用了觸發(fā)器的什么特征？CHECK約束能解決嗎？

4、對(duì)觸發(fā)器3種操作的分析：

在SQL SERVER為每個(gè)觸發(fā)器都創(chuàng)建了兩個(gè)專用表：inserted表和deleted表。

這是兩個(gè)邏輯表，由系統(tǒng)來維護(hù)，在觸發(fā)執(zhí)行時(shí)存在，在觸發(fā)結(jié)束時(shí)消失。

這樣有什么用途？

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

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

在執(zhí)行delete或update操作時(shí)，被刪除的行從激活觸發(fā)器的表中被移動(dòng)（move）到deleted

表，這兩個(gè)表不會(huì)有共同的行。

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

在執(zhí)行insert或update事物時(shí)，新的行同時(shí)添加到激活觸發(fā)器的表中和inserted表中，

inserted表的內(nèi)容是激活觸發(fā)器的表中新行的拷貝。

說明：update事務(wù)可以看作是先執(zhí)行一個(gè)delete操作，再執(zhí)行一個(gè)insert操作，舊的行首先

被移動(dòng)到deleted表，讓后新行同時(shí)添加到激活觸發(fā)器的表中和inserted表中。

11.1.3 instead of 和 after 觸發(fā)器

主要包括定義和應(yīng)用范圍條件，操作執(zhí)行時(shí)機(jī)；

11.2 創(chuàng)建觸發(fā)器

1、考慮為什么要設(shè)計(jì)出發(fā)器，為解決什么問題而設(shè)計(jì)？

2、應(yīng)制定的內(nèi)容：為什么，大家思考一下？不知道，看下面的例子，全看完！

T-SQL語句創(chuàng)建觸發(fā)器

語法結(jié)構(gòu)：

create trigger 觸發(fā)器名 on 表或視圖for|after|instead of --操作時(shí)機(jī)insert,update,deleteassql語句

作業(yè)：

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

cust_test: CustomerID char(5) PK; order_test:CustomerID char(5)--對(duì)應(yīng)關(guān)系CustcityOrderid;PKCustnameOrderNamesCStatus;int;;OStatus;int --狀態(tài)Cstorageint;;Orders;;int --定購(gòu)量和庫(kù)存量Cdate;;;date;Odate;;;date--日期

作業(yè)1：

在cust_test表中建立刪除觸發(fā)器，實(shí)現(xiàn)上述2表的級(jí)聯(lián)刪除。

作業(yè)2：

在order_test表建立insert觸發(fā)器，當(dāng)向order_test表插入一行，如果cust_test表中對(duì)應(yīng)

記錄status值為1，說明處于準(zhǔn)備狀態(tài)不能寫入該數(shù)據(jù)。

答案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

圖形化操作觸發(fā)器

11.3 查看觸發(fā)器情況

圖形化操作結(jié)合T-SQL命令

(1)sp_helptrigger 觸發(fā)器名

查看觸發(fā)器的名稱，擁有者和五個(gè)布爾值

supdate,isdelete,isinsert,isafter,isinsteadof

(2)sp_helptext 觸發(fā)器名

查看文本信息

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

無效：use northwindalter table 表名disable trigger 觸發(fā)器名

重新有效：use northwindalter table 表名enable trigger 觸發(fā)器名

(4）刪除觸發(fā)器

use northwinddrop trigger 觸發(fā)器名，觸發(fā)器名

作業(yè)3：

在order_test表上建立一個(gè)插入觸發(fā)器，在添加一個(gè)訂單時(shí)，減少cust_test表的相應(yīng)貨物的記錄的庫(kù)存量。

作業(yè)4：

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

作業(yè)5：

要求訂購(gòu)的物品一定要在倉(cāng)庫(kù)中有的，并且數(shù)量足夠。

例6：

在order_test表上建立一個(gè)插入觸發(fā)器，同時(shí)插入多行數(shù)據(jù)時(shí)，要求訂購(gòu)的物品一定要在倉(cāng)庫(kù)中有的。

答案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--可以在觸發(fā)器邏輯中使用 @@ROWCOUNT 函數(shù)以區(qū)分單行插入和多行插入。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? 在返回指定的行數(shù)之后停止處理查詢。

語法

SET ROWCOUNT { number | @number_var }

參數(shù)

number | @number_var

是在停止給定查詢之前要處理的行數(shù)（整數(shù)）。

注釋

建議將當(dāng)前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 語句重新編寫為使用 TOP 語法。有關(guān)更多信息，請(qǐng)參見 DELETE、INSERT 或 UPDATE。

對(duì)于在遠(yuǎn)程表和本地及遠(yuǎn)程分區(qū)視圖上執(zhí)行的 INSERT、UPDATE 和 DELETE 語句，忽略 SET ROWCOUNT 選項(xiàng)設(shè)置。

若要關(guān)閉該選項(xiàng)（以便返回所有的行），請(qǐng)將 SET ROWCOUNT 指定為 0。

說明 設(shè)置 SET ROWCOUNT 選項(xiàng)將使大多數(shù) Transact-SQL 語句在已受指定數(shù)目的行影響后停止處理。這包括觸發(fā)器和 INSERT、UPDATE 及 DELETE 等數(shù)據(jù)修改語句。ROWCOUNT 選項(xiàng)對(duì)動(dòng)態(tài)游標(biāo)無效，但限制鍵集的行集和不感知游標(biāo)。使用該選項(xiàng)時(shí)應(yīng)謹(jǐn)慎，它主要與 SELECT 語句一起使用。

如果行數(shù)的值較小，則 SET ROWCOUNT 替代 SELECT 語句 TOP 關(guān)鍵字。

SET ROWCOUNT 的設(shè)置是在執(zhí)行或運(yùn)行時(shí)設(shè)置，而不是在分析時(shí)設(shè)置。

權(quán)限

SET ROWCOUNT 權(quán)限默認(rèn)授予所有用戶。

示例

SET ROWCOUNT 在指定的行數(shù)后停止處理。在下例中，注意有 x 行滿足預(yù)付款少于或等于 $5,000 的條件；但是，從更新所返回的行數(shù)中可以看出并非所有的行都得到處理。ROWCOUNT 影響所有的 Transact-SQL 語句。

USE pubsGOSELECT count(*) AS CntFROM titles WHERE advance >= 5000GO

下面是結(jié)果集：

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

(1 row(s) affected)

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