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

MySQL 觸發器操作本表資料正確姿勢：NEW 伪列直接賦值避免 1442 錯誤完整實戰

編輯：MySQL 資料庫相關知識

觸發器操作本表資料的核心陷阱

MySQL 觸發器在 BEFORE INSERT/UPDATE 階段操作觸發表自身的資料時，嚴禁使用 UPDATE 語句，否則拋出錯誤：

ERROR 1442 (HY000): Can't update table 'dept' in stored function/trigger 
because it is already used by statement which invoked this stored function/trigger.

原因：觸發器執行時，表已被觸發語句鎖定/使用，無法在同一事務中再次對該表執行 DML。

錯誤示範

DELIMITER |
CREATE TRIGGER replaceGangcn
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
    -- 錯誤：嘗試 UPDATE 觸發表自身
    UPDATE dept SET name = REPLACE(NEW.name, '-', ' ');
END |
DELIMITER ;

正確姿勢：直接賦值 NEW 伪列

DELIMITER |
CREATE TRIGGER replaceGangcn
BEFORE INSERT ON dept
FOR EACH ROW
BEGIN
    -- 正確：直接修改 NEW 伪列，資料寫入前生效
    SET NEW.name = REPLACE(NEW.name, '-', ' ');
END |
DELIMITER ;

核心規則總結

NEW / OLD 伪列完整語意

場景	正確做法	錯誤做法
BEFORE INSERT/UPDATE 修改本表當前行	`SET NEW.col = ...`	`UPDATE table SET col = ...`
BEFORE INSERT/UPDATE 讀取本表其他行	`SELECT ... FROM table WHERE ...`（允許讀）	無
AFTER INSERT/UPDATE 修改本表其他行	`UPDATE table SET ... WHERE ...`（允許寫其他行）	無
BEFORE DELETE 讀取被刪行	`SELECT ... FROM table WHERE ...` 用 `OLD.col`	無
AFTER DELETE 修改本表其他行	`UPDATE table SET ... WHERE ...`	無

觸發時機	NEW	OLD
BEFORE INSERT	可讀寫（新行值）	不可用
AFTER INSERT	可讀（已寫入值）	不可用
BEFORE UPDATE	可讀寫（新值）	可讀（舊值）
AFTER UPDATE	可讀（已更新值）	可讀（舊值）
BEFORE DELETE	不可用	可讀（被刪值）
AFTER DELETE	不可用	可讀（被刪值）

進階實戰範例

-- 1. 插入前自動生成代碼：前綴 + 序號
CREATE TRIGGER gen_code_before_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    DECLARE next_seq INT;
    SELECT COALESCE(MAX(CAST(SUBSTRING(code, 4) AS UNSIGNED)), 0) + 1
    INTO next_seq FROM products WHERE code LIKE 'PRD-%';
    SET NEW.code = CONCAT('PRD-', LPAD(next_seq, 6, '0'));
END;

-- 2. 更新前記錄變更日誌（寫入另一張表）
CREATE TRIGGER log_price_change
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price <> OLD.price THEN
        INSERT INTO price_log (product_id, old_price, new_price, changed_at)
        VALUES (OLD.id, OLD.price, NEW.price, NOW(6));
    END IF;
END;

-- 3. 刪除前歸檔資料（寫入歸檔表）
CREATE TRIGGER archive_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO orders_archive SELECT * FROM orders WHERE id = OLD.id;
END;

-- 4. 複雜驗證：插入前檢查業務規則
CREATE TRIGGER validate_stock_before_insert
BEFORE INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE available INT;
    SELECT stock INTO available FROM products WHERE id = NEW.product_id;
    IF available < NEW.quantity THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
    END IF;
END;

觸發器效能與最佳實踐

輕量化：觸發器在每行觸發，避免複雜查詢、迴圈、外部呼叫
避免遞歸：觸發器中修改同表觸發同類型觸發器 → 無限遞歸。用會話變數標記：IF @trig_flag IS NULL THEN SET @trig_flag=1; ... END IF;
事務一致性：觸發器與觸發語句在同一事務，觸發器拋錯 → 整體回滾
複寫影響：行複寫 (RBR) 下觸發器在從庫不執行（主庫已產生行事件），語句複寫 (SBR) 在從庫重放觸發器。建議 RBR + 主庫觸發器
除錯技巧：INSERT INTO debug_log(msg) VALUES(CONCAT('TRIGGER: ', NEW.id)); 追蹤執行