mysql之触发器

触发器（Trigger） 是 MySQL 中与表事件关联的特殊存储过程，当表发生 INSERT/UPDATE/DELETE 操作时，会自动触发执行预设的 SQL 逻辑。触发器的核心是 事件驱动，无需手动调用，常用于保证数据一致性、实现审计日志、数据同步等场景。

触发器的关键概念

触发时机与事件

触发时机	触发事件	适用场景
BEFORE	INSERT/UPDATE/DELETE	数据校验、默认值填充、字段格式转换
AFTER	INSERT/UPDATE/DELETE	审计日志、数据同步、关联表更新

虚拟表 NEW 和 OLD

触发器执行时，MySQL 会自动创建两个临时虚拟表，用于存储操作前后的数据：

虚拟表	适用事件	作用
NEW	INSERT/UPDATE	存储插入 / 更新后的数据 - INSERT：NEW.字段 表示新插入的值 - UPDATE：NEW.字段 表示更新后的值
OLD	UPDATE/DELETE	存储更新前 / 删除前的数据 - UPDATE：OLD.字段 表示更新前的值 - DELETE：OLD.字段 表示删除前的值

注意：INSERT 事件无 OLD 表，DELETE 事件无 NEW 表。

触发器的创建（CREATE TRIGGER）

DELIMITER $$  -- 修改语句结束符为 $$（避免与触发器内的 ; 冲突）
CREATE TRIGGER 触发器名
触发时机 BEFORE|AFTER
触发事件 INSERT|UPDATE|DELETE
ON 表名 FOR EACH ROW  -- 行级触发器：每操作一行触发一次
BEGIN
    -- 触发器执行的 SQL 逻辑（可写多条语句，用 ; 分隔）
END$$
DELIMITER ;  -- 恢复语句结束符为 ;

关键参数：FOR EACH ROW 表示行级触发器，是 MySQL 唯一支持的触发器类型（即每插入 / 更新 / 删除一行数据，触发器就执行一次）。

实战示例：

场景 1：数据校验（BEFORE INSERT）

需求：插入用户数据时，强制年龄必须在 0-120 之间，否则抛出异常。

-- 准备用户表
CREATE TABLE `user` (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED COMMENT '年龄'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 创建触发器
DELIMITER $$
CREATE TRIGGER `trg_user_before_insert`
BEFORE INSERT ON `user` FOR EACH ROW
BEGIN
    -- 校验年龄范围
    IF NEW.age < 0 OR NEW.age > 120 THEN
        -- 抛出异常，中断插入操作（MySQL 5.5+ 支持 SIGNAL）
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在0-120之间';
    END IF;
END$$
DELIMITER ;

-- 测试触发器
INSERT INTO `user` (name, age) VALUES ('张三', 150);  -- 触发异常，插入失败
INSERT INTO `user` (name, age) VALUES ('李四', 25);   -- 插入成功

触发器的查询、修改与删除

（1）查看数据库中所有触发器

SHOW TRIGGERS;
-- 或查询系统表（更灵活）
SELECT TRIGGER_NAME, EVENT_MANIPULATION, TABLE_NAME 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_SCHEMA = '你的数据库名';

（2）查看触发器的创建语句

-- 需查询 information_schema 系统表
SELECT TRIGGER_BODY 
FROM information_schema.TRIGGERS 
WHERE TRIGGER_NAME = 'trg_user_before_insert' 
AND TRIGGER_SCHEMA = '你的数据库名';

MySQL 不支持直接修改触发器，若需修改逻辑，需先删除旧触发器，再创建新触发器。

删除触发器

DROP TRIGGER [IF EXISTS] 触发器名;

-- 示例：删除用户插入触发器
DROP TRIGGER IF EXISTS `trg_user_before_insert`;