MySQL触发器

MySQL 触发器（Trigger）是与表关联的特殊存储过程，当表发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行。触发器可用于实现数据校验、日志记录、数据同步等功能，是保证数据完整性和自动化业务逻辑的重要工具。

1、触发器的基本概念

• 自动执行：无需手动调用，当触发事件发生时自动运行。
• 与表关联：触发器依附于具体表，不能独立存在。
• 事件驱动：由 INSERT、UPDATE、DELETE 等 DML 事件触发。
• 新旧数据访问：可通过 NEW（新数据）和 OLD（旧数据）关键字访问数据变化。

触发器的典型应用场景：

• 数据校验（如限制订单金额不能为负）。
• 自动记录操作日志（如跟踪数据修改历史）。
• 数据同步（如更新订单时同步更新用户消费总额）。
• 实现复杂业务规则（如库存不足时禁止下单）。

2、触发器的创建与管理

2.1 创建触发器的语法

CREATE TRIGGER 触发器名
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW  -- 行级触发器（每影响一行就执行一次）
BEGIN-- 触发器执行的SQL语句
END;

核心要素：

• 触发时机：BEFORE（事件发生前）或 AFTER（事件发生后）。
• 触发事件：INSERT、UPDATE、DELETE。
• 作用范围：FOR EACH ROW 表示行级触发器（MySQL 只支持行级触发器）。

2.2 触发器中的特殊变量

• NEW.字段名：访问插入/更新后的新数据（INSERT、UPDATE 可用）。
• OLD.字段名：访问更新/删除前的旧数据（UPDATE、DELETE 可用）。
• 可通过 SET NEW.字段名 = 值 在 BEFORE 触发器中修改新数据。

2.3 常见触发器示例

示例 1：BEFORE INSERT 触发器（数据校验）

限制插入的订单金额不能为负数：

DELIMITER //
CREATE TRIGGER `check_order_amount_before_insert`
BEFORE INSERT ON `order`
FOR EACH ROW
BEGIN-- 若金额小于0，抛出错误IF NEW.amount < 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单金额不能为负数';END IF;
END //
DELIMITER ;

示例 2：AFTER INSERT 触发器（日志记录）

订单创建后自动记录日志：

DELIMITER //
CREATE TRIGGER `log_order_after_insert`
AFTER INSERT ON `order`
FOR EACH ROW
BEGIN-- 插入日志，记录新订单信息INSERT INTO `order_log` (order_id, content, created_at)VALUES (NEW.id,  -- 新订单IDCONCAT('订单创建：', NEW.order_no, '，金额：', NEW.amount),NOW());
END //
DELIMITER ;

示例 3：AFTER UPDATE 触发器（数据同步）

订单状态更新后，同步更新用户的最后订单时间：

DELIMITER //
CREATE TRIGGER `sync_user_last_order_time`
AFTER UPDATE ON `order`
FOR EACH ROW
BEGIN-- 仅当订单状态变为"已支付"时更新IF OLD.status != 1 AND NEW.status = 1 THENUPDATE `user` SET last_order_time = NOW()WHERE id = NEW.user_id;END IF;
END //
DELIMITER ;

示例 4：BEFORE DELETE 触发器（删除前校验）

禁止删除有订单的用户：

DELIMITER //
CREATE TRIGGER `prevent_delete_user_with_orders`
BEFORE DELETE ON `user`
FOR EACH ROW
BEGINDECLARE `order_count` INT;-- 查询用户是否有订单SELECT COUNT(*) INTO order_count FROM `order` WHERE user_id = OLD.id;-- 若有订单，阻止删除IF order_count > 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该用户存在订单，不能删除';END IF;
END //
DELIMITER ;

2.4 查看触发器

-- 方法1：查看所有触发器
SHOW TRIGGERS;-- 方法2：通过information_schema查询（更灵活）
SELECT trigger_name, event_manipulation AS 触发事件,action_timing AS 触发时机,table_name AS 关联表
FROM information_schema.triggers 
WHERE trigger_schema = '数据库名';

2.5 删除触发器

-- 删除触发器（无需指定表名，触发器名在数据库中唯一）
DROP TRIGGER IF EXISTS `check_order_amount_before_insert`;

3、触发器的执行顺序

对同一张表的同一事件（如 INSERT），可创建多个触发器，执行顺序为：

1. 所有 BEFORE 触发器按创建顺序执行。
2. 触发事件（INSERT/UPDATE/DELETE）执行。
3. 所有 AFTER 触发器按创建顺序执行。

示例：一张表上创建两个 BEFORE INSERT 触发器：

-- 先创建的先执行
CREATE TRIGGER `trigger1` BEFORE INSERT ON `test` ...
CREATE TRIGGER `trigger2` BEFORE INSERT ON `test` ...-- 执行顺序：trigger1 → trigger2 → 实际INSERT

4、触发器的优缺点

优点：

1. 自动执行：无需应用程序干预，确保数据操作的一致性。
2. 数据校验：在数据写入前（BEFORE）校验，避免非法数据入库。
3. 日志跟踪：自动记录数据变更历史，便于审计和溯源。
4. 简化应用代码：将部分数据逻辑放在数据库层，减少应用程序代码。

缺点：

1. 隐蔽性强：触发器在后台自动执行，调试和排查问题困难。
2. 性能影响：复杂触发器会增加数据操作的响应时间，尤其批量操作时。
3. 维护成本高：过多触发器会导致业务逻辑分散，难以理解和维护。
4. 递归风险：触发器中若修改了其他表，可能触发其他触发器，形成递归。

5、使用触发器的注意事项

1. 避免复杂逻辑：触发器应保持简单，复杂业务逻辑建议放在应用层。
2. 禁用长时间操作：触发器中禁止执行 SELECT 耗时查询或大量数据操作。
3. 谨慎使用事务：触发器中若包含事务操作，需注意与外部事务的兼容性。
4. 避免循环触发：触发器修改的表不应再次触发自身或其他触发器，防止死循环。

   -- 危险示例：可能导致循环触发
   CREATE TRIGGER `update_after_update`
   AFTER UPDATE ON `test`
   FOR EACH ROW
   BEGIN-- 更新同一张表，会再次触发本触发器UPDATE `test` SET col = 1 WHERE id = NEW.id;
   END;
   

5. 版本兼容性：不同 MySQL 版本对触发器的支持可能有差异，迁移时需测试。