您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 觸發器管理實戰：從建立到審計日誌的完整應用範例（進階版）

編輯：MySQL 資料庫相關知識

觸發器核心價值

資料層強制約束：無論何種管道寫入（應用、ETL、手動 SQL），邏輯必然執行。
審計追蹤：自動記錄誰、何時、改了什麼、舊值新值。
衍生資料同步：統計欄位、快照表、搜尋索引自動維護。

完整建立語法（含多陳述式）

DELIMITER //
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 宣告區域變數
    DECLARE v_count INT DEFAULT 0;
    
    -- 條件邏輯
    IF NEW.status = 'active' THEN
        UPDATE stats SET active_count = active_count + 1;
    END IF;
    
    -- 迴圈（罕用，慎用）
    WHILE v_count < 10 DO
        -- ...
        SET v_count = v_count + 1;
    END WHILE;
    
    -- 例外處理（MySQL 觸發器不支援 TRY/CATCH，靠 SQLSTATE 檢查）
    -- 錯誤會導致整個交易回滾
END//
DELIMITER ;

進階實戰：多表層疊同步與防遞迴

-- 場景：orders 表新增訂單 → 自動更新 customers.total_orders、products.sales_count
-- 風險：若 customers 也有觸發器更新 orders → 遞迴

-- 解法：使用會話變數標記觸發器來源
CREATE TRIGGER trg_orders_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    IF @trigger_source IS NULL OR @trigger_source <> 'orders_sync' THEN
        SET @trigger_source = 'orders_sync';
        
        UPDATE customers 
        SET total_orders = total_orders + 1, 
            last_order_at = NEW.created_at
        WHERE id = NEW.customer_id;
        
        UPDATE products 
        SET sales_count = sales_count + NEW.quantity
        WHERE id = NEW.product_id;
        
        SET @trigger_source = NULL;
    END IF;
END;

JSON 結構化審計日誌（MySQL 5.7+）

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(64) NOT NULL,
    pk_value VARCHAR(128) NOT NULL,  -- 主鍵值，支援複合鍵 JSON
    operation ENUM('INSERT','UPDATE','DELETE') NOT NULL,
    old_data JSON,
    new_data JSON,
    changed_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    changed_by VARCHAR(128),
    INDEX idx_table_pk (table_name, pk_value),
    INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB;

-- 通用審計觸發器產生器（應用層動態建立）
-- 範例：products 表
DELIMITER //
CREATE TRIGGER trg_products_audit_ins
AFTER INSERT ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, pk_value, operation, new_data, changed_by)
    VALUES ('products', CAST(NEW.id AS CHAR), 'INSERT', 
        JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'price', NEW.price, 'stock', NEW.stock),
        CURRENT_USER());
END//

CREATE TRIGGER trg_products_audit_upd
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, pk_value, operation, old_data, new_data, changed_by)
    VALUES ('products', CAST(OLD.id AS CHAR), 'UPDATE',
        JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'price', OLD.price, 'stock', OLD.stock),
        JSON_OBJECT('id', NEW.id, 'name', NEW.name, 'price', NEW.price, 'stock', NEW.stock),
        CURRENT_USER());
END//

CREATE TRIGGER trg_products_audit_del
BEFORE DELETE ON products
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, pk_value, operation, old_data, changed_by)
    VALUES ('products', CAST(OLD.id AS CHAR), 'DELETE',
        JSON_OBJECT('id', OLD.id, 'name', OLD.name, 'price', OLD.price, 'stock', OLD.stock),
        CURRENT_USER());
END//
DELIMITER ;

查詢審計日誌實用 SQL

-- 某商品完整變更歷程
SELECT * FROM audit_log 
WHERE table_name = 'products' AND pk_value = '123'
ORDER BY changed_at;

-- 某用戶今日所有操作
SELECT * FROM audit_log 
WHERE changed_by = 'app_user@%' AND DATE(changed_at) = CURDATE()
ORDER BY changed_at DESC;

-- 異常價格變更（漲幅 > 50%）
SELECT * FROM audit_log 
WHERE table_name = 'products' 
  AND operation = 'UPDATE'
  AND JSON_EXTRACT(new_data, '$.price') > JSON_EXTRACT(old_data, '$.price') * 1.5;