CREATE TRIGGER `trigger_name`
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON `table_name`
FOR EACH ROW
BEGIN
-- 觸發器主體
END;
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
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);
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 ❌
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 ✅
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)、建立/刪除資料庫物件INSERT 只有 NEW;DELETE 只有 OLD;UPDATE 兩者皆有NEW 值;AFTER 僅可讀取SIGNAL (MySQL 5.5+) 拋出自訂錯誤,替代舊版 INSERT INTO non_exist_table 手法