MySql教程

MySQL 触发器

本文主要是介绍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 $$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1VK3NWzy-1640607716925)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227182217960.png)]

  • 查看触发器
-- 查看触发器
SHOW TRIGGERS;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0Jgkawjy-1640607716927)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227182617091.png)]

先来看一下数据

students 表的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aqrqOZB9-1640607716929)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227183040440.png)]

stulogs 表的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-e9ZyaFsC-1640607716930)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227183119543.png)]

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

-- 往学生表中插入数据,测试触发器是否执行
-- 测试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);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SU7La1UR-1640607716931)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227183429473.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LJkjg4Q9-1640607716932)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227184043106.png)]

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UryiePlN-1640607716933)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227183529310.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pjy3LToL-1640607716935)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227184105364.png)]

三、删除触发器

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SnseuSy8-1640607716935)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227191642955.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kGgygjsL-1640607716936)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227191752792.png)]

四、NEW 与 OLD

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

我们可以通过 NEWOLD 关键字在触发器中获取触发器的 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 $$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gzpn5SWf-1640607716937)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227193326637.png)]

测试触发器

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kqbq4921-1640607716938)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227194037599.png)]

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 $$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fUZ15cEI-1640607716939)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227195152871.png)]

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

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8IhvIig5-1640607716939)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227195400687.png)]

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 $$

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2lMhjiAN-1640607716940)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227200022958.png)]

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

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i1aLy3NW-1640607716940)(MySQL%20%E8%A7%A6%E5%8F%91%E5%99%A8.assets/image-20211227200234349.png)]

NEW:

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

OLD:

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

五、触发器总结

优点:

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

缺点:

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

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

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

这篇关于MySQL 触发器的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!