MySQL进阶三板斧（三）看清“触发器 (Trigger)”的真实面目

最新推荐文章于 2024-06-30 07:14:16 发布

efgtrrg

最新推荐文章于 2024-06-30 07:14:16 发布

阅读量399

点赞数 4

分类专栏：程序员文章标签： mysql adb android

本文链接：https://blog.csdn.net/efgtrrg/article/details/137279739

版权

程序员专栏收录该内容

174 篇文章 0 订阅

订阅专栏

1. 创建语法四要素

监视地点(table)
监视事件(insert | update | delete)
触发时间(after | before)
触发事件(insert | update | delete)

2. 语法公式

CREATE TRIGGER <trigger_name>

BEFORE|AFTER

INSERT|UPDATE|DELETE ON <table_name> # 表名

FOR EACH ROW # 这句话在mysql是固定的

BEGIN

<触发的SQL语句>（调用NEW/OLD参数）;

END

3. 语法参数说明

CREATE TRIGGER <trigger_name> — 触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
{ BEFORE | AFTER } — 触发器触发时间设置：可以设置为事件发生前或后（前：一般用于校验；后：一般用于关联）。
{ INSERT | UPDATE | DELETE } – 设定触发事件：如执行insert、update或delete的过程时激活触发器。
ON <table_name> — 触发器是属于某一个表的: 当在这个表上执行 INSERT|UPDATE|DELETE 操作的时候就导致触发器的激活. 同时，我们不能给同一张表的“同一个事件”安排两个触发器（意味着不能同时有两个Insert触发器）。
FOR EACH ROW — 触发器的执行间隔（必有的公式内容）：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。
<触发的SQL语句> — 触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句，包括复合语句，但是这里的语句受的限制和函数的一样。当然，触发SQL中可以调用“触发了（ INSERT | UPDATE | DELETE ）触发器的那一行数据”。

例如下方代码：

create trigger add_stu

after insert on student for each row

begin

insert into student_score ( stu_id, score, rank)

values( NEW.stuid, NEW.username); – NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据

end;

4. NEW 与 OLD关键字详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；

③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

另外，原则上请编写简单高效的触发执行语句，以免悄无声息的浪费过多资源你还不知道！

三、实际应用

======

1. 数据准备

想我初三时常年倒数，成绩稳定，因此我拿当时几位老友排名数据来纪念一波（手动挠头）。给大家提供一个测试数据；

（沿用的前两篇“视图”、“存储过程”博文中的数据）

a.学生表

CREATE TABLE student (

ID int NOT NULL AUTO_INCREMENT ,

NAME varchar(30) NOT NULL ,

SEX char(2) NOT NULL ,

AGE int NOT NULL ,

CLASS varchar(10) NOT NULL ,

GRADE varchar(20) NOT NULL ,

HOBBY varchar(100) NULL ,

PRIMARY KEY (ID)

)

#插入数据：

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘1’, ‘陈哈哈’, ‘男’, ‘15’, ‘18班’, ‘9年级’, ‘上网’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘2’, ‘扈亚鹏’, ‘男’, ‘15’, ‘18班’, ‘9年级’, ‘美食’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘3’, ‘刘晓莉’, ‘女’, ‘14’, ‘18班’, ‘9年级’, ‘金希澈’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘4’, ‘朱志鹏’, ‘男’, ‘15’, ‘18班’, ‘9年级’, ‘睡觉’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘5’, ‘徐立楠’, ‘女’, ‘14’, ‘18班’, ‘9年级’, ‘阅读’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘6’, ‘顾昊’, ‘男’, ‘15’, ‘5班’, ‘9年级’, ‘篮球’);

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY) VALUES (‘7’, ‘陈子凝’, ‘女’, ‘15’, ‘18班’, ‘9年级’, ‘看电影’);

#插入结果：

b.成绩表

CREATE TABLE student_score (

SID int(11) NOT NULL,

S_NAME varchar(30) NOT NULL,

TOTAL_SCORE int(11) NOT NULL,

RANK int(11) NOT NULL,

PRIMARY KEY (SID)

)

#插入数据：

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘1’, ‘陈哈哈’, ‘405’, ‘1760’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘2’, ‘扈亚鹏’, ‘497’, ‘1000’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘3’, ‘刘晓莉’, ‘488’, ‘1170’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘4’, ‘朱志鹏’, ‘405’, ‘1770’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘5’, ‘徐立楠’, ‘530’, ‘701’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘6’, ‘顾昊’, ‘485’, ‘1286’);

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK) VALUES (‘7’, ‘陈子凝’, ‘704’, ‘9’);

#插入结果：

c.逃课上网表

CREATE TABLE student_go_wangba (

SID int(9) NOT NULL,

SGW_NAME varchar(30) DEFAULT NULL,

TIMES int(9) DEFAULT NULL,

PRIMARY KEY (SID)

)

#插入数据：

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘1’, ‘陈哈哈’, 15);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘2’, ‘扈亚鹏’, 1);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘3’, ‘刘晓莉’, 0);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘4’, ‘朱志鹏’, 63);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘5’, ‘徐立楠’, 0);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘6’, ‘顾昊’, 7);

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES) VALUES (‘7’, ‘陈子凝’, 0);

#插入结果：

2. 使用案例

# Insert触发器 - 级联插入

表数据：以上面的三张表为例；学生表（student）、学生成绩表（student_score）、逃课上网次数表（student_go_wangba），均已学号（stuid）为主键。

需求：

需要设计一个触发器A，当增加新的学生时，需要在成绩表（student_score）中插入对应的学生信息，至于“分值、排名”字段为0即可；后面由老师打分更新。
需要设计一个触发器B，当增加新的学生成绩信息时，需要在逃课上网表（student_go_wangba）中插入对应的学生信息，至于“逃课上网次数”字段为0即可；后面由教导主任“小平头”去更新。（该触发器意义在于：测试after insert链式反应是否支持）

那么，如何设计触发器A呢？

首先它是一个插入Insert触发器，是建立在表student上的；
然后是after，插入后的事件；
事件内容是插入成绩表，需要插入学生的学号和姓名，number为自增，而“分值、排名”目前不需要。

注意：new表示student中新插入的值

触发器A:

– 新增触发器A，当student表插入数据时，student_score表生成初始关联数据

DROP TRIGGER IF EXISTS add_stu;

create trigger add_stu

after insert on student for each row

begin

INSERT INTO student_score (SID, S_NAME, TOTAL_SCORE, RANK)

VALUES (new.ID,new.NAME, 0, 0 );

end;

触发器B:

– 新增触发器B，当student_score表插入数据时，student_go_wangba表生成初始关联数据

DROP TRIGGER IF EXISTS add_score;

create trigger add_score

after insert on student_score for each row

begin

INSERT INTO student_go_wangba (SID, SGW_NAME, TIMES)

VALUES (new.SID,new.S_NAME, 0 );

end;

查询一下我的触发器：

show triggers \G

– "\G"是干什么用的？

– 作用：在shell中树形展示

如果在Navicat中就不用 \G，直接"show triggers;"就可以。

show triggers;

执行触发器，向student表中加入一条数据：

INSERT INTO student (ID, NAME, SEX, AGE, CLASS, GRADE, HOBBY)

VALUES (‘8’, ‘李昂’, ‘男’, ‘15’, ‘18班’, ‘9年级’, ‘看片儿’);

结果如下图所示：

同时插入三个数据，两个触发器正确执行了~

注意：创建触发器和表一样，建议增加判断：DROP TRIGGER IF EXISTS `add_stu`;

# Delete触发器 - 级联删除

表数据：以上面的三张表为例；学生表（student）、学生成绩表（student_score）、逃课上网次数表（student_go_wangba），均已学号（stuid）为主键。

需求：有些老是逃课上网的学生被开除，需要删掉所有信息，以免给学校抹黑~~~

需要设计一个触发器C，当删除新的学生时，需要在成绩表（student_score）中删除对应的学生信息。
需要设计一个触发器D，当删除新的学生成绩信息时，需要在逃课上网表（student_go_wangba）中删除对应的学生信息。（该触发器意义在于：测试after delete链式反应是否支持）

那么，如何设计触发器C呢？

首先它是一个插入delete触发器，是建立在表student上的；
然后是after，插入后的事件；
事件内容是关联删除成绩表数据，需要删除学生的学号即可。

注意：old表示student中新删除的值。

触发器C:

– 新增触发器C，当student表删除数据时，student_score表删除关联数据

DROP TRIGGER IF EXISTS del_stu;

create trigger del_stu

after delete on student for each row

begin

DELETE FROM student_score where SID = old.ID;

end;

触发器D:

– 新增触发器D，当student_score表删除数据时，student_go_wangba表删除关联数据

DROP TRIGGER IF EXISTS del_score;

create trigger del_score

after delete on student_score for each row

begin

DELETE FROM student_go_wangba where SID = old.SID;

end;

查询一下我的触发器（show triggers \G）：

执行触发器，从student表中删除一条数据：

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

这份文档从构建一个键值数据库的关键架构入手，不仅带你建立起全局观，还帮你迅速抓住核心主线。除此之外，还会具体讲解数据结构、线程模型、网络框架、持久化、主从同步和切片集群等，帮你搞懂底层原理。相信这对于所有层次的Redis使用者都是一份非常完美的教程了。

整理不易，觉得有帮助的朋友可以帮忙点赞分享支持一下小编~

你的支持，我的动力；祝各位前程似锦，offer不断！！！
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门即可获取！
*

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

[外链图片转存中…(img-kGIcY7qY-1712051962303)]

整理不易，觉得有帮助的朋友可以帮忙点赞分享支持一下小编~

你的支持，我的动力；祝各位前程似锦，offer不断！！！
《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门即可获取！

efgtrrg

关注

4
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录