触发器

触发器是与表相关的数据库对象：在 INSERT / UPDATE / DELETE 之前或之后，自动执行其中定义的 SQL 语句集合，常用于保证数据完整性、记录日志、数据校验。

一、介绍

通过别名 OLD 和 NEW 引用触发器中发生变化的记录内容。
触发器只支持行级触发（FOR EACH ROW），不支持语句级触发。

触发器类型	`NEW` 与 `OLD`
`INSERT` 型	`NEW` 表示将要或已经新增的数据
`UPDATE` 型	`OLD` 表示修改之前的数据，`NEW` 表示将要或已经修改后的数据
`DELETE` 型	`OLD` 表示将要或已经删除的数据

INSERT 型没有 OLD，DELETE 型没有 NEW，引用不存在的一侧会报错。

二、语法

操作	语法
创建	`CREATE TRIGGER trigger_name BEFORE/AFTER INSERT/UPDATE/DELETE ON tbl_name FOR EACH ROW BEGIN ... END;`
查看	`SHOW TRIGGERS;`
删除	`DROP TRIGGER [schema_name.]trigger_name;`（不写 `schema_name` 时默认当前数据库）

命令行中创建触发器需先用 DELIMITER 临时改结束符，否则 BEGIN ... END 内的 ; 会被提前结束；图形化工具无需处理。

三、案例：记录 tb_user 的数据变更日志

需求：把 tb_user 的增加、修改、删除操作记录到日志表 user_logs。

准备日志表

CREATE TABLE user_logs(
    id INT(11) NOT NULL AUTO_INCREMENT,
    operation VARCHAR(20) NOT NULL COMMENT '操作类型, insert/update/delete',
    operate_time DATETIME NOT NULL COMMENT '操作时间',
    operate_id INT(11) NOT NULL COMMENT '操作的ID',
    operate_params VARCHAR(500) COMMENT '操作参数',
    PRIMARY KEY(id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

插入触发器（AFTER INSERT，用 NEW 取新增数据）

CREATE TRIGGER tb_user_insert_trigger
    AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'insert', NOW(), NEW.id,
            CONCAT('插入数据内容: id=', NEW.id, ',name=', NEW.name, ',phone=', NEW.phone,
                   ',email=', NEW.email, ',profession=', NEW.profession));
END;

修改触发器（AFTER UPDATE，同时记录 OLD 修改前与 NEW 修改后）

CREATE TRIGGER tb_user_update_trigger
    AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'update', NOW(), NEW.id,
            CONCAT('更新之前的数据: id=', OLD.id, ',name=', OLD.name, ',phone=', OLD.phone,
                   ',email=', OLD.email, ',profession=', OLD.profession,
                   ' | 更新之后的数据: id=', NEW.id, ',name=', NEW.name, ',phone=', NEW.phone,
                   ',email=', NEW.email, ',profession=', NEW.profession));
END;

删除触发器（AFTER DELETE，用 OLD 取被删除数据）

CREATE TRIGGER tb_user_delete_trigger
    AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'delete', NOW(), OLD.id,
            CONCAT('删除之前的数据: id=', OLD.id, ',name=', OLD.name, ',phone=', OLD.phone,
                   ',email=', OLD.email, ',profession=', OLD.profession));
END;

触发测试

INSERT INTO tb_user(id, name, phone, email, profession, age, gender, status, createtime)
VALUES (25, '二皇子', '18809091212', 'erhuangzi@163.com', '软件工程', 23, '1', '1', NOW());

UPDATE tb_user SET profession = '会计' WHERE id = 23;
DELETE FROM tb_user WHERE id = 25;

SELECT * FROM user_logs;     -- 三条记录：insert / update / delete

易错：UPDATE 的触发器要同时引用 OLD 和 NEW 才能对比修改前后的值。

速查

-- 查看当前库的全部触发器
SHOW TRIGGERS;

-- 删除触发器（存在才删）
DROP TRIGGER IF EXISTS tb_user_insert_trigger;

-- 插入后记录日志：NEW 为新增数据
CREATE TRIGGER tb_user_insert_trigger AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'insert', NOW(), NEW.id, CONCAT('name=', NEW.name));
END;

-- 修改后记录日志：OLD 为修改前，NEW 为修改后
CREATE TRIGGER tb_user_update_trigger AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'update', NOW(), NEW.id,
            CONCAT('before=', OLD.profession, ' after=', NEW.profession));
END;

-- 删除前记录日志：OLD 为被删除数据
CREATE TRIGGER tb_user_delete_trigger AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
    VALUES (NULL, 'delete', NOW(), OLD.id, CONCAT('name=', OLD.name));
END;

触发时机	能引用的数据
`INSERT`	`NEW`
`UPDATE`	`OLD`（修改前）、`NEW`（修改后）
`DELETE`	`OLD`