萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2005 觸發器實戰:捕獲 UPDATE 變更前後值

觸發器設計目標

在資料表 E3_DICT_TABLE 上建立 AFTER UPDATE 觸發器,當 value 欄位被修改時,記錄變更前後值,並針對特定 id 執行業務邏輯(範例中僅列印)。

完整觸發器代碼(修正語法錯誤)

ALTER TRIGGER [dbo].[update_email_template]
ON [dbo].[E3_DICT_TABLE]
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 僅當 value 欄位實際更新時觸發
    IF UPDATE(value)
    BEGIN
        DECLARE @id VARCHAR(MAX);
        DECLARE @oldValue VARCHAR(MAX);
        DECLARE @newValue VARCHAR(MAX);

        -- 從 DELETED 取得舊值(假設單行更新,多行需用游標/JOIN)
        SELECT @id = id, @oldValue = value FROM DELETED;

        -- 從 INSERTED 取得新值
        SELECT @newValue = value FROM INSERTED WHERE id = @id;

        -- 業務邏輯範例
        IF @id = '1'
        BEGIN
            PRINT 'ID=1 舊值: ' + ISNULL(@oldValue, 'NULL');
            PRINT 'ID=1 新值: ' + ISNULL(@newValue, 'NULL');
        END
        ELSE IF @id = '2'
        BEGIN
            -- 擴充其他業務
            PRINT 'ID=2 已更新';
        END

        -- 實際應用建議:寫入稽核表、發送通知、同步快取等
        -- INSERT INTO AuditLog (TableName, PKValue, ColumnName, OldValue, NewValue, ModTime)
        -- VALUES ('E3_DICT_TABLE', @id, 'value', @oldValue, @newValue, GETDATE());
    END
END

關鍵技術點

  • INSERTED / DELETED 虛擬表:分別保存更新後/前的資料列,結構與基礎表相同。
  • UPDATE(column):判斷特定欄位是否在 SET 子句中出現(即使值未真正改變也會返回 TRUE)。
  • 多行更新處理:上述範例假設單行。批次更新時,需用 JOIN 或游標逐行處理:
-- 多行安全寫法
INSERT INTO AuditLog (TableName, PKValue, ColumnName, OldValue, NewValue, ModTime)
SELECT 'E3_DICT_TABLE', d.id, 'value', d.value, i.value, GETDATE()
FROM DELETED d
JOIN INSERTED i ON d.id = i.id
WHERE d.value <> i.value OR (d.value IS NULL AND i.value IS NOT NULL) OR (d.value IS NOT NULL AND i.value IS NULL);

效能與最佳實踐

  1. 避免觸發器中執行耗時操作(郵件、Web Service、跨資料庫),改用 Service Broker 或異步佇列。
  2. 使用 TRY...CATCH 包裹,防止觸發器錯誤導致原交易回滾。
  3. 嚴謹比對 NULL:ISNULL 或 NULLIF 處理空值比較。
  4. 文件化觸發器邏輯,避免隱性業務規則造成維護困境。

常見錯誤修正

原始代碼存在 BEGIN...END 巢狀錯誤、WHERE 子句誤置於 SELECT 之後、變數宣告位置不當等語法問題,已在上方修正版中修復。

copyright © 萬盛學電腦網 all rights reserved