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

MySQL 觸發器語法詳解：BEFORE/AFTER、NEW/OLD、跨表同步實戰範例

編輯：MySQL 資料庫相關知識

觸發器基礎概念

**觸發時機**：BEFORE (寫入前驗證/修改) / AFTER (寫入後聯動/日誌)。
**觸發事件**：INSERT / UPDATE / DELETE。
**行級觸發**：FOR EACH ROW，每影響一行執行一次 (MySQL 僅支援行級)。
**特殊變數**：NEW.col (新值，INSERT/UPDATE 可用) / OLD.col (舊值，UPDATE/DELETE 可用)。

SQL Server vs MySQL 語法差異

特性	SQL Server	MySQL
虛擬表	`inserted`, `deleted`	`NEW`, `OLD` (逐行)
多行處理	集合式	逐行迴圈
結束符	批次分隔符 GO	`DELIMITER` 自定義
建立語法	`CREATE TRIGGER ... ON table FOR INSERT AS ...`	`CREATE TRIGGER ... BEFORE INSERT ON table FOR EACH ROW BEGIN ... END`

MySQL 完整範例：刪除客戶同步刪除報價


-- 建表
CREATE TABLE customer_info (
  customer_id INT PRIMARY KEY,
  name VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE ego_price (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT,
  price DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customer_info(customer_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 觸發器：AFTER DELETE (外鍵 CASCADE 已自動處理，此處示範邏輯)
DELIMITER $$
CREATE TRIGGER trg_customer_delete_sync
AFTER DELETE ON customer_info
FOR EACH ROW
BEGIN
  -- 手動同步刪除 (若無外鍵或需額外邏輯)
  DELETE FROM ego_price WHERE customer_id = OLD.customer_id;
  
  -- 記錄審計日誌
  INSERT INTO audit_log (table_name, action, pk_value, created_at)
  VALUES ('customer_info', 'DELETE', OLD.customer_id, NOW());
END$$
DELIMITER ;

BEFORE 觸發器：資料驗證與自動填充


DELIMITER $$
CREATE TRIGGER trg_order_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
  -- 自動生成訂單號
  IF NEW.order_no IS NULL OR NEW.order_no = '' THEN
    SET NEW.order_no = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), LPAD(FLOOR(RAND()*10000), 4, '0'));
  END IF;
  
  -- 業務規則驗證
  IF NEW.amount <= 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '訂單金額必須大於 0';
  END IF;
  
  -- 修正異常資料
  IF NEW.discount < 0 THEN SET NEW.discount = 0; END IF;
  IF NEW.discount > 1 THEN SET NEW.discount = 1; END IF;
END$$
DELIMITER ;

UPDATE 觸發器：NEW vs OLD 實戰


DELIMITER $$
CREATE TRIGGER trg_product_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  -- 價格變動才記錄
  IF NEW.price <> OLD.price THEN
    INSERT INTO price_history (product_id, old_price, new_price, changed_at)
    VALUES (OLD.id, OLD.price, NEW.price, NOW());
  END IF;
  
  -- 庫存歸零觸發下架
  IF NEW.stock = 0 AND OLD.stock > 0 THEN
    UPDATE products SET status = 'offline' WHERE id = NEW.id;
  END IF;
END$$
DELIMITER ;

管理與監控


-- 查看觸發器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA='db_name';

-- 刪除觸發器
DROP TRIGGER IF EXISTS trg_name;

-- 權限：需要 TRIGGER 權限 (SUPER 權限隱含)
GRANT TRIGGER ON db.* TO 'user'@'%';

效能與避坑指南

輕量級邏輯：觸發器在同一事務內執行，複雜邏輯會拉長鎖持有時間，增加死鎖風險。
避免跨庫/跨表大量寫入：若需大量聯動，改用應用層事件發布/訊息佇列非同步處理。
不可呼叫會隱含提交的語句：START TRANSACTION, COMMIT, LOCK TABLES, LOAD DATA 等在觸發器中禁止。
遞歸觸發防護：觸發器內修改同表可能再次觸發，使用會話變數標記 IF @skip_trigger IS NULL THEN SET @skip_trigger=1; ... END IF;。
備份還原：mysqldump --triggers 預設導出，還原時需確保權限。