MSSQL 的觸發程序 TRIGGER

關於 TRIGGER 本篇將討論以下幾個問題

1. 什麼是 DML、DDL、DQL？

2. 什麼是 TRIGGER？

3. 如何使用 TRIGGER？

4. 情境

5. 使用建議

測試環境：

MS SQL：SQL Server 2019 Linux

SSMS：Microsoft SQL Server Management Studio 18

1. 什麼是 DML、DDL、DQL？

你可能沒有聽過 DML、DDL、DQL 這些縮寫，不過只要工作上有使用到 MSSQL，那就肯定使用過只是不知道這些縮寫罷了，說明如下

• DML(Data Manipulation Language)：用於操作資料表內的資料(e.g. INSERT、UPDATE、DELETE)
• DDL(Data Definition Language)：用於操作資料表(e.g. CREATE、ALTER、DROP)
• DQL(Data Query Language)：用於資料查詢(e.g. SELECT)

本篇範例主要會說明如何建立在 DML 操作時會觸發的觸發程序(TRIGGER)。

2. 什麼是 TRIGGER？

MSDN 上說：「觸發程序是一種特殊的預存程序，其會在資料庫伺服器發生事件時自動執行。」

如同字面上的意思，在 INSERT、UPDATE、DELETE 等事件發生時，會觸發執行的程式。

3. 如何使用 TRIGGER？

本篇會以如何使用為主，實際運作細節會附上 MSDN 連結，有興趣的朋友可以深入研究。

1. Create Trigger (MSDN)
2. AFTER 觸發 (MSDN)
3. if 條件中的 UPDATE() (MSDN)
4. if 條件中的 EXIST() (MSDN)

CREATE TRIGGER [TRIGGER 的名稱]
   ON  [指定資料表]
   AFTER UPDATE, INSERT, DELETE -- 觸發事件
AS 
BEGIN
    -- 觸發後可寫 if/else 條件
    -- 下面 if 中包含兩個條件
    -- 1. UPDATE(欄位名稱)，不論 INSERT 或 UPDATE 嘗試成功與否，UPDATE() 都會傳回 TRUE
    -- 2. EXISTS 指定測試資料列是否存在的子查詢
    IF UPDATE([指定欄位]) AND EXISTS([子查詢])
    -- if 條件成立則執行
    BEGIN
        PRINT '使用 PRINT 印出想知道的資訊'
    END
    ELSE
    -- else 條件成立則執行
    BEGIN
        PRINT 'Nothing to do.'
    END
END

4. 情境

1. 資料表UserInfo中存有使用者資料，
2. 現在有一筆資料的DataCount被異動了
3. 希望藉由 Trigger 在 Update 事件發生時自動將異動的紀錄寫入UserLog資料表中

建立UserInfo & UserLog兩張資料表

USE [資料庫名稱]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[UserInfo](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nvarchar](50) NOT NULL,
    [DataCount] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserLog](
    [UserId] [int] NOT NULL,
    [DataCount] [int] NOT NULL,
    [CreateDate] [datetime] NOT NULL
) ON [PRIMARY]
GO

手動在UserInfo資料表中加入一筆資料

建立 Trigger UserInfo_UpdateTrigger

CREATE TRIGGER UserInfo_UpdateTrigger -- TRIGGER 的名稱
   ON  [UserInfo] -- 指定資料表
   AFTER UPDATE -- UPDATE 之後觸發，還有 INSERT, DELETE，多個則以 ',' 分隔
AS 
BEGIN
    -- 觸發後可寫 if/else 條件
    -- 下面 if 中包含兩個條件
    -- 1. UPDATE(欄位名稱)，不論 INSERT 或 UPDATE 嘗試成功與否，UPDATE() 都會傳回 TRUE
    -- 2. EXISTS 指定測試資料列是否存在的子查詢
    IF UPDATE([DataCount]) AND EXISTS(SELECT Count(1) FROM [UserInfo] WHERE DataCount > 0)
    -- if 條件成立則執行
    BEGIN
        DECLARE @userId INT;
        DECLARE @dataCount INT;
        PRINT 'Set log start.'
        -- 新增資料使用 inseted，刪除資料使用 deleted，更新資料(Update)的話則是都會有
        SELECT @userId=UserId, @dataCount=DataCount FROM deleted;
        INSERT INTO UserLog (UserId, DataCount, CreateDate)
        VALUES (@userId, @dataCount, GETDATE());
        -- 使用 PRINT 印出想知道的資訊
        PRINT 'Set log success.'
    END
    ELSE
    -- else 條件成立則執行
    BEGIN
        PRINT 'Nothing to do.'
    END
END

建立完成後可以在 SSMS 中資料表下的觸發程序中找到剛建立的 Trigger
右鍵可以開啟選單，進行修改、停用、刪除等操作

建立異動UserInfo中DataCount欄位的 Update

UPDATE UserInfo
SET DataCount = 20
WHERE UserId = 1

執行後可以看到兩次的「(1 個資料列受到影響)」
分別是 Insert UserLog & Update UserInfo 兩個操作

Set log start.

(1 個資料列受到影響)
Set log success.

(1 個資料列受到影響)

完成時間: 2021-02-11T14:39:19.2255724+08:00

可以看到UserLog中新增一筆異動前的紀錄

UserInfo中DataCount也更新為 20 了

5. 使用建議

雖然 Trigger 使用上很簡單，維護起來卻不是那麼容易，若是有多個複雜邏輯時，在除錯上就更是困難了，請謹慎評估後再使用。

另外建議不要在團隊不知情的情況下使用 Trigger，避免遇到問題時同事花了大把時間才發現原來資料寫入是在資料庫中觸發的，雖然 git 中不會有 commit 紀錄，使用前還是經由團隊開會後決議再使用比較保險。

總結

由於還是覺得 Trigger 維護上較不方便，我都只用在測試上，沒有在 Production 環境中使用過，所以是不是有什麼效能的坑或其他問題，也無法在此給各位建議。

參考資料

1. Create Trigger MSDN
2. AFTER 觸發 MSDN
3. if 條件中的 UPDATE() MSDN
4. if 條件中的 EXIST() MSDN

新手上路，若有錯誤還請告知，謝謝