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

最新推荐文章于 2024-09-24 22:47:46 发布
最新推荐文章于 2024-09-24 22:47:46 发布
阅读量788 收藏 29
点赞数 30
分类专栏： 程序员 文章标签： mysql android 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/peixiancccc/article/details/138421984
版权
本文详细解析了MySQL触发器的创建、语法、实际应用，包括级联插入和删除操作，以及触发器的注意事项和限制。通过实例展示了如何使用触发器维护数据库一致性，以及面试中可能涉及的相关知识。
摘要由CSDN通过智能技术生成

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

  • 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

  • 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

5. 同步实时地复制表中的数据。

6. 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。

二、触发器语法

==========

1. 创建语法四要素

  1. 监视地点(table)

  2. 监视事件(insert | update | delete)

  3. 触发时间(after | before)

  4. 触发事件(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. 语法参数说明

  1. CREATE TRIGGER <trigger_name>  — 触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.

  2. { BEFORE | AFTER }  — 触发器触发时间设置：可以设置为事件发生前或后（前：一般用于校验；后：一般用于关联）。

  3. { INSERT | UPDATE | DELETE }  – 设定触发事件：如执行insert、update或delete的过程时激活触发器。

  4. ON <table_name>  — 触发器是属于某一个表的: 当在这个表上执行 INSERT|UPDATE|DELETE 操作的时候就导致触发器的激活. 同时，我们不能给同一张表的“同一个事件”安排两个触发器（意味着不能同时有两个Insert触发器）。

  5. FOR EACH ROW  — 触发器的执行间隔（必有的公式内容）：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。

  6. <触发的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呢？

  1. 首先它是一个插入Insert触发器，是建立在表student上的；

  2. 然后是after，插入后的事件；

  3. 事件内容是插入成绩表，需要插入学生的学号和姓名，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呢？

  1. 首先它是一个插入delete触发器，是建立在表student上的；

  2. 然后是after，插入后的事件；

  3. 事件内容是关联删除成绩表数据，需要删除学生的学号即可。

  • 注意：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表中删除一条数据：

DELETE FROM student where NAME = ‘朱志鹏’

结果如下图所示：

同时删除三个数据，触发器正确执行了。朱志鹏同学数据已经木得了~

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

# Update触发器 - 实时更新

跟Insert触发器、Delete触发器同理，这里不再赘述

四、触发器有哪些缺陷 @江湖中人

================

下面通过参考知乎、CSDN论坛、天涯社区，简单讲解几个内容供大家参考：

问题一： 触发器有哪些限制和注意事项

回答：

触发器会有以下两种限制：

本次面试答案，以及收集到的大厂必问面试题分享：

字节跳动超高难度三面java程序员面经，大厂的面试都这么变态吗？

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！
增加判断：DROP TRIGGER IF EXISTS `del_stu`;

# Update触发器 - 实时更新

跟Insert触发器、Delete触发器同理，这里不再赘述

四、触发器有哪些缺陷 @江湖中人

================

下面通过参考知乎、CSDN论坛、天涯社区，简单讲解几个内容供大家参考：

问题一： 触发器有哪些限制和注意事项

回答：

触发器会有以下两种限制：

本次面试答案，以及收集到的大厂必问面试题分享：

[外链图片转存中…(img-vKVyC3OY-1714733621271)]

《一线大厂Java面试题解析+核心总结学习笔记+最新讲解视频+实战项目源码》，点击传送门，即可获取！

peixiancccc
关注
专栏目录
MySQL进阶三板斧（三）看清“触发器 (Trigger)
m0_63174529的博客
11-06 625
3. 实现复杂的数据完整性规则 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。 提供可变的缺省值。 4. 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。 例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。 在修改或删除时级联修改或删除其它表中的与之匹配的行。 在修改或删除时把其它表中的与之
数据库mysql进阶—trigger触发器
sfwefqe的博客
12-08 1300
触发器 在实际开发中，我们经常会遇到这样的情况：有两个或者多个相互关联的表，如商品信息和库存信息分别存放在2个不同的数据表中，我们在添加一条新的商品记录的时候，为了保证数据的完整性，必须同时在库存表中添加一条库存记录。 这样一来，我们就必须把这两个关联的操作步骤写到程序里面，而且要用事务包裹起来，确保这两个操作成为一个原子操作，要么全部执行，要么全部不执行。要是遇到特殊情况，可能还需要对数据进行手动维护，这样就很容易忘记其中的一步，导致数据缺失。 这个时候，咱们可以使用触发器。你可以创建一个触发器，让商品信
触发器-trigger
LHX_ldm的博客
07-30 424
触发器-trigger 　　触发器(trigger)：监视某种情况，并触发某种操作。 　　触发器创建语法四要素：1.监视地点(table) 　　　　　　　　　　　　    2.监视事件(insert/update/delete) 　　　　　　　　　　　　　3.触发时间(after/before) 　　　　　　　　　　　　　4.触发事件(insert/update/delete)   ...
什么是触发器（Trigger）？触发器何时会被触发？
最新发布
程序员学习园地。
09-24 701
在数据库管理系统中，触发器是一种特殊的存储过程，它会在特定的表上执行插入、更新或删除操作时自动触发。触发器的主要用途是维护数据的一致性和完整性，以及实现一些复杂的业务逻辑。
触发器trigger
qq_41584921的博客
03-11 382
触发事件：引起触发器运行的操作，包含：dml语句（delete update insert对数据的操纵）、ddl（create、alter drop对数据库表的结构的创建 修改 删除），数据库系统事件（数据库的启动、退出、异常错误）、用户事件（登录或退出数据库）。 1、触发器的语法格式： create [or replace] trigger tri_name [before|after|i...
初学sql server 2008之触发器
weixin_30258901的博客
04-07 384
　　触发器(trigger)：是一种特殊的存储过程，可以用来对表实施复杂的完整性约束，保持数据的一致性。当触发器所保护的数据发生改变时，触发器会自动被激活，并执行触发器中所定义的相关操作，从而保证对数据的不完整性约束或不正确的修改。 　　在SQL SERVER 2008中，有三种类型的触发器： (1)DML触发器：是指触发器在数据库中发生数据操作语言(DML)事件时将启用。DML事件即指在表或...
三、MySQL mysql进阶
06-11
三、MySQL mysql进阶
MySQL进阶：理解与应用触发器实战揭秘
MySQL进阶三板斧（三）—— 触发器 (Trigger) 的真实面目 MySQL作为一款流行的开源关系型数据库管理系统，自5.0.1版开始引入了触发器这一高级功能，与视图和存储过程一起构成了其强大的核心特性。触发器是一种特殊...
触发器（trigger）
Hello World
05-21 8270
触发器 Trigger创建触发器创建只有一个执行语句的触发器创建有多个执行语句的触发器NEW与OLD详解查看触发器三、删除触发器 什么是触发器？ 　触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。 　 触发器的特点： 　1、有begin end体，begin end;之间的语句可以写的简单或者复杂 　2、什么条件会触发：Input、Delete、Update 　3、什么时候触发：在增删改前或者后 　4、触发频率：针对每一行执行 　5、触发器定义在表上，附着在表上。 建议不
Trigger(触发器)
Poor - Because you have no ambition
10-23 667
Trigger(触发器)流式查询的触发器定义了流式数据处理的时间, 流式查询根据触发器的不同, 可以是根据固定的批处理间隔进行微批处理查询, 也可以是连续的查询.(experimental 2.3 引入)
触发器（Trigger）
weixin_45538732的博客
09-07 955
触发器（Trigger）
【MySQL】触发器trigger / 事件
xuwuuu的博客
12-13 1199
运行结果：payments_audit表中记录了两条操作记录。结果：原来id为3的total是0，触发器后自动更新为10。创建时间后，删除了表中的数据。
Mysql中禁用与启动触发器教程【推荐】
09-09
在MySQL数据库中，触发器是一种强大的工具，它允许在特定的数据操作（如INSERT、UPDATE或DELETE）发生时自动执行预定义的SQL语句。然而，触发器的误用可能导致不必要的性能消耗或者逻辑错误。因此，了解如何禁用和...
mysql事件、触发器
Kang
01-13 2787
公司前两天要做一个系统每日自动拨币的事件，刚开始想到的是关闭浏览器交互ignore_user_abort();配合程序睡眠sleep（），但是公司的服务器在阿里云，经常发生停顿，导致事件终止执行。而且set_time_limit(0);只能在linux当中修改ini，修改后，就对其它页面跟程序产生了影响，本人linux的Crontab又不熟悉，所以就想到了mysql事件，通过任务计划到点后事件改变
MySQL--触发器和事件
weixin_44914446的博客
03-10 3066
触发器和事件 一、触发器 1、触发器的基本概念 ​ 触发器是和表相关的一种数据库对象，可以将他看作一种特殊的存储过程，不需要人为调动的存储过程。 关键字：trigger 基本作用：通过对表进行数据的插入、更新或删除等操作来触发，从而执行某些特定的操作。 2、触发器的创建 可以在MySql命令界面通过’? create trigger '命令查看创建触发器的格式。 mysql> ? create trigger Description: Syntax: CREATE [DEFINER = u
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值