SQL Server 觸發器實戰詳解：INSERT/UPDATE/DELETE 範例與 INSERTED/DELETED 表深度解析

觸發器基礎認知

• 定義： 綁定於資料表/檢視表的特殊預存程序，由 INSERT/UPDATE/DELETE 事件自動觸發，不可显式呼叫。
• 兩張虛擬表： inserted（新資料）、deleted（舊資料），結構與觸發表完全一致，存在於 tempdb，觸發結束自動銷毀。

虛擬表資料對應規則

UPDATE 本質 = DELETE + INSERT，因此同時填充兩張表。

範例一：同步學號變更與級聯刪除借書記錄

場景

• Student(StudentID PK, ...)
• BorrowRecord(BorrowID PK, StudentID FK, BorrowDate, ReturnDate, ...)

UPDATE 觸發器：學號異動同步至借書記錄

CREATE TRIGGER trg_Student_Update
ON Student
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF UPDATE(StudentID)          -- 僅當 StudentID 真正被修改時執行
    BEGIN
        UPDATE br
        SET br.StudentID = i.StudentID
        FROM BorrowRecord br
        INNER JOIN deleted d ON br.StudentID = d.StudentID
        INNER JOIN inserted i ON d.StudentID = i.StudentID;  -- 或直接 JOIN inserted
    END
END;
GO

DELETE 觸發器：學生畢業刪除時級聯清除借書記錄

CREATE TRIGGER trg_Student_Delete
ON Student
AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    DELETE br
    FROM BorrowRecord br
    INNER JOIN deleted d ON br.StudentID = d.StudentID;
END;
GO

範例二：卷菸庫存/銷售業務規則強制執行

表結構

• 庫存表(品牌 PK, 數量, 單價, 金額 = 數量 * 單價)
• 銷售表(品牌 PK, 購貨商, 數量, 單價, 金額 = 數量 * 單價)

觸發器 1：庫存表 INSERT 強制金額 = 數量 × 單價

CREATE TRIGGER trg_Stock_Insert
ON 庫存表
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE s
    SET 金額 = 數量 * 單價
    FROM 庫存表 s
    INNER JOIN inserted i ON s.品牌 = i.品牌;
END;
GO

觸發器 2：銷售表 INSERT 扣減庫存、驗證庫存足夠

CREATE TRIGGER trg_Sale_Insert
ON 銷售表
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @品牌 NVARCHAR(40), @銷售數量 INT;
    SELECT @品牌 = 品牌, @銷售數量 = 銷售數量 FROM inserted;

    -- 1. 驗證品牌存在且庫存 > 0
    IF NOT EXISTS (SELECT 1 FROM 庫存表 WHERE 品牌 = @品牌 AND 數量 > 0)
    BEGIN
        RAISERROR('錯誤：該品牌無庫存或庫存不足，無法銷售。', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END

    -- 2. 強制銷售金額 = 數量 × 單價
    UPDATE 銷售表
    SET 金額 = 銷售數量 * 單價
    WHERE 品牌 = @品牌;

    -- 3. 扣減庫存
    UPDATE 庫存表
    SET 數量 = 數量 - @銷售數量,
        金額 = (數量 - @銷售數量) * 單價
    WHERE 品牌 = @品牌;
END;
GO

測試腳本與預期結果

-- 初始化庫存
INSERT INTO 庫存表 VALUES ('紅塔山', 100, 12, 1200), ('玉溪', 0, 30, 0);

-- 正常銷售
INSERT INTO 銷售表 VALUES ('紅塔山', '商A', 10, 12, 120);  -- 庫存變 90，金額 1080

-- 銷售金額輸入錯誤（觸發器自動修正）
INSERT INTO 銷售表 VALUES ('紅塔山', '商B', 5, 12, 50);   -- 金額自動改為 60

-- 銷售無庫存品牌
INSERT INTO 銷售表 VALUES ('紅河', '商C', 10, 60, 600);   -- RAISERROR + ROLLBACK

-- 銷售庫存為 0 品牌
INSERT INTO 銷售表 VALUES ('玉溪', '商D', 10, 30, 300);   -- RAISERROR + ROLLBACK

觸發器進階語法與限制

CREATE TRIGGER trigger_name
ON { table | view }
[WITH ENCRYPTION]
{ FOR | AFTER | INSTEAD OF } [INSERT] [,] [UPDATE] [,] [DELETE]
[NOT FOR REPLICATION]
AS
    sql_statement [...n]

• AFTER（預設）：約束檢查通過後執行，僅限表。
• INSTEAD OF：取代觸發動作，可用於檢視表實現可更新檢視。
• 遞迴/巢狀： 最多 32 層，可用 sp_configure 'nested triggers', 0 關閉。
• 禁止語句： ALTER/CREATE/DROP DATABASE, RESTORE, RECONFIGURE, DISK 等。
• TRUNCATE TABLE 不會觸發 DELETE 觸發器。

管理指令速查

最佳實務建議

1. 業務邏輯優先放應用層/預存程序，觸發器僅作為「最後防線」或跨應用共享規則。
2. 避免觸發器中發送郵件、呼叫 Web Service、大量游標循環，導致鎖定時間過長。
3. 使用 SET NOCOUNT ON 減少網路封包。
4. 測試遞迴場景：觸發器修改同表 → 再次觸發 → 無限迴圈。
5. 文件化觸發器依賴關係，防止結構變更導致隱性故障。