觸發(fā)器按類型分為三類：

裕民網(wǎng)站建設(shè)公司成都創(chuàng)新互聯(lián)公司,裕民網(wǎng)站設(shè)計(jì)制作，有大型網(wǎng)站制作公司豐富經(jīng)驗(yàn)。已為裕民成百上千家提供企業(yè)網(wǎng)站建設(shè)服務(wù)。企業(yè)網(wǎng)站搭建\成都外貿(mào)網(wǎng)站建設(shè)公司要多少錢，請(qǐng)找那個(gè)售后服務(wù)好的裕民做網(wǎng)站的公司定做！

1. DML 觸發(fā)器，在數(shù)據(jù)變更時(shí)觸發(fā)；

2. DDL 觸發(fā)器，在修改數(shù)據(jù)庫級(jí)別或?qū)嵗?jí)別對(duì)象時(shí)觸發(fā)；

3. Login 觸發(fā)器，在用戶登錄時(shí)觸發(fā)；

 

最常見的是DML觸發(fā)器，DML觸發(fā)器又可以分為兩類： INSTEAD OF觸發(fā)器和AFTER觸發(fā)器（部分書上有提到FOR觸發(fā)器，其實(shí)就是AFTER 觸發(fā)器，只是寫法不同而已）。

從功能來看，INSTEAD OF觸發(fā)器用來替換實(shí)際的數(shù)據(jù)修改操作，而AFTER觸發(fā)器用來在實(shí)際操作完成后進(jìn)行后續(xù)操作。例如對(duì)于DELETE操作，如果我們期望只修改數(shù)據(jù)狀態(tài)來標(biāo)示數(shù)據(jù)已被刪除而不是將數(shù)據(jù)從表中刪除，那么我們可以使用INSTEAD OF觸發(fā)器來實(shí)現(xiàn)；如果我們期望在刪除數(shù)據(jù)后在其他表記錄刪除操作的發(fā)生時(shí)間，那么我們可以使用AFTER觸發(fā)器來實(shí)現(xiàn)。

從執(zhí)行來看，INSTEAD OF觸發(fā)器和AFTER觸發(fā)器的所處的執(zhí)行時(shí)期不同，SQL Server中的觸發(fā)順序?yàn)椋?/p>

1. 觸發(fā)INSTEAD OF觸發(fā)器

2. 觸發(fā)DEFAULT 約束

3. 觸發(fā)主鍵/唯一/CHECK約束

4. 觸發(fā)外鍵約束

5. 觸發(fā)AFTER 觸發(fā)器

因此如果期望修改操作順利執(zhí)行而不觸發(fā)約束導(dǎo)致回滾的話，可以使用INSTEAD OF觸發(fā)器來將實(shí)現(xiàn)(在INSTEAD OF 觸發(fā)器中修改使數(shù)據(jù)滿足約束條件)。

 

因?yàn)镮NSTEAD OF 觸發(fā)器改寫了實(shí)際要發(fā)生的修改操作，因此每個(gè)表上每種修改類型(DELETE/INSERT/UPDATE)只能有一個(gè)INSTEAD OF 觸發(fā)器；而AFTER 觸發(fā)器沒有類似限制，可以創(chuàng)建多個(gè)AFTER觸發(fā)器。

問題來了，在存在多個(gè)AFTER觸發(fā)器情況下，AFTER觸發(fā)器按什么順序來執(zhí)行呢？SQL Server允許針對(duì)每種修改類型(DELETE/INSERT/UPDATE)指定一個(gè)最先觸發(fā)和最后觸發(fā)的AFTER觸發(fā)器，但不能控制其余的觸發(fā)器觸發(fā)順序。

指定最先執(zhí)行的AFTER觸發(fā)器：

--指定針對(duì)INSERT操作最先觸發(fā)的AFTER觸發(fā)器EXEC sys.sp_settriggerorder@triggername='tr_TB1_INSERT',@order='First',@stmttype='INSERT'

 

說完觸發(fā)順序，再來說道說道觸發(fā)次數(shù)，裝逼的說法為：DML trrigers have statement scope and only fire just once regardless of how many rows affected.通俗說法就是對(duì)于一條語句，不管語句修改了多少行(0行或者1000行)，對(duì)應(yīng)該操作類型的觸發(fā)器都會(huì)被觸發(fā)并且只觸發(fā)一次。

PS:上面說的Fire only once只是針對(duì)執(zhí)行的SQL語句，并不包含該觸發(fā)器內(nèi)部的SQL語句

 

SQL server中有兩種特殊的觸發(fā)器:嵌套(Nested)觸發(fā)器和遞歸(Recursive)觸發(fā)器，由Demo來解釋下：

嵌套(Nested)觸發(fā)器：在TB1和TB2上創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被觸發(fā)時(shí)，TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB2上TR_TB2_INSERT1被觸發(fā)

--================================--在TB1和TB2上創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被--觸發(fā)時(shí)，TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB2上--TR_TB2_INSERT1被觸發(fā)，即屬于Nested觸發(fā)器CREATE TRIGGER TR_TB1_INSERT1ON dbo.TB1
AFTER INSERTASBEGININSERT INTO TB2(C1)SELECT C1 FROM insertedENDGOCREATE TRIGGER TR_TB2_INSERT1ON dbo.TB2
AFTER INSERTASBEGINSELECT 1END

 

遞歸(Recursive)觸發(fā)器可分為直接遞歸(Directed Recursive)觸發(fā)器和間接遞歸(Indirect Recursive)觸發(fā)器

直接遞歸(Directed Recursive)觸發(fā)器:

在TB1創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被觸發(fā)時(shí)，TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB1上TR_TB1_INSERT1再次被觸發(fā)

--================================--在TB1創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被觸發(fā)時(shí)，--TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB1上TR_TB1_INSERT1--再次被觸發(fā)，即屬于直接遞歸(Directed Recursive)觸發(fā)器。ALTER TRIGGER TR_TB1_INSERT1ON dbo.TB1
AFTER INSERTASBEGIN--限制遞歸層數(shù)為10層
    IF(@@NESTLEVEL<10)    BEGIN
        INSERT INTO TB1(C1)        SELECT C1+1 FROM inserted    ENDENDGO

 

間接遞歸(Indirect Recursive)觸發(fā)器:

在TB1和TB2上創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被觸發(fā)時(shí)，TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB2上TR_TB2_INSERT1被觸發(fā)，而TB2上TR_TB2_INSERT1的觸發(fā)器執(zhí)行時(shí)又導(dǎo)致TB1上TR_TB1_INSERT1被觸發(fā)，從而引發(fā)循環(huán)。

--================================--在TB1和TB2上創(chuàng)建觸發(fā)器，當(dāng)TB1上TR_TB1_INSERT1被--觸發(fā)時(shí)，TR_TB1_INSERT1中的語句執(zhí)行導(dǎo)致TB2上--TR_TB2_INSERT1被觸發(fā)，而TB2上TR_TB2_INSERT1的--觸發(fā)器執(zhí)行時(shí)又導(dǎo)致TB1上TR_TB1_INSERT1被觸發(fā)，從而--引發(fā)循環(huán)，即間接遞歸(Indirect Recursive)觸發(fā)器CREATE TRIGGER TR_TB1_INSERT1ON dbo.TB1
AFTER INSERTASBEGIN
    IF(@@NESTLEVEL<10)    BEGIN
        INSERT INTO TB2(C1)        SELECT C1 FROM inserted    ENDENDGOCREATE TRIGGER TR_TB2_INSERT1ON dbo.TB2
AFTER INSERTASBEGIN
    IF(@@NESTLEVEL<10)    BEGIN
        INSERT INTO TB1(C1)        SELECT C1 FROM inserted    ENDEND

需要注意的是:

1. 嵌套(Nested)觸發(fā)器在sys.configurations中配置，默認(rèn)開啟

2. (Recursive)觸發(fā)器在數(shù)據(jù)庫級(jí)別配置，默認(rèn)為關(guān)閉，即不允許直接遞歸(Directed Recursive)觸發(fā)器，但不影響間接遞歸(Indirect Recursive)觸發(fā)器，如果需要禁用遞歸(Indirect Recursive)觸發(fā)器，需要同時(shí)禁用嵌套(Nested)觸發(fā)器和(Recursive)觸發(fā)器

3. 由于嵌套觸發(fā)器會(huì)消耗大量資源(需要保留每層觸發(fā)器的上下文以便回滾)，因此默認(rèn)限制最多嵌套32層。

 

行版本(Row version)

在SQL Server多中功能中使用到row version來保留多個(gè)版本的數(shù)據(jù)，這些功能有：

1. MARS

2. Triggers

3. Online indexing

4. Optimistic Transaction Isolation Levels

因此在使用觸發(fā)器時(shí)，應(yīng)考慮到可能會(huì)為表增加額外14bytes的行版本存儲(chǔ)指針

如下面例子中，表中數(shù)據(jù)被刪除一半，但由于數(shù)據(jù)只是表示為gost，尚未真正移除，而由于觸發(fā)器存在，每行額外增加14byte的數(shù)據(jù)，從而導(dǎo)致頁拆分，最終使得刪除操作完成后表反而增大。

測(cè)試代碼：

USE tempdb--================================--創(chuàng)建測(cè)試表DROP TABLE TB1GOCREATE TABLE TB1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    C2 INT NOT NULL,
    C3 VARCHAR(MAX)
)GO--================================--創(chuàng)建Delete觸發(fā)器CREATE TRIGGER TR_TB1_DELETEON dbo.TB1
AFTER DELETEASBEGINRETURN ENDGO--================================--插入5w數(shù)據(jù)INSERT INTO TB1(C2)SELECT TOP(5000) 1 AS C2 FROM sys.all_columns TGO 10--================================--查看表TB1使用的頁DBCC TRACEON(3604)GODBCC IND('tempdb','TB1',1)GO--================================--刪除一半的數(shù)據(jù)DELETE FROM  dbo.TB1WHERE ID%2=0GO--================================--查看表TB1使用的頁DBCC TRACEON(3604)GODBCC IND('tempdb','TB1',1)GO

PS： 如果表中不存在LOB或者VARCHAR(MAX)之類的大字段，不存在ROW_OVERFLOW數(shù)據(jù)頁，則SQL Server不會(huì)為每行增加14byte的行版本存儲(chǔ)指針

--==============================================================

--額外補(bǔ)充

1. 如果使用Merge并且設(shè)置了INSERT/DELETE/UPDATE方法，那么即使沒有滿足條件的數(shù)據(jù)進(jìn)行INSERT/DELETE/UPDATE,也會(huì)觸發(fā)INSERT/DELETE/UPDATE相關(guān)的觸發(fā)器。

 

--==================================================

新一年，換換口味，來點(diǎn)萌妹子吧！

網(wǎng)站標(biāo)題：雜談--DML觸發(fā)器學(xué)習(xí)
轉(zhuǎn)載源于：http://www.chinadenli.net/article20/igpjjo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供企業(yè)建站、網(wǎng)站導(dǎo)航、網(wǎng)站內(nèi)鏈、搜索引擎優(yōu)化、小程序開發(fā)、全網(wǎng)營銷推廣

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)