Trigger 觸發器 (SQL Trigger)

Trigger（觸發器）是一種特殊的預存程序，當資料表發生特定事件（INSERT、UPDATE、DELETE）時會自動執行。觸發器常用於自動記錄變更歷史、維護資料完整性、同步更新相關資料等。

觸發器的類型

依觸發時機分類：

• BEFORE：在資料變更之前執行（可修改即將寫入的資料）
• AFTER：在資料變更之後執行
• INSTEAD OF：取代原本的操作（常用於 View）

依觸發事件分類：

• INSERT：新增資料時觸發
• UPDATE：更新資料時觸發
• DELETE：刪除資料時觸發

MySQL 語法

建立觸發器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 觸發器邏輯
END;

範例：記錄異動歷史

-- 先建立歷史記錄表
CREATE TABLE products_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_at DATETIME
);

-- 建立觸發器
DELIMITER //
CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO products_history (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
END//
DELIMITER ;

在 MySQL 觸發器中：

• OLD 代表變更前的資料（UPDATE、DELETE 可用）
• NEW 代表變更後的資料（INSERT、UPDATE 可用）

SQL Server 語法

CREATE TRIGGER trigger_name
ON table_name
{AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
AS
BEGIN
    -- 觸發器邏輯
END;

範例

CREATE TRIGGER log_price_change
ON products
AFTER UPDATE
AS
BEGIN
    INSERT INTO products_history (product_id, old_price, new_price, changed_at)
    SELECT d.id, d.price, i.price, GETDATE()
    FROM deleted d
    JOIN inserted i ON d.id = i.id
    WHERE d.price != i.price;
END;

SQL Server 使用虛擬表：

• inserted：新增或更新後的資料
• deleted：刪除或更新前的資料

PostgreSQL 語法

PostgreSQL 需要先建立函數，再建立觸發器：

-- 建立觸發器函數
CREATE OR REPLACE FUNCTION log_price_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO products_history (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 建立觸發器
CREATE TRIGGER price_change_trigger
AFTER UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION log_price_change();

查看與刪除觸發器

MySQL

-- 查看觸發器
SHOW TRIGGERS;

-- 刪除觸發器
DROP TRIGGER trigger_name;

SQL Server

-- 查看觸發器
SELECT * FROM sys.triggers;

-- 刪除觸發器
DROP TRIGGER trigger_name;

-- 停用觸發器
DISABLE TRIGGER trigger_name ON table_name;

PostgreSQL

-- 查看觸發器
SELECT * FROM information_schema.triggers;

-- 刪除觸發器
DROP TRIGGER trigger_name ON table_name;

常見用途

1. 自動記錄變更歷史：追蹤誰在什麼時候改了什麼
2. 維護衍生欄位：自動計算並更新統計欄位
3. 資料驗證：在 BEFORE 觸發器中檢查資料
4. 級聯更新：當主表變更時同步更新相關資料
5. 通知機制：當特定條件符合時發送通知