MySQL 学习笔记 2：触发器

MySQL 学习笔记 2：触发器

图源：ubiq.co

触发器，就像字面意思那样，它会在数据库某些事件发生时执行一些操作。

具体来说，触发器会在特定表的INSERT、UPDATE、DELETE这些类型的 SQL 语句执行时被“触发”，并执行触发器中定义好的（一条或多条） SQL 语句。

在 MySQL 中，触发器存在一些限制，我们只能对同一张表定义最多6个触发器，分别对应6个事件：

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

此外，无法在触发器中调用存储过程，也无法对临时表和视图使用存储过程。

本文使用的数据库可视化工具和测试数据同上一篇文章，不再赘述。

创建触发器

用 SQLyog 创建触发器会有类似下面这样的模版：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `jpa`.`afterStudentAdd` BEFORE/AFTER INSERT/UPDATE/DELETE
    ON `jpa`.`<Table Name>`
    FOR EACH ROW BEGIN

    END$$

DELIMITER ;

这里的afterStudentAdd是我们的触发器名称，名称后需要指明执行触发器的事件，也就是前面说过的6种事件之一，这里我们要按需要修改。ON后是触发器关联的数据库和表，这里要填写需要触发器的表名。FOR EACH ROW说明每一条数据触发事件后都会执行触发器内的 SQL。最后，在BEGIN和END之间填写触发器的 SQL，如果只有一条 SQL 要执行，BEGIN和END可以省略。

原则上同一张表的触发器名称唯一即可，但作为良好习惯，触发器名称应该在数据库中唯一。

INSERT

下面是我编写的触发器：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `jpa`.`afterStudentAdd`  AFTER INSERT
    ON `jpa`.`student`
    FOR EACH ROW BEGIN
        declare nowTime datetime default NOW();
        INSERT INTO `jpa`.`student_add_log` (`id`, `average_score`, `level`, `name`, `time`) VALUES (NEW.id, NEW.average_score, NEW.level, NEW.name, nowTime);
    END$$

DELIMITER ;

这个触发器将在INSERT语句执行后执行，作用是将新添加的数据插入一个日志表student_add_log，并且记录添加时间。在 INSERT 事件中，我们可以使用临时表NEW获取新添加的数据。需要注意的是，自增主键只有在数据真正添加后（INSERT语句执行后）才会产生，所以类似的NEW.id只有在AFTER INSERT事件中才能获取到，BEFORE INSERT中是不存在的。

实际上这里的局部变量nowTime并不是必须的。

日志表的表结构如下：

CREATE TABLE `student_add_log` (
  `id` bigint NOT NULL,
  `average_score` int NOT NULL,
  `level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `name` varchar(45) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

需要注意的是，**如果触发器关联的事件是BEFORE INSERT，而且触发器执行失败，那后续的INSERT语句和AFTER INSERT关联的触发器就不会再执行。**这点其它的BEFORE XXX触发器也是同样的。

我们还可以利用BEFORE INSERT事件，在执行插入语句之前对要插入的数据进行检查，或者将某些数据处理成我们需要的格式：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `jpa`.`beforeStudentAdd` BEFORE INSERT
    ON `jpa`.`student`
    FOR EACH ROW BEGIN
	set NEW.name = INSERT(NEW.name,1,1,UCASE(LEFT(NEW.name,1)));
    END$$

DELIMITER ;

现在即使 INSERT SQL 中要插入的name字段的值首字母小写，也会在执行 SQL 时被触发器替换为首字母大写的形式，这样就确保了数据库中student表的name字段首字母都是大写。

关于 MySQL 的相关函数说明，可以阅读这里。

修改 & 删除触发器

用 SQLyog 修改触发器会提供类似下面的模版 SQL：

DELIMITER $$

USE `jpa`$$

DROP TRIGGER /*!50032 IF EXISTS */ `afterStudentAdd`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `afterStudentAdd` AFTER INSERT ON `student` 
    FOR EACH ROW BEGIN
	DECLARE nowTime DATETIME DEFAULT NOW();
	INSERT INTO `jpa`.`student_add_log` (`id`, `average_score`, `level`, `name`, `time`) VALUES (NEW.id, NEW.average_score, NEW.level, NEW.name, nowTime); 
    END;
$$

DELIMITER ;

和存储过程类似，触发器只能在删除后重新添加，删除触发器的语句是：

DROP TRIGGER `afterStudentAdd`;

如果触发器不存在时避免报错，可以：

DROP TRIGGER IF EXISTS `afterStudentAdd`;

UPDATE

下面是我编写的一个在BEFORE UPDATE事件发生时执行的触发器：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `jpa`.`beforeStudentUpdate` BEFORE UPDATE
    ON `jpa`.`student`
    FOR EACH ROW BEGIN
	INSERT INTO `jpa`.`student_update_log` (`old_id`, `old_average_score`, `old_level`, `old_name`, `new_id`, `new_average_score`, `new_level`, `new_name`, `time`) VALUES (OLD.id, OLD.average_score, OLD.level, OLD.name, NEW.id, NEW.average_score, NEW.level, NEW.name, NOW()); 
    END$$

DELIMITER ;

这个触发器会在student中的表数据修改时将其记录到日志表。

在UPDATE事件中，可以用临时表NEW访问修改后的表数据，可以用临时表OLD访问被修改前的表数据。

日志表结构如下：

CREATE TABLE `student_update_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `old_id` bigint NOT NULL,
  `old_average_score` int NOT NULL,
  `old_level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `old_name` varchar(45) NOT NULL,
  `new_id` bigint NOT NULL,
  `new_average_score` int NOT NULL,
  `new_level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `new_name` varchar(45) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

这里使用BEFORE UPDATE而不是AFTER UPDATE的好处在于，可以保证任何情况下都能记录日志信息（即使UPDATE语句执行失败）。当然这也可能是缺陷，要具体问题具体分析。

与BEFORE INSERT类似，在 BEFORE UPDATE 事件关联的触发器中，我们也可以修改临时表NEW中的值，这样就会导致之后执行的INSERT语句使用我们修改后的值来进行插入：

DELIMITER $$

USE `jpa`$$

DROP TRIGGER /*!50032 IF EXISTS */ `beforeStudentUpdate`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `beforeStudentUpdate` BEFORE UPDATE ON `student` 
    FOR EACH ROW BEGIN
	set NEW.name = INSERT(NEW.name,1,1,UCASE(LEFT(NEW.name,1)));
	INSERT INTO `jpa`.`student_update_log` (`old_id`, `old_average_score`, `old_level`, `old_name`, `new_id`, `new_average_score`, `new_level`, `new_name`, `time`) VALUES (OLD.id, OLD.average_score, OLD.level, OLD.name, NEW.id, NEW.average_score, NEW.level, NEW.name, NOW()); 
    END;
$$

DELIMITER ;

这里修改了存储过程beforeStudentUpdate，通过以下命令改写了临时表NEW中的name字段：

set NEW.name = INSERT(NEW.name,1,1,UCASE(LEFT(NEW.name,1)));

这个命令的用途是将name字段值的首字母替换为大写。

DELETE

最后，用类似的方式添加一个触发器，用于在执行删除 SQL 时将删除的数据保存到日志表：

DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `jpa`.`beforeStudentDelete` BEFORE DELETE
    ON `jpa`.`student`
    FOR EACH ROW BEGIN
	INSERT INTO `jpa`.`student_delete_log` (`id`, `average_score`, `level`, `name`, `time`) VALUES (OLD.id, OLD.average_score, OLD.level, OLD.name, NOW()); 
    END$$

DELIMITER ;

在DELETE事件时，可以用临时表OLD获取被删除的旧数据。

日志表结构：

CREATE TABLE `student_delete_log` (
  `id` bigint NOT NULL,
  `average_score` int NOT NULL,
  `level` enum('FRESH_MAN','JUNIOR','SENIOR','SOPHOMORE') NOT NULL,
  `name` varchar(45) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

总结

触发器的优点在于：在数据库端是透明的，可以通过设置触发器来控制数据录入或者记录日志信息。这些都不受客户端调用的影响，无论 SQL 是以什么样的方式在数据库上执行，相应的触发器都会起作用。

缺点在于：这些触发器对于代码端是“隐藏的”，只凭借代码是无法察觉到这些触发器的，是容易被忽略的。

说到底，触发器属于数据库编程的部分，对于开发人员有一定要求。

The End，谢谢阅读。

参考资料

• 《MySQL 必知必会》
• MySQL 函数 | 菜鸟教程 (runoob.com)