MySQL 触发器

MySQL 触发器

一、触发器介绍

触发器，其实是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的 SQL 片段，但，触发器无需调用，当对数据表中的数据指向 DML 操作时，会自动触发这个 SQL 片段的执行，无需手动调用。

在 MySQL 中，只有执行了 insert \ delete \ update 操作时才能触发触发器（也可以理解为监听器）的执行。

二、触发器的使用

例子：

• 当向学生表中 添加 / 修改 / 删除 学生信息时，使用触发器自动进行日志记录。

-- 学生信息表
CREATE TABLE students(
  stu_num CHAR(8),
  stu_name VARCHAR(20) NOT NULL,
  stu_gender CHAR(2) NOT NULL DEFAULT '男',
  stu_age INT NOT NULL,
  PRIMARY KEY (stu_num)
);

-- 学生信息操作日志表
CREATE TABLE `stulogs`(
  `id` INT AUTO_INCREMENT,
  `time` TIMESTAMP,
  `log_text` VARCHAR(200),
  PRIMARY KEY (`id`)
);

• 创建触发器语法

注意：创建触发器时，触发器名称后面没有小括号，也没有输入参数和输出参数

-- 创建触发器
DELIMITER $$
CREATE TRIGGER 触发器名称
-- 定义触发时机，在什么之前或在什么之后，二选一
<BEFORE|AFTER>
-- 定义 DML 类型，三选一
<INSERT|UPDATE|DELETE> 
-- 表名称
ON <TABLE_NAME>
-- 触发器的 SQL 操作
SQL_STATEMENT
-- 声明为行级触发器（只要操作一条记录就触发触发器执行一次）
-- 如果 insert\update\delete 语句执行了一次，触发器触发一次
-- 如果 insert\update\delete 语句执行了多次，触发器触发多次
FOR EACH ROW
BEGIN
  -- SQL 
END $$

insert 操作中：

-- 创建触发器：当学生表中发生了操作时，则向日志信息表中记录一条日志
DELIMITER $$
CREATE TRIGGER trigger_test1
-- 这个表触发 insert 操作
-- 触发器会在 insert 之后执行
AFTER INSERT ON students
FOR EACH ROW
BEGIN
  -- SQL
  INSERT INTO stulogs(`time`,`log_text`)VALUES(NOW(),CONCAT('添加',NEW.stu_num,'学生信息'));
END $$

• 查看触发器

-- 查看触发器
SHOW TRIGGERS;

先来看一下数据

students 表的

stulogs 表的

接下来测试一下，看一下触发器是否执行了

-- 往学生表中插入数据，测试触发器是否执行
-- 测试1：对 students 表插入一条数据，触发器执行了一次
INSERT INTO students(stu_num,stu_name,stu_gender,stu_age)
VALUES('1004','赵六','男',20);

-- 测试2：一条 SQL 指令添加两条学生信息，触发器执行了两次
INSERT INTO students(stu_num,stu_name,stu_gender,stu_age)
VALUES('1005','阿七','男',20),('1006','阿八','男',20);

再来看一些日志表中是否添加了日志

三、删除触发器

-- 删除触发器
-- DROP TRIGGER 触发器名称;
DROP TRIGGER trigger_test1;

四、NEW 与 OLD

触发器用于监听对数据表中的 insert \ update \ delete 操作，在触发器中通常处理一些 DML 的关联操作。

我们可以通过 NEW 和 OLD 关键字在触发器中获取触发器的 DML 操作的数据

• NEW 关键字：在触发器中用于获取 insert 操作添加数据或者 update 操作修改后的记录
• OLD 关键字：在触发器用于获取 delete 操作删除前的数据或者 update 操作修改前的记录

4.1、NEW

案例使用：

update 操作中：

-- 创建触发器：在监听 update 操作的触发器，可以使用 NEW 关键字获取修改后的数据
DELIMITER $$
CREATE TRIGGER trigger_test2
AFTER UPDATE ON students FOR EACH ROW
BEGIN
  -- SQL，这里的 NEW 代表是修改后的名字
  INSERT INTO stulogs(`time`,`log_text`)VALUES(NOW(),CONCAT('将学号为：',NEW.stu_num,'的学生姓名修改为：',NEW.stu_name));
END $$

测试触发器

-- 测试修改：是否触发触发器添加日志
UPDATE students SET stu_name='阿八' WHERE stu_num='1006';

查看一下日志信息是否添加了

4.2、OLD

delete 操作中：

-- 当从 students 表中删除的时候在 stulogs 表中添加日志
DELIMITER $$
CREATE TRIGGER trigger_test3
AFTER DELETE ON students FOR EACH ROW
BEGIN
  -- SQL，OLD 表示删除的记录，也可以用在 update 中
  INSERT INTO stulogs(`time`,`log_text`)VALUES(NOW(),CONCAT('将学号为：',OLD.stu_num,'的学生删除了'));
END $$

好创建没问题，接下来测试一下

-- 删除测试
DELETE FROM students WHERE stu_num='1006';

接下来，看一下日志表中有没有获取到删除前的信息

4.3、NEW 和 OLD 组合使用

-- 创建触发器
DELIMITER $$
CREATE TRIGGER trigger_test4
AFTER UPDATE ON students FOR EACH ROW
BEGIN
  -- SQL，OLD 获取修改前的信息，NEW 获取修改后的信息
  INSERT INTO stulogs(`time`,`log_text`)VALUES(NOW(),CONCAT('将学生姓名从 [',OLD.stu_name,']修改为[',NEW.stu_name,']'));
END $$

创建没问题，接下来测试一下，看一下日志表中的记录

-- 测试
UPDATE students SET stu_name='七仔' WHERE stu_num='1005';

NEW：

• insert 操作中：NEW 表示获取添加的新记录
• update 操作中：NEW 表示获取修改后的记录

OLD：

• delete 操作中：OLD 表示获取删除前的记录
• update 操作中：OLD 表示获取修改前的记录

五、触发器总结

优点：

1. 触发器是自动执行的，当对触发器相关的表执行 DML 操作时可以触发触发器执行
2. 触发器可以实现表中的数据的级联操作（关联操作），有利于保证数据的完整性
3. 触发器可以对 DML 操作的数据进行更为复杂合法性校验

缺点：

1. 使用触发器实现的业务逻辑，如果出现问题将难以定位，后续维护困难
2. 如果大量使用触发器容易导致代码结构杂乱，增加了程序的复杂性
3. 当触发器处理的数据量比较大时，执行效率会大大降低

使用建议：在互联网项目中，应避免使用触发器；对于并发量不大的项目，可以选中使用存储过程，但，在互联网项目中不提倡使用存储过程

原因：存储过程时将业务逻辑交给数据处理，一则增减了数据库的负载，二则不利于数据库的迁移