SQL Server 觸發器其實支援多行：正確處理 inserted/deleted 虛擬表

誤區

許多開發者以為 SQL Server 觸發器只能逐行觸發，實際上 inserted 與 deleted 虛擬表包含**該語句影響的所有行**。只需正確迭代即可實現真正的集合級觸發邏輯。

完整範例：刪除觸發器歸檔至歷史表

1. 建立測試表

CREATE TABLE tbl_src (id INT PRIMARY KEY, val VARCHAR(100));
CREATE TABLE tbl_hist (id INT PRIMARY KEY, val VARCHAR(100), del_dt DATETIME DEFAULT GETDATE());

2. 插入 100 筆測試資料

DECLARE @i INT = 1;
WHILE @i <= 100
BEGIN
    INSERT INTO tbl_src (id, val) VALUES (@i, CAST(@i AS VARCHAR));
    SET @i += 1;
END

3. 建立 FOR DELETE 觸發器 (游標逐行寫入歷史表)

CREATE TRIGGER trg_del_archive ON tbl_src
FOR DELETE
AS
BEGIN
    DECLARE @id INT, @val VARCHAR(100);

    DECLARE cur_del CURSOR LOCAL FORWARD_ONLY FOR
        SELECT id, val FROM deleted;  -- 包含所有被刪除的行

    OPEN cur_del;
    FETCH NEXT FROM cur_del INTO @id, @val;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO tbl_hist (id, val) VALUES (@id, @val);
        FETCH NEXT FROM cur_del INTO @id, @val;
    END

    CLOSE cur_del;
    DEALLOCATE cur_del;
END;

4. 驗證：一次刪除多行

DELETE FROM tbl_src WHERE id BETWEEN 10 AND 20;  -- 刪除 11 行
SELECT * FROM tbl_hist;  -- 11 筆歷史記錄完整保留

關鍵要點

• inserted / deleted 是**結果集**，非單行；可直接 SELECT ... FROM inserted 做集合操作 (如 INSERT INTO hist SELECT * FROM deleted)，**完全不需要游標**。
• 游標範例僅為示範行級邏輯 (如需呼叫外部 API、複雜逐行判斷)；純資料歸檔請用集合寫法：
  INSERT INTO tbl_hist (id, val) SELECT id, val FROM deleted;
• 觸發器內避免長時間運行、外部呼叫、動態 SQL，防止鎖升級與死鎖。
• INSTEAD OF 觸發器同樣可存取 inserted/deleted，邏輯一致。

最佳實踐模板

CREATE TRIGGER trg_name ON tbl
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;

    -- 1. 早期退出：無影響行直接返回
    IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
        RETURN;

    -- 2. 集合式業務邏輯
    INSERT INTO audit_log (tbl_name, op_type, pk_val, mod_dt)
    SELECT 'tbl',
           CASE WHEN EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted) THEN 'U'
                WHEN EXISTS(SELECT 1 FROM inserted) THEN 'I'
                ELSE 'D' END,
           COALESCE(i.pk, d.pk),
           GETDATE()
    FROM   inserted i
    FULL JOIN deleted d ON i.pk = d.pk;
END;

結語

SQL Server 觸發器**原生支援多行**，關鍵在於正確理解 inserted/deleted 為多行集合。優先使用集合操作，僅在極少數逐行不可避免場景才引入游標，即能寫出高效、健壯的觸發器。