萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 觸發器實戰範例:建立、BEFORE/AFTER、條件控制與跨表同步

基本語法

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

範例 1:最簡單觸發器 — 設定固定值

CREATE TABLE `t` (`s1` INT);

DELIMITER //

CREATE TRIGGER `t_bi` BEFORE INSERT ON `t`
FOR EACH ROW
BEGIN
    SET NEW.`s1` = 55;           -- 覆蓋插入值
    SET @msg = 'hello trigger';  -- 設定會話變數
END //

DELIMITER ;

-- 測試
INSERT INTO `t` VALUES (1);
SELECT @msg, * FROM `t`;  -- @msg='hello trigger', s1=55

範例 2:庫存扣減 — AFTER vs BEFORE 關鍵差異

CREATE TABLE `goods` (`id` INT PRIMARY KEY, `name` VARCHAR(20), `stock` INT);
CREATE TABLE `orders` (`id` INT AUTO_INCREMENT PRIMARY KEY, `goods_id` INT, `qty` INT);

INSERT INTO `goods` VALUES (1, 'TV', 35), (2, 'Fridge', 23);

錯誤示範:AFTER INSERT 無法攔截超賣

CREATE TRIGGER `order_ai` AFTER INSERT ON `orders`
FOR EACH ROW
BEGIN
    UPDATE `goods` SET `stock` = `stock` - NEW.`qty` WHERE `id` = NEW.`goods_id`;
END;

測試:

INSERT INTO `orders` (`goods_id`, `qty`) VALUES (1, 5);  -- TV 庫存 30
INSERT INTO `orders` (`goods_id`, `qty`) VALUES (2, 100); -- Fridge 庫存 -77 ❌

正確做法:BEFORE INSERT 先檢查再扣減

DROP TRIGGER `order_ai`;

CREATE TRIGGER `order_bi` BEFORE INSERT ON `orders`
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    SELECT `stock` INTO current_stock FROM `goods` WHERE `id` = NEW.`goods_id` FOR UPDATE;
    
    IF NEW.`qty` > current_stock THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '庫存不足';
    ELSEIF NEW.`qty` > 5 THEN
        SET NEW.`qty` = 5;  -- 限制單筆最大下單量
    END IF;
    
    UPDATE `goods` SET `stock` = `stock` - NEW.`qty` WHERE `id` = NEW.`goods_id`;
END;

測試:

INSERT INTO `orders` (`goods_id`, `qty`) VALUES (2, 100);  -- 實際寫入 qty=5,Fridge 庫存 18 ✅

範例 3:跨表同步 — 新增使用者同步建立檔案

CREATE TABLE `users` (`id` INT PRIMARY KEY, `name` VARCHAR(20), `pwd` VARCHAR(50));
CREATE TABLE `user_profiles` (`user_id` INT PRIMARY KEY, `tel` VARCHAR(20));

CREATE TRIGGER `user_ai` AFTER INSERT ON `users`
FOR EACH ROW
BEGIN
    INSERT INTO `user_profiles` (`user_id`, `tel`)
    VALUES (NEW.`id`, '')
    ON DUPLICATE KEY UPDATE `user_id` = `user_id`;  -- 避免重複報錯
END;

管理指令

-- 查看觸發器
SHOW TRIGGERS;
SHOW CREATE TRIGGER `trigger_name`;

-- 刪除觸發器
DROP TRIGGER [IF EXISTS] `trigger_name`;

-- 修改觸發器:必須先 DROP 再 CREATE

重要限制與最佳實務

  • 不能在觸發器中:呼叫會話變更語句 (COMMIT, ROLLBACK)、動態 SQL (PREPARE)、建立/刪除資料庫物件
  • NEW/OLD:INSERT 只有 NEW;DELETE 只有 OLD;UPDATE 兩者皆有
  • BEFORE 可修改 NEW 值;AFTER 僅可讀取
  • 避免在觸發器中執行耗時操作(發送郵件、HTTP 請求),改用訊息佇列非同步處理
  • 使用 SIGNAL (MySQL 5.5+) 拋出自訂錯誤,替代舊版 INSERT INTO non_exist_table 手法
  • 為避免死鎖,跨表更新請保持固定順序(如先鎖 `goods` 再鎖 `orders`)
copyright © 萬盛學電腦網 all rights reserved