雜談--DML觸發器學習

觸發器按類型分為三類：

1. DML 觸發器，在數據變更時觸發；

2. DDL 觸發器，在修改數據庫級別或實例級別對象時觸發；

3. Login 觸發器，在用戶登錄時觸發；

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

從功能來看，INSTEAD OF觸發器用來替換實際的數據修改操作，而AFTER觸發器用來在實際操作完成后進行后續操作。例如對于DELETE操作，如果我們期望只修改數據狀態來標示數據已被刪除而不是將數據從表中刪除，那么我們可以使用INSTEAD OF觸發器來實現；如果我們期望在刪除數據后在其他表記錄刪除操作的發生時間，那么我們可以使用AFTER觸發器來實現。

從執行來看，INSTEAD OF觸發器和AFTER觸發器的所處的執行時期不同，SQL Server中的觸發順序為：

1. 觸發INSTEAD OF觸發器

2. 觸發DEFAULT 約束

3. 觸發主鍵/唯一/CHECK約束

4. 觸發外鍵約束

5. 觸發AFTER 觸發器

因此如果期望修改操作順利執行而不觸發約束導致回滾的話，可以使用INSTEAD OF觸發器來將實現(在INSTEAD OF 觸發器中修改使數據滿足約束條件)。

因為INSTEAD OF 觸發器改寫了實際要發生的修改操作，因此每個表上每種修改類型(DELETE/INSERT/UPDATE)只能有一個INSTEAD OF 觸發器；而AFTER 觸發器沒有類似限制，可以創建多個AFTER觸發器。

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

指定最先執行的AFTER觸發器：

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

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

PS:上面說的Fire only once只是針對執行的SQL語句，并不包含該觸發器內部的SQL語句

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

嵌套(Nested)觸發器：在TB1和TB2上創建觸發器，當TB1上TR_TB1_INSERT1被觸發時，TR_TB1_INSERT1中的語句執行導致TB2上TR_TB2_INSERT1被觸發

--================================--在TB1和TB2上創建觸發器，當TB1上TR_TB1_INSERT1被--觸發時，TR_TB1_INSERT1中的語句執行導致TB2上--TR_TB2_INSERT1被觸發，即屬于Nested觸發器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)觸發器可分為直接遞歸(Directed Recursive)觸發器和間接遞歸(Indirect Recursive)觸發器

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

在TB1創建觸發器，當TB1上TR_TB1_INSERT1被觸發時，TR_TB1_INSERT1中的語句執行導致TB1上TR_TB1_INSERT1再次被觸發

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

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

在TB1和TB2上創建觸發器，當TB1上TR_TB1_INSERT1被觸發時，TR_TB1_INSERT1中的語句執行導致TB2上TR_TB2_INSERT1被觸發，而TB2上TR_TB2_INSERT1的觸發器執行時又導致TB1上TR_TB1_INSERT1被觸發，從而引發循環。

--================================--在TB1和TB2上創建觸發器，當TB1上TR_TB1_INSERT1被--觸發時，TR_TB1_INSERT1中的語句執行導致TB2上--TR_TB2_INSERT1被觸發，而TB2上TR_TB2_INSERT1的--觸發器執行時又導致TB1上TR_TB1_INSERT1被觸發，從而--引發循環，即間接遞歸(Indirect Recursive)觸發器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)觸發器在sys.configurations中配置，默認開啟

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

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

行版本(Row version)

在SQL Server多中功能中使用到row version來保留多個版本的數據，這些功能有：

1. MARS

2. Triggers

3. Online indexing

4. Optimistic Transaction Isolation Levels

因此在使用觸發器時，應考慮到可能會為表增加額外14bytes的行版本存儲指針

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

測試代碼：

USE tempdb--================================--創建測試表DROP TABLE TB1GOCREATE TABLE TB1
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    C2 INT NOT NULL,
    C3 VARCHAR(MAX)
)GO--================================--創建Delete觸發器CREATE TRIGGER TR_TB1_DELETEON dbo.TB1
AFTER DELETEASBEGINRETURN ENDGO--================================--插入5w數據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--================================--刪除一半的數據DELETE FROM  dbo.TB1WHERE ID%2=0GO--================================--查看表TB1使用的頁DBCC TRACEON(3604)GODBCC IND('tempdb','TB1',1)GO

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

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

--額外補充

1. 如果使用Merge并且設置了INSERT/DELETE/UPDATE方法，那么即使沒有滿足條件的數據進行INSERT/DELETE/UPDATE,也會觸發INSERT/DELETE/UPDATE相關的觸發器。

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

新一年，換換口味，來點萌妹子吧！