数据库基础篇 《17.触发器》
数据库基础篇 《17.触发器》
在实际开发中,我们经常会遇到这样的情况:有 2 个或者多个相互关联的表,如商品信息和库存信息分别存放在 2 个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性,必须同时在库存表中添加一条库存记录。
这样一来,我们就必须把这两个关联的操作步骤写到程序里面,而且要用事务包裹起来,确保这两个操作成为一个原子操作,要么全部执行,要么全部不执行。要是遇到特殊情况,可能还需要对数据进行手动维护,这样就很容易忘记其中的一步,导致数据缺失。
这个时候,咱们可以使用触发器。**你可以创建一个触发器,让商品信息数据的插入操作自动触发库存数据的插入操作。**这样一来,就不用担心因为忘记添加库存数据而导致的数据缺失了。
1. 触发器概述
MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样,都是嵌入到MySQL服务器的一段程序。
触发器是由事件来触发某个操作,这些事件包括INSERT、UPDATE、DELETE事件。所谓事件就是指用户的动作或者触发某项行为。如果定义了触发程序,当数据库执行这些语句时候,就相当于事件发生了,就会自动激发触发器执行相应的操作。
当对数据表中的数据执行插入、更新和删除操作,需要自动执行一些数据库逻辑时,可以使用触发器来实现。
2. 触发器的创建
2.1 创建触发器语法
创建触发器的语法结构是:
CREATE TRIGGER 触发器名称
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;
说明:
-
表名:表示触发器监控的对象。 -
BEFORE|AFTER:表示触发的时间。BEFORE 表示在事件之前触发;AFTER 表示在事件之后触发。 -
INSERT|UPDATE|DELETE:表示触发的事件。- INSERT 表示插入记录时触发;
- UPDATE 表示更新记录时触发;
- DELETE 表示删除记录时触发。
-
触发器执行的语句块:可以是单条SQL语句,也可以是由BEGIN…END结构组成的复合语句块。
2.2 代码举例
举例1:
1、创建数据表:
CREATE TABLE test_trigger (
id INT PRIMARY KEY AUTO_INCREMENT,
t_note VARCHAR(30)
);CREATE TABLE test_trigger_log (
id INT PRIMARY KEY AUTO_INCREMENT,
t_log VARCHAR(30)
);
2、创建触发器:创建名称为before_insert的触发器,向test_trigger数据表插入数据之前,向test_trigger_log数据表中插入before_insert的日志信息。
DELIMITER //CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGININSERT INTO test_trigger_log (t_log)VALUES('before_insert');END //DELIMITER ;
3、向test_trigger数据表中插入数据
INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');
4、查看test_trigger_log数据表中的数据
mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
+----+---------------+
1 row in set (0.00 sec)
举例2:
1、创建名称为after_insert的触发器,向test_trigger数据表插入数据之后,向test_trigger_log数据表中插入after_insert的日志信息。
DELIMITER //CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGININSERT INTO test_trigger_log (t_log)VALUES('after_insert');
END //DELIMITER ;
2、向test_trigger数据表中插入数据。
INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');
3、查看test_trigger_log数据表中的数据
mysql> SELECT * FROM test_trigger_log;
+----+---------------+
| id | t_log |
+----+---------------+
| 1 | before_insert |
| 2 | before_insert |
| 3 | after_insert |
+----+---------------+
3 rows in set (0.00 sec)
举例3: 定义触发器“salary_check_trigger”,基于员工表“employees”的INSERT事件,在INSERT之前检查将要添加的新员工薪资是否大于他领导的薪资,如果大于领导薪资,则报sqlstate_value为’HY000’的错误,从而使得添加失败。
DELIMITER //CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGINDECLARE mgrsalary DOUBLE;SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;IF NEW.salary > mgrsalary THENSIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';END IF;
END //DELIMITER ;
上面触发器声明过程中的NEW关键字代表INSERT添加语句的新记录。
3. 查看、删除触发器
3.1 查看触发器
查看触发器是查看数据库中已经存在的触发器的定义、状态和语法信息等。
方式1:查看当前数据库的所有触发器的定义
SHOW TRIGGERS\G
方式2:查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
方式3:从系统库information_schema的TRIGGERS表中查询“salary_check_trigger”触发器的信息。
SELECT * FROM information_schema.TRIGGERS;
3.2 删除触发器
触发器也是数据库对象,删除触发器也用DROP语句,语法格式如下:
DROP TRIGGER IF EXISTS 触发器名称;
4. 触发器的优缺点
4.1 优点
1、触发器可以确保数据的完整性。
假设我们用进货单头表(demo.importhead)来保存进货单的总体信息,包括进货单编号、供货商编号、仓库编号、总计进货数量、总计进货金额和验收日期。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Y1dSOUux-1682450111129)(images/image-20211010233336012.png)]](https://img-blog.csdnimg.cn/57b2d2fa808e4e4eb55b7298dc17da3d.png)
用进货单明细表(demo.importdetails)来保存进货商品的明细,包括进货单编号、商品编号、进货数量、进货价格和进货金额。
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6h6XPnWa-1682450111130)(images/image-20211010233344125.png)]](https://img-blog.csdnimg.cn/ba3a09f9c3124d38a46f814d2568c7cf.png)
每当我们录入、删除和修改一条进货单明细数据的时候,进货单明细表里的数据就会发生变动。这个时候,在进货单头表中的总计数量和总计金额就必须重新计算,否则,进货单头表中的总计数量和总计金额就不等于进货单明细表中数量合计和金额合计了,这就是数据不一致。
为了解决这个问题,我们就可以使用触发器,规定每当进货单明细表有数据插入、修改和删除的操作时,自动触发 2 步操作:
1)重新计算进货单明细表中的数量合计和金额合计;
2)用第一步中计算出来的值更新进货单头表中的合计数量与合计金额。
这样一来,进货单头表中的合计数量与合计金额的值,就始终与进货单明细表中计算出来的合计数量与合计金额的值相同,数据就是一致的,不会互相矛盾。
2、触发器可以帮助我们记录操作日志。
利用触发器,可以具体记录什么时间发生了什么。比如,记录修改会员储值金额的触发器,就是一个很好的例子。这对我们还原操作执行时的具体场景,更好地定位问题原因很有帮助。
3、触发器还可以用在操作数据前,对数据进行合法性检查。
比如,超市进货的时候,需要库管录入进货价格。但是,人为操作很容易犯错误,比如说在录入数量的时候,把条形码扫进去了;录入金额的时候,看串了行,录入的价格远超售价,导致账面上的巨亏……这些都可以通过触发器,在实际插入或者更新操作之前,对相应的数据进行检查,及时提示错误,防止错误数据进入系统。
4.2 缺点
1、触发器最大的一个问题就是可读性差。
因为触发器存储在数据库中,并且由事件驱动,这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。
比如,创建触发器用于修改会员储值操作。如果触发器中的操作出了问题,会导致会员储值金额更新失败。我用下面的代码演示一下:
mysql> update demo.membermaster set memberdeposit=20 where memberid = 2;
ERROR 1054 (42S22): Unknown column 'aa' in 'field list'
结果显示,系统提示错误,字段“aa”不存在。
这是因为,触发器中的数据插入操作多了一个字段,系统提示错误。可是,如果你不了解这个触发器,很可能会认为是更新语句本身的问题,或者是会员信息表的结构出了问题。说不定你还会给会员信息表添加一个叫“aa”的字段,试图解决这个问题,结果只能是白费力。
2、相关数据的变更,可能会导致触发器出错。
特别是数据表结构的变更,都可能会导致触发器出错,进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性,影响到应用中错误原因排查的效率。
4.3 注意点
注意,如果在子表中定义了外键约束,并且外键指定了ON UPDATE/DELETE CASCADE/SET NULL子句,此时修改父表被引用的键值或删除父表被引用的记录行时,也会引起子表的修改和删除操作,此时基于子表的UPDATE和DELETE语句定义的触发器并不会被激活。
例如:基于子表员工表(t_employee)的DELETE语句定义了触发器t1,而子表的部门编号(did)字段定义了外键约束引用了父表部门表(t_department)的主键列部门编号(did),并且该外键加了“ON DELETE SET NULL”子句,那么如果此时删除父表部门表(t_department)在子表员工表(t_employee)有匹配记录的部门记录时,会引起子表员工表(t_employee)匹配记录的部门编号(did)修改为NULL,但是此时不会激活触发器t1。只有直接对子表员工表(t_employee)执行DELETE语句时才会激活触发器t1。
4. 触发器的优缺点课后练习
练习1:
0. 准备工作
CREATE DATABASE test17_trigger;USE test17_trigger;CREATE TABLE emps
AS
SELECT employee_id,last_name,salary
FROM atguigudb.`employees`;SELECT * FROM emps;
1. 复制一张emps表的空表emps_back,只有表结构,不包含任何数据
CREATE TABLE emps_back
AS
SELECT * FROM emps
WHERE 1 = 2;
2. 查询emps_back表中的数据
SELECT * FROM emps_back;
3. 创建触发器emps_insert_trigger,每当向emps表中添加一条记录时,同步将这条记录
#添加到emps_back表中DELIMITER //CREATE TRIGGER emps_insert_trigger
AFTER INSERT ON emps
FOR EACH ROW
BEGIN#将新添加到emps表中的记录添加到emps_back表中INSERT INTO emps_back(employee_id,last_name,salary)VALUES(NEW.employee_id,NEW.last_name,NEW.salary);
END //DELIMITER ;#show triggers;
4. 验证触发器是否起作用
SELECT * FROM emps;SELECT * FROM emps_back;INSERT INTO emps(employee_id,last_name,salary)
VALUES(301,'Tom1',3600);
练习2:
#0. 准备工作:使用练习1中的emps表#1. 复制一张emps表的空表emps_back1,只有表结构,不包含任何数据CREATE TABLE emps_back1
AS
SELECT *
FROM emps
WHERE 1 = 2;
#2. 查询emps_back1表中的数据
SELECT * FROM emps_back1;
3. 创建触发器emps_del_trigger,每当向emps表中删除一条记录时,同步将删除的这条记录添加到emps_back1表中
DELIMITER //CREATE TRIGGER emps_del_trigger
BEFORE DELETE ON emps
FOR EACH ROW
BEGIN#将emps表中删除的记录,添加到emps_back1表中。INSERT INTO emps_back1(employee_id,last_name,salary)VALUES(OLD.employee_id,OLD.last_name,OLD.salary);
END //DELIMITER ;
4. 验证触发器是否起作用
DELETE FROM emps
WHERE employee_id = 101;DELETE FROM emps;SELECT * FROM emps;SELECT * FROM emps_back1;
相关文章:
数据库基础篇 《17.触发器》
数据库基础篇 《17.触发器》 在实际开发中,我们经常会遇到这样的情况:有 2 个或者多个相互关联的表,如商品信息和库存信息分别存放在 2 个不同的数据表中,我们在添加一条新商品记录的时候,为了保证数据的完整性&#…...
03 - 大学生如何使用GPT
大学生如何使用GPT提高学习效率 一、引言 在当今的高速发展的信息时代,大学生面临着越来越多的学习挑战。作为一种先进的人工智能技术,GPT为大学生提供了一种强大的学习工具。本文将介绍大学生在不同场景中如何使用GPT来提高学习效率,并给出…...
【P1】Jmeter 准备工作
文章目录 一、Jmeter 介绍1.1、Jmeter 有什么样功能1.2、Jmeter 与 LoadRunner 比较1.3、常用性能测试工具1.4、性能测试工具如何选型1.5、学习 Jmeter 对 Java 编程的要求 二、Jmeter 软件安装2.1、官网介绍2.2、JDK 安装及环境配置2.3、Jmeter 三种模式2.4、主要配置介绍2.4.…...
字节的面试,你能扛住几道?
C , Python 哪一个更快? 读者答:这个我不知道从哪方面说,就是 C 的话,它其实能够提供开发者非常多的权限,就是说它能涉及到一些操作系统级别的一些操作,速度应该挺快。然后 Python 实现功能还…...
NOPI用法之自定义单元格背景色(3)
NPOI针对office2003使用HSSFWorkbook,对于offce2007及以上使用XSSFWorkbook;今天我以HSSFWorkbook自定义颜色为例说明,Office2007的未研究呢 在NPOI中默认的颜色类是HSSFColor,它内置的颜色有几十种供我们选择,如果不…...
数据分析中常见标准的参考文献
做数据分析过程中,有些分析法方法的标准随便一搜就能找到,不管是口口相传还是默认,大家都按那样的标准做了。日常分析不细究出处还可以,但是正式的学术论文你需要为你写下的每一句话负责,每一个判断标准都应该有参考文…...
辨析 变更请求、批准的变更请求、实施批准的变更请求
变更请求、批准的变更请求、实施批准的变更请求辨析 辨析各种变更请求,不服来辨。 变更请求 定义:对正规受控的文件或计划(范围、进度、成本、政策、过程、计划或程序)等的变更,以反映修改或增加的意见或内容 根据变更请求的工作内容可将变…...
leetcode 561. 数组拆分
题目描述解题思路执行结果 leetcode 561. 数组拆分 题目描述 数组拆分 给定长度为 2n 的整数数组 nums ,你的任务是将这些数分成 n 对, 例如 (a1, b1), (a2, b2), ..., (an, bn) ,使得从 1 到 n 的 min(ai, bi) 总和最大。 返回该 最大总和 。 示例 1&am…...
AviatorScript
AviatorScript 是一门高性能、轻量级寄宿于 JVM (包括 Android 平台)之上的脚本语言 特性介绍 支持数字、字符串、正则表达式、布尔值、正则表达式等基本类型,完整支持所有 Java 运算符及优先级等。函数是一等公民,支持闭包和函…...
Oracle跨服务器取数——DBlink 初级使用
前言 一句话解释DBlink是干啥用的 实现跨库访问的可能性. 通过DBlink我们可以在A数据库访问到B数据库中的所有信息,例如我们在加工FDS层表时需要访问ODS层的表,这是就需要跨库访问 一、DBlink的分类 private:用户级别,只有创建该dblink的用户才可以使…...
200人 500人 园区网设计
实验要求: ① 设置合理的STP优先级、边缘端口、Eth-trunk ② 企业内网划分多个vlan ,减少广播域大小,提高网络稳定性 ③ 所有设备,在任何位置都可以telnet远程管理 ④ 出口配置NAT ⑤ 所有用户均为自动获取ip地址 ⑥ 在企业…...
netstat命令解析
一、linux系统中netstat命令的帮助信息 └──╼ $netstat -h usage: netstat [-vWeenNcCF] [<Af>] -r netstat {-V|--version|-h|--help}netstat [-vWnNcaeol] [<Socket> ...]netstat { [-vWeenNac] -i | [-cnNe] -M | -s [-6tuw] }-r, --route …...
API接口的自我阐述
API(Application Programming Interface),翻译为应用程序接口,是一套定义程序之间如何通讯的接口。API可以实现软件的可重用性、可维护性和互操作性,同时也可以提升软件的性能和安全性。API接口是一个软件系统中的重要…...
Day32内部类
内部类 内部类就是在一个类中定义一个类,(在A类中定义一个B类,B类就被称为内部类) 格式:public class 类名{ 修饰符 class 类名{} } 如:public class Outer{ public class Inner {} } //内部类可以访问外部…...
用户画像系列——HBase 在画像标签过期策略中的应用
一、背景 前面系列文章介绍了用户画像的概念、用户画像的标签加工、用户画像的应用。本篇文章主要介绍一些画像的技术细节,让大家更加详细的了解画像数据存储和处理的逻辑 举个现实中的例子: 例子1:因为疫情原因,上线一个平台(…...
时下热门话题:ChatGPT能否取代人类?
时下热门话题:ChatGPT能否取代人类? 2022年11月底,人工智能对话聊天机器人ChatGPT推出,迅速在社交媒体上走红,短短5天,注册用户数就超过100万。2023年1月末,ChatGPT的月活用户已突破1亿&#x…...
每日刷题记录(十七)
目录 第一题:求12...n解题思路:代码实现: 第二题:两两交换链表中的节点解题思路:代码实现: 第三题:只出现一次的数字 II解题思路:代码实现: 第四题:根据字符串…...
开放原子训练营(第三季)RT-Thread Nano学习营一探究竟
前言 不知道从什么时候起,智能设备开始普及到了我们日常生活的方方面面。下班还未到家,热水器就可以调到合适的温度;上班刚进公司,忘关空调也不再是什么烦恼;诸如夜晚的灯光变换,白昼的窗帘适应等更给我们…...
数据库系统概论(二)关系数据库,SQL概述和数据库安全性
作者的话 前言:总结下知识点,自己偶尔看一看。 目录 一、关系模型概述 1.1关系数据结构及形式化定义 1.1.1域(Domain) 1.1.2笛卡尔积(Cartesian Product) 1.1.3关系(Relation) …...
【VM服务管家】VM4.x算子SDK开发_3.1 环境配置类
目录 3.1.1 环境配置:CSharp算子SDK开发环境配置方法3.1.2 算子封装:使用C封装算子SDK的方法3.1.3 异常中断:算子SDK软件运行报错“托管调试助手”中断的解决方法3.1.4 深度学习:GPU运行深度学习算子引发StackOverFlow异常的方法 …...
业务系统对接大模型的基础方案:架构设计与关键步骤
业务系统对接大模型:架构设计与关键步骤 在当今数字化转型的浪潮中,大语言模型(LLM)已成为企业提升业务效率和创新能力的关键技术之一。将大模型集成到业务系统中,不仅可以优化用户体验,还能为业务决策提供…...
2025年能源电力系统与流体力学国际会议 (EPSFD 2025)
2025年能源电力系统与流体力学国际会议(EPSFD 2025)将于本年度在美丽的杭州盛大召开。作为全球能源、电力系统以及流体力学领域的顶级盛会,EPSFD 2025旨在为来自世界各地的科学家、工程师和研究人员提供一个展示最新研究成果、分享实践经验及…...
Spring Boot面试题精选汇总
🤟致敬读者 🟩感谢阅读🟦笑口常开🟪生日快乐⬛早点睡觉 📘博主相关 🟧博主信息🟨博客首页🟫专栏推荐🟥活动信息 文章目录 Spring Boot面试题精选汇总⚙️ **一、核心概…...
《基于Apache Flink的流处理》笔记
思维导图 1-3 章 4-7章 8-11 章 参考资料 源码: https://github.com/streaming-with-flink 博客 https://flink.apache.org/bloghttps://www.ververica.com/blog 聚会及会议 https://flink-forward.orghttps://www.meetup.com/topics/apache-flink https://n…...
ArcGIS Pro制作水平横向图例+多级标注
今天介绍下载ArcGIS Pro中如何设置水平横向图例。 之前我们介绍了ArcGIS的横向图例制作:ArcGIS横向、多列图例、顺序重排、符号居中、批量更改图例符号等等(ArcGIS出图图例8大技巧),那这次我们看看ArcGIS Pro如何更加快捷的操作。…...
Device Mapper 机制
Device Mapper 机制详解 Device Mapper(简称 DM)是 Linux 内核中的一套通用块设备映射框架,为 LVM、加密磁盘、RAID 等提供底层支持。本文将详细介绍 Device Mapper 的原理、实现、内核配置、常用工具、操作测试流程,并配以详细的…...
省略号和可变参数模板
本文主要介绍如何展开可变参数的参数包 1.C语言的va_list展开可变参数 #include <iostream> #include <cstdarg>void printNumbers(int count, ...) {// 声明va_list类型的变量va_list args;// 使用va_start将可变参数写入变量argsva_start(args, count);for (in…...
BLEU评分:机器翻译质量评估的黄金标准
BLEU评分:机器翻译质量评估的黄金标准 1. 引言 在自然语言处理(NLP)领域,衡量一个机器翻译模型的性能至关重要。BLEU (Bilingual Evaluation Understudy) 作为一种自动化评估指标,自2002年由IBM的Kishore Papineni等人提出以来,…...
OD 算法题 B卷【正整数到Excel编号之间的转换】
文章目录 正整数到Excel编号之间的转换 正整数到Excel编号之间的转换 excel的列编号是这样的:a b c … z aa ab ac… az ba bb bc…yz za zb zc …zz aaa aab aac…; 分别代表以下的编号1 2 3 … 26 27 28 29… 52 53 54 55… 676 677 678 679 … 702 703 704 705;…...
AI语音助手的Python实现
引言 语音助手(如小爱同学、Siri)通过语音识别、自然语言处理(NLP)和语音合成技术,为用户提供直观、高效的交互体验。随着人工智能的普及,Python开发者可以利用开源库和AI模型,快速构建自定义语音助手。本文由浅入深,详细介绍如何使用Python开发AI语音助手,涵盖基础功…...
