MySQL 觸發器踩坑：Unknown column 錯誤與 NEW/OLD 別名規範

MySQL 觸發器踩坑：Unknown column 錯誤與 NEW/OLD 別名規範

觸發器中引用觸發表欄位時，必須使用 NEW. 或 OLD. 前綴，直接寫欄位名會報 [1054] Unknown column 'xxx' in 'where clause'。本文以實際報錯案例說明正確寫法。

錯誤範例

CREATE TRIGGER `delete_actdata` BEFORE DELETE ON `actdata`
FOR EACH ROW
BEGIN
    DELETE FROM `great` WHERE `great`.`actid` = `actid`;      -- ❌ 錯誤
    DELETE FROM `join`  WHERE `join`.`actid`  = `actid`;      -- ❌
    DELETE FROM `get`   WHERE `get`.`actid`   = `actid`;      -- ❌
    ...
END;

報錯：Unknown column 'actid' in 'where clause' —— 觸發器上下文中無裸欄位名 actid。

正確寫法

CREATE TRIGGER `delete_actdata` BEFORE DELETE ON `actdata`
FOR EACH ROW
BEGIN
    DELETE FROM `great` WHERE `great`.`actid` = OLD.`actid`;   -- ✅ 刪除前舊值
    DELETE FROM `join`  WHERE `join`.`actid`  = OLD.`actid`;   -- ✅
    DELETE FROM `get`   WHERE `get`.`actid`   = OLD.`actid`;   -- ✅
    DELETE FROM `signup` WHERE `signup`.`actid` = OLD.`actid`; -- ✅
    DELETE FROM `vote_option` WHERE `vote_option`.`actid` = OLD.`actid`; -- ✅
    DELETE FROM `vote_users` WHERE `vote_users`.`actid` = OLD.`actid`;   -- ✅
END;

核心規則速記

避坑檢查清單

• ✅ 所有觸發表欄位引用均加 NEW./OLD.。
• ✅ 目標表欄位加反引號 `col`，避免保留字衝突。
• ✅ BEFORE DELETE 僅能用 OLD，不可用 NEW。
• ✅ 多表級聯刪除時，外鍵 ON DELETE CASCADE 往往比觸發器更簡潔高效。
• ✅ 觸發器內避免複雜邏輯/事務，影響主表寫入效能。