MySQL触发器详解及应用实践

最新推荐文章于 2024-06-14 08:00:00 发布

袁世航或KK

最新推荐文章于 2024-06-14 08:00:00 发布

阅读量979

点赞数 2

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_45538889/article/details/125878841

版权

应用场景

多表之间的数据同步，或者单表单个事件的触发等。。。。。

什么是MySQL触发器

结合MySQL官方网站的解释及个人的理解：触发器是与表关联的数据库对象，当这个表中发生一些特定事件时（比如INSERT\UPDATE\DELETE）相关操作时就会触发这个对象，该操作被认为是一个触发事件，触发器可以设置为在触发事件之前或之后激活，从而进行相关的计算操作。
链接: 详见MySQL官网中对于触发器的定义

触发器的应用和语法

创建MySQL触发器

在Navicat中选中需要创建触发器的表，右键设计表，在设计中有个触发器的选项，具体如下图所示：
Navicat中的操作步骤
其中第一列是给自己定义的触发器命名。第二列触发是给触发器设置执行的顺序，分别有BEFORE和AFTER两种，两者的区别before是在数据变更之前触发，例如delete操作和update操作之前就可以定位到老的数据；而after是在数据变更之后触发的，例如insert或者update之后新的数据可以通过该方式获取。后面的三列是只在具体的操作下触发该事件，有插入、更新和删除三种，且只能单选。
具体的创建SQL如下：

DROP TRIGGER `XXXX触发器名称`;
CREATE DEFINER = `root`@`localhost` TRIGGER `XXXX触发器名称` AFTER INSERT ON `XXXX表名` FOR EACH ROW BEGIN
   '这里输入具体要操作的SQL';
END;

注意上面我在具体的SQL后面加了一个分号 ; 触发器的SQL和普通的SQL单条运行不同对于语句的规范极为苛刻，这个分号不加就会出现各种语法错误且不便定位问题

关于NEW和OLD的使用

上面我们介绍了如何创建触发器，下面我就来说说借助触发器，如何进行业务方面的操作；在说具体的操作SQL之前让我们一起来看看MySQL的NEW和OLD的定义：
MySQL中定义了NEW 和OLD 两个临时表，用来表示触发器的所在表中，触发了触发器的哪一行数据，用来引用触发器中发生变化的记录内容

名称	定义	结合操作
NEW	在操作之后的数据	在 inster 触发器中，NEW 用来拦截并保存将要（before）或者已经（after）插入的数据；在 update 触发器中，NEW用来拦截并保存将要或已经修改的为新的数据
OLD	在操作之前的数据	在 update 触发器中，OLD用来拦截并保存将要或已经修改的原数据；在delete 触发器中，OLD用来拦截并保存将要或或者已经被删除的原数据

使用触发器案例

上面我们已经知道了如何创建触发器已经触发器中关键语法NEW和OLD的定义，下面我们就借助一个简单的例子来实践一下；首先我们创建三张表：

--学生表：--
CREATE TABLE `student`  (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NULL COMMENT '姓名',
  `SEX` int(10) NULL COMMENT '性别',
  `CLASS` int(10) NULL COMMENT '班级ID',
  PRIMARY KEY (`ID`)
) COMMENT = '人员表';
--班级表--
CREATE TABLE `class`  (
  `ID` int NOT NULL AUTO_INCREMENT,
  `NAME` varchar(255) NULL COMMENT '班级名称',
  `COUNT` int(10) NULL COMMENT '班级人数',
  PRIMARY KEY (`ID`)
) COMMENT = '班级表';

例如我们在学生表里面新增一个学生，需要在班级表中更新对应的班级人数，那么我们可以设置一个触发器：
新增类型的触发器
通过这种方式我们发现where条件中使用了NEW语法，NEW就表示新增的数据；在新增一个学生的时候，就可以自动通过触发事件在班级表中更新学生数量了。
删除数据的触发器
上面这个则表示了在student表中数据删除时对class表的更新操作，这里使用了OLD语句，表示定位到删除之前的数据，通过该ID进行class表的更新
主要的简单用法就是这些，重要的就是如何使用OLD和NEW语句

IF判断语句的使用

在实际的使用场景中不可能像上面那样简单的进行一个SQL的处理，在大多数情况下我们甚至需要更新触发器所在表的个别字段进行判断并进行不同的操作，具体语法如下：

BEGIN
IF(1 = NEW.DATA_TYPE OR 3 = NEW.DATA_TYPE) THEN --这里是一个判断语句--
	--这里写入要进行操作的SQL语句--
END IF;
END

复杂的触发器语句

数据操作前进行合法性判断

通过判断语句进行数据操作前的校验，例如下面就对学生名称进行了相关校验

BEGIN
	IF (NEW.NAME = '胡汉三') then
		SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请正确输入学生姓名';
	END IF;
END

具体执行后的结果如下：
数据操作校验

注意事项

begin和end中间的具体业务操作的SQL语句结尾一定要带上分号 ; 不然报错不容易定位到问题；
注意NEW和OLD的使用，如果使用错误会在触发事件的时候报错；
在同一个物理库中，不同表中新建触发器的名称要唯一，不同表之前的触发器名称也要唯一，否则会新增失败。

触发器的优缺点

优点：1.触发器可以确保数据的完整性；2.触发器可以帮助我们记录操作日志；3.触发器还可以用在操作数据前，对数据进行合法性检查。
缺点：1.可读性差，2.维护困难，由于触发器具体的执行本身就是一个SQL存在数据库中，所以有可能不受应用层的控制，在出错后排查困难等。