SQL Server 觸發器中 INSERTED/DELETED 虛擬表判斷 DML 操作類型

核心判斷邏輯

單一觸發器處理 INSERT/UPDATE/DELETE 時，透過 inserted 與 deleted 虛擬表是否有資料判斷操作類型：

T-SQL 實作範例

CREATE TRIGGER tr_T_A ON T_A
FOR INSERT, UPDATE, DELETE
AS
BEGIN
    -- 判斷 INSERT
    IF EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'INSERT 操作';
        -- 插入邏輯
    END
    
    -- 判斷 UPDATE
    ELSE IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
    BEGIN
        PRINT 'UPDATE 操作';
        -- 更新邏輯：inserted = 新值, deleted = 舊值
    END
    
    -- 判斷 DELETE
    ELSE IF EXISTS (SELECT 1 FROM deleted) AND NOT EXISTS (SELECT 1 FROM inserted)
    BEGIN
        PRINT 'DELETE 操作';
        -- 刪除邏輯
    END
END

實戰案例：條件式稽核記錄

CREATE TRIGGER [risk].[Entry_Head_port_Exchange_Trigger]
ON [RiskH800].[risk].[ENTRY_HEAD]
AFTER INSERT, UPDATE
AS
BEGIN
    DECLARE @COUNT INT, @MANUAL_NO_COUNT INT;
    
    -- UPDATE 情況：inserted 與 deleted 都有資料
    IF EXISTS (SELECT 1 FROM INSERTED) AND EXISTS (SELECT 1 FROM DELETED)
    BEGIN
        SELECT @COUNT = COUNT(*) 
        FROM risk.ENTRY_HEAD_port_EXCHANGE 
        WHERE ENTRY_ID = (SELECT ENTRY_ID FROM DELETED);
        
        SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) 
        FROM DELETED 
        WHERE SUBSTRING(MANUAL_NO,1,1) IN ('B','C');
        
        IF @COUNT <= 0 AND @MANUAL_NO_COUNT > 0
            INSERT INTO risk.ENTRY_HEAD_port_EXCHANGE (ENTRY_ID, CREATE_DATE)
            SELECT ENTRY_ID, GETDATE() FROM DELETED;
    END
    
    -- INSERT 情況：只有 inserted 有資料
    ELSE IF EXISTS (SELECT 1 FROM INSERTED)
    BEGIN
        SELECT @COUNT = COUNT(*) 
        FROM risk.ENTRY_HEAD_port_EXCHANGE 
        WHERE ENTRY_ID = (SELECT ENTRY_ID FROM INSERTED);
        
        SELECT @MANUAL_NO_COUNT = COUNT(MANUAL_NO) 
        FROM INSERTED 
        WHERE SUBSTRING(MANUAL_NO,1,1) IN ('B','C');
        
        IF @COUNT <= 0 AND @MANUAL_NO_COUNT > 0
            INSERT INTO risk.ENTRY_HEAD_port_EXCHANGE (ENTRY_ID, CREATE_DATE)
            SELECT ENTRY_ID, GETDATE() FROM INSERTED;
    END
END

進階技巧

• 多筆資料：inserted/deleted 可能包含多列，避免用 = (SELECT ...)，改用 JOIN 或 IN
• UPDATE 的 inserted/deleted 對應：同序號列對應同一筆資料的新/舊值
• COLUMNS_UPDATED()：判斷特定欄位是否被更新 (位元遮罩)
• UPDATE()：IF UPDATE(ColumnName) 判斷單一欄位

效能建議

• 觸發器邏輯越簡單越好，複雜處理移至非同步程序
• 避免觸發器中跨資料庫/連結伺服器操作
• 使用 SET NOCOUNT ON 減少網路往返
• 大量異動表謹慎使用觸發器