SQL Server 2000 觸發器多筆資料批次處理完整範本

觸發器設計核心原則

觸發器永遠要假設 INSERTED/DELETED 包含多筆資料。 批次匯入、合併陳述式、應用程式批次更新都會觸發多筆觸發。單筆處理邏輯會導致資料不一致或效能災難。

標準多筆處理範本架構

CREATE TRIGGER [TRI_TableName] ON [TableName]
FOR INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;  -- 減少網路傳輸

    -- 1. 宣告變數
    DECLARE 
        @CHECK INT,                    -- 檢查標記
        @TODAY DATETIME,               -- 共用變數
        @Field1 INT, @Field2 INT,      -- 基表欄位變數
        @Field3 VARCHAR(20), @FieldN INT,
        @Table1_Field1 INT,            -- 跨表查詢變數
        @TableN_FieldM INT;

    -- 2. 定義游標遍歷 INSERTED
    DECLARE cur_Trigger CURSOR FOR
        SELECT Field1, Field2, Field3, FieldN
        FROM INSERTED;  -- 關鍵：從 INSERTED 讀取所有異動行

    -- 3. 開啟游標並取第一筆
    SET @TODAY = CONVERT(VARCHAR(10), GETDATE(), 120);
    OPEN cur_Trigger;
    FETCH NEXT FROM cur_Trigger INTO @Field1, @Field2, @Field3, @FieldN;

    -- 4. 迴圈處理每一筆
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        --------------------------------------------------------
        -- 業務規則區塊（每筆獨立判斷）
        --------------------------------------------------------
        
        -- 規則1：符合條件直接跳過此筆
        IF (/* 條件1 */)
        BEGIN
            FETCH NEXT FROM cur_Trigger INTO @Field1, @Field2, @Field3, @FieldN;
            CONTINUE;
        END

        -- 規則2：嚴重錯誤 → 回滾整批交易
        IF (/* 條件2 */)
        BEGIN
            RAISERROR('業務規則違反：xxx', 16, 1);
            ROLLBACK TRANSACTION;
            CLOSE cur_Trigger;
            DEALLOCATE cur_Trigger;
            RETURN;
        END

        -- 規則3：跨表檢查與值衍生
        SELECT TOP 1 @Table1_Field1 = Field1 
        FROM Table1 
        WHERE /* 關聯條件 */;
        
        IF (@Table1_Field1 IS NOT NULL)
        BEGIN
            -- 依跨表結果更新當前處理值
            SET @Field1 = @Table1_Field1;
        END

        -- 規則4：條件式更新基表（避免無異動寫入）
        UPDATE TableName
        SET 
            Field1 = @Field1,
            Field2 = @Field2
        WHERE 
            PrimaryKey = @PrimaryKey
            AND (ISNULL(Field1,'') <> ISNULL(@Field1,'') 
                 OR ISNULL(Field2,0) <> ISNULL(@Field2,0));

        -- 取下一筆
        FETCH NEXT FROM cur_Trigger INTO @Field1, @Field2, @Field3, @FieldN;
    END

    -- 5. 清理資源
    CLOSE cur_Trigger;
    DEALLOCATE cur_Trigger;
    SET NOCOUNT OFF;
END
GO

關鍵設計細節解析

效能優化替代方案

游標雖符合邏輯但有開銷，現代版本（SQL 2005+）建議優先考慮：

1. 集合式操作：直接 JOIN INSERTED 進行 UPDATE/INSERT，無需游標
2. MERGE 陳述式：單一陳述式處理 INSERT/UPDATE/DELETE
3. INSTEAD OF 觸發器：完全接管 DML 邏輯，適用複雜驗證

集合式重寫範例（SQL 2005+）

CREATE TRIGGER TRI_TableName_SetBased ON TableName
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 一次性處理所有異動行
    UPDATE t
    SET t.Field1 = i.Field1,
        t.Field2 = i.Field2,
        t.ModifiedDate = GETDATE()
    FROM TableName t
    INNER JOIN INSERTED i ON t.PrimaryKey = i.PrimaryKey
    WHERE ISNULL(t.Field1,'') <> ISNULL(i.Field1,'')
       OR ISNULL(t.Field2,0) <> ISNULL(i.Field2,0);
       
    -- 跨表驗證失敗整批回滾
    IF EXISTS (
        SELECT 1 FROM INSERTED i
        WHERE NOT EXISTS (SELECT 1 FROM RefTable r WHERE r.Key = i.RefKey)
    )
    BEGIN
        RAISERROR('參考資料不存在', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END
END

版本遷移建議

• SQL 2000 專案維護期內：使用本文游標範本，確保正確性
• 新專案或升級專案：強制改用集合式寫法，效能提升 10~100 倍
• 嚴禁在觸發器中發送郵件、呼叫 Web Service、執行長時間運算