数据库原理学习——MySql触发器详解

目录

一、什么是触发器（Trigger）？

二、触发器的特点

三、MySQL中触发器的用法、语法

1. 定义触发器  2. 触发器逻辑开始

3. 触发条件检查  4. 删除逻辑

5. 结束逻辑  6. DELIMITER 语句

四、示例演示触发器的执行过程

五、触发器的应用场景（六种场景，含示例代码）

补充：触发器如何关闭

总结

一、什么是触发器（Trigger）？

触发器（Trigger）是数据库中的一种特殊存储程序，它绑定到某张表（或视图）上，并在特定的数据库操作（如 INSERT、UPDATE 或 DELETE）发生时自动执行预定义的操作。触发器无需手动调用，是一种事件驱动的机制。

二、触发器的特点

  1. 自动执行：一旦满足触发条件，触发器会在相关操作执行之前或之后自动运行。

  2. 绑定表：每个触发器都与特定的表绑定，只对该表的操作有效。

  3. 操作类型：常见触发器类型包括：

    • AFTER DELETE：在删除数据之后执行。

    • BEFORE DELETE：在删除数据之前执行。

    • AFTER UPDATE：在更新数据之后执行。

    • BEFORE UPDATE：在更新数据之前执行。

    • AFTER INSERT：在插入数据之后执行。

    • BEFORE INSERT：在插入数据之前执行。

  4. 适用范围：

    • 触发器作用于每一行操作（FOR EACH ROW），或是整个语句的操作（FOR EACH STATEMENT，但在 MySQL 中不支持）。

三、MySQL中触发器的用法、语法

下面是一个用于清理数据的触发器代码示例，为了使大家更好的理解触发器的使用，下面将为大家逐帧分析每句代码的作用以及使用方法。

(题目：在社区表community中，当新增新的小区时；触发事件为当前新增小区这个insert操作，条件是：新增小区楼栋数量 大于20栋 且 住户不低于150人，若满足条件则：在访客记录表manual_record中查询当前所有已离开的访客记录，将离开时间（out_time）距离现在已经是一年以前的记录都删除； )

DELIMITER $$
​
CREATE TRIGGER update_is_leave_cleanup
AFTER INSERT ON community
FOR EACH ROW
BEGIN
  -- 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人
  IF NEW.term_count > 20 AND NEW.per_count >= 150 THEN
    -- 删除所有离开时间超过一年的访客记录
    DELETE FROM manual_record
    WHERE is_leave = 1
      AND out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);
  END IF;
END$$
​
DELIMITER ;

这段代码定义了一个 MySQL 触发器，用于在对表 community执行 insert操作后自动执行特定逻辑。以下是逐步的解释：

1. 定义触发器
CREATE TRIGGER update_is_leave_cleanup
AFTER INSERTON community
FOR EACH ROW

  • CREATE TRIGGER update_is_leave_cleanup：创建一个名为 update_is_leave_cleanup 的触发器。

  • AFTER INSERT：表示在 community表执行 INSERT操作后触发该触发器。

  • ON community：触发器绑定到 community表。

  • FOR EACH ROW：触发器对 UPDATE 操作的每一行都生效（逐行触发）。

2. 触发器逻辑开始
BEGIN
...
end

  • BEGIN 和 END 标志触发器主体，表示触发器的逻辑操作。

3. 触发条件检查
 -- 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人
  IF NEW.term_count > 20 AND NEW.per_count >= 150 THEN

  • NEW.字段名：表示插入的新值。

  • 条件含义：

    • 检查新增小区楼栋数量是否 大于20栋 且 住户是否不低于150人。

    • 如果 NEW.term_count > 20 且 NEW.per_count >= 150，说明当前新增记录符合条件。

2. OLD.字段名的补充：

  • OLD 是在 DELETE 或 UPDATE 触发器中使用的，用于引用被修改或删除的 旧数据。
  • 在 INSERT 触发器中，OLD 不可用，因为没有“旧数据”。
触发器类型NEWOLD
BEFORE INSERT可用（可修改）不可用
AFTER INSERT可用（只读）不可用
BEFORE UPDATE可用（可修改）可用（只读）
AFTER UPDATE可用（只读）可用（只读）
BEFORE DELETE不可用可用（只读）
AFTER DELETE不可用可用（只读）
4. 删除逻辑
DELETE FROM manual_record
WHERE is_leave = 1
  AND out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

  • DELETE FROM manual_record：删除符合条件的记录。

条件 1：is_leave = 1：

  • 仅删除已经离开的记录。

条件 2：out_time < DATE_SUB(NOW(), INTERVAL 1 YEAR)：

  • out_time 是记录的离开时间。

  • DATE_SUB(NOW(), INTERVAL 1 YEAR) 计算当前时间减去 1 年的日期。

  • 如果 out_time 早于一年前，则认为该记录已过期，删除之。

5. 结束逻辑
END IF;
END$$

  • END IF;：结束条件语句。

  • END$$：标志触发器逻辑结束。

6. DELIMITER 语句
DELIMITER $$

  • 默认的 MySQL 语句分隔符是 ;。

  • 在创建触发器时，为了避免触发器内部的 ; 与默认分隔符冲突，使用 DELIMITER 改变分隔符（如 $$）。

  • DELIMITER ;：在触发器定义完成后，将分隔符改回默认值。

7.功能总结

  • 触发器目标：

    1. 检查新增小区是否满足指定条件。

    2. 删除manual_record表中所有满足以下条件的记录：

      ①is_leave = 1（表示访客已离开）。
      ②out_time 距离当前时间已有 1 年以前。

  • 触发时机： 每当 community表中新增（楼栋数量大于20且住户不低于150人的）小区时，该触发器会自动执行。

四、示例演示触发器的执行过程

以下步骤将演示如何使用上述触发器。

1. 表结构准备

创建触发器涉及到两张表：community 和 manual_record。我们先定义表结构：

community 表
CREATE TABLE 
community ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    name VARCHAR(255), term_count INT, -- 楼栋数量 
    per_count INT -- 住户数量 
);
manual_record 表
CREATE TABLE 
manual_record ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    is_leave TINYINT(1), -- 是否离开 (1: 已离开, 0: 未离开) 
    out_time DATETIME -- 离开时间 
);
2. 插入数据

在触发器触发前，我们需要确保 manual_record 表中有符合条件的数据：

向 manual_record 表插入数据
INSERT INTO manual_record (is_leave, out_time) 
VALUES (1, '2022-12-01 12:00:00'), -- 超过一年的离开记录 
(1, '2023-06-01 12:00:00'), -- 未超过一年的离开记录 
(0, '2023-12-01 12:00:00'); -- 未离开的记录
查看 manual_record 表内容
SELECT * FROM manual_record;

结果：

idis_leaveout_time
112022-12-01 12:00:00
212023-06-01 12:00:00
302023-12-01 12:00:00
3. 触发器测试
向 community 表插入满足条件的记录

触发器的条件是 term_count > 20 且 per_count >= 150。执行以下插入语句：

INSERT INTO community (name, term_count, per_count) 
VALUES ('Example Community', 25, 200);
检查触发器执行结果

触发器应该已经被触发，并从 manual_record 表中删除所有离开时间超过一年的记录。

SELECT * FROM manual_record;

结果：

idis_leaveout_time
212023-06-01 12:00:00
302023-12-01 12:00:00

解释：

  • ID 为 1 的记录已被删除，因为它的离开时间（2022-12-01）超过了一年。
  • ID 为 2 和 3 的记录未被删除，因为它们不满足删除条件。
4. 插入不满足条件的记录

验证触发器不会在条件不满足时执行：

INSERT INTO community (name, term_count, per_count)
 VALUES ('Small Community', 15, 100);

再次检查 manual_record 表，结果没有变化，仍为：

idis_leaveout_time
212023-06-01 12:00:00
302023-12-01 12:00:00

解释： 触发器未触发，因为 term_count = 15 和 per_count = 100 不满足条件。

注意事项

  1. 触发器性能：如果表数据量大，触发器中执行 DELETE 操作可能会影响性能。

  2. 事务支持：如果 UPDATE 操作失败，触发器逻辑也不会执行。

  3. 调试触发器：可以通过启用 MySQL 日志或手动检查数据变化来调试触发器。

  4. 数据备份：删除记录操作是不可逆的，在执行触发器前确保数据已备份。

如有其他问题，可以进一步优化触发器逻辑！ 😊

 五、触发器的应用场景

以下是六个适合使用触发器的实际项目场景，包含基础代码；

1. 数据完整性和一致性

  • 确保数据满足特定的业务规则，避免人为错误。

  • 触发器代码示例①：在用户表插入新记录时，自动设置默认值或校验数据合法性。

    CREATE TRIGGER set_default_role
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.role IS NULL THEN
    SET NEW.role = 'user';
  END IF;
END;

2. 自动审计（Audit Log）

  • 自动记录对表的增删改操作，追踪数据变化历史。

  • 触发器代码示例②：在修改数据时，记录操作日志。

    CREATE TRIGGER log_updates
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (table_name, action, old_value, new_value, change_time)
  VALUES ('employees', 'UPDATE', OLD.salary, NEW.salary, NOW());
END;

3. 级联操作

  • 自动处理与主表相关的其他表数据，以保持数据一致性。

  • 触发器代码示例③：在删除订单时，同时删除与该订单相关的所有明细记录。

    CREATE TRIGGER cascade_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  DELETE FROM order_details WHERE order_id = OLD.id;
END;

4. 定期清理数据

  • 对过期或冗余的数据进行自动清理。

  • 触发器代码示例④：当用户状态更新为注销时，自动清理其关联的会话数据。

    CREATE TRIGGER cleanup_sessions
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF NEW.status = 'deactivated' THEN
    DELETE FROM sessions WHERE user_id = NEW.id;
  END IF;
END;

5. 复杂计算或衍生数据更新

  • 根据某些操作自动更新相关的统计信息或衍生数据。

  • 触发器代码示例⑤：当订单状态更新时，重新计算用户累计消费金额。

    CREATE TRIGGER recalculate_total_spent
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  UPDATE customers
  SET total_spent = (SELECT SUM(total_price) FROM orders WHERE customer_id = NEW.customer_id)
  WHERE id = NEW.customer_id;
END;

6. 安全控制

  • 限制对敏感数据的非法修改或记录潜在违规行为。

  • 触发器代码示例⑥：防止员工工资被修改为负值。

    CREATE TRIGGER prevent_negative_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Salary cannot be negative';
  END IF;
END;

六、触发器的优缺点

优点：

  1. 自动化：无需手动触发或调用，减少代码冗余。

  2. 提高数据一致性：通过预定义规则，确保数据满足业务逻辑。

  3. 分离业务逻辑：将部分逻辑从应用程序移到数据库，集中管理。

  4. 安全性增强：可以避免应用层的意外或恶意操作。

缺点：

  1. 调试困难：触发器是隐式执行的，不易定位问题。

  2. 性能开销：复杂的触发器逻辑可能对数据库性能造成影响，尤其是高并发场景。

  3. 不可移植性：触发器依赖于特定数据库的实现，迁移到其他数据库可能需要重写。

  4. 隐藏逻辑：触发器的自动执行特性可能让开发人员忽略其作用，导致意外行为。

补充：触发器如何关闭

在 MySQL 中，可以通过以下两种方法关闭触发器：

1. 禁用触发器

  • 可以使用命令临时禁用触发器，以停止触发器的执行，而无需删除触发器定义。
  • 需要注意的是，MySQL 不支持直接禁用单个触发器，但可以通过禁用整个表的触发器来达到类似的效果。
禁用所有触发器（全局禁用）
SET GLOBAL DISABLE_TRIGGERS = 1;
启用所有触发器（全局启用）
SET GLOBAL DISABLE_TRIGGERS = 0;

2. 删除触发器

如果需要永久关闭某个触发器，可以直接使用 DROP TRIGGER 命令删除触发器。

删除触发器的语法
DROP TRIGGER [IF EXISTS] trigger_name;
示例

假设有一个触发器 名称为：update_is_leave_cleanup，可以用以下命令删除：

DROP TRIGGER IF EXISTS update_is_leave_cleanup;

3. 触发器删除注意事项

  1. 作用范围：触发器是绑定在特定表上的，删除触发器只会影响当前表，不会影响其他表。

  2. 权限要求：删除触发器需要具有 SUPER 或 TRIGGER 权限。

  3. 无法撤销：删除触发器是永久操作，触发器的定义不会被保存，因此建议在删除之前备份触发器定义。

4. 示例：完整操作流程

（1）查看现有触发器

可以查询数据库中的触发器列表，确认需要删除的触发器名称。

SHOW TRIGGERS;
（2）删除触发器
DROP TRIGGER IF EXISTS update_is_leave_cleanup;
（3）验证触发器是否已被删除

重新查询触发器列表，确认是否已经删除：

SHOW TRIGGERS;

触发器的使用建议：

  • 临时关闭触发器：建议尽量避免直接删除触发器，尤其是在调试或临时停用的情况下，可以通过全局禁用的方式实现。
  • 备份触发器：如果确实需要删除触发器，请提前备份触发器的定义，以便未来需要时快速恢复。

触发器的备份示例：

SHOW CREATE TRIGGER update_is_leave_cleanup \G;

此命令会显示触发器的创建语句，可以复制并保存以备后续使用。

总结

        触发器是一种强大的工具，用于增强数据库的自动化处理能力。它适合处理诸如数据校验、审计记录、自动清理、级联操作等任务，但应谨慎使用，避免复杂逻辑影响性能。如果业务需求涉及复杂逻辑，建议将部分逻辑移到应用程序中实现

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/942864.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

Flink调优----资源配置调优与状态及Checkpoint调优

Flink调优----资源配置调优与状态及Checkpoint调优

目录 第 1 章 资源配置调优 1.1 内存设置 1.1.1 TaskManager 内存模型 1、内存模型详解 2、案例分析 1.1.2 生产资源配置示例 1.2 合理利用 cpu 资源 1.2.1 使用 DefaultResourceCalculator 策略 1.2.2 使用 DominantResourceCalculator 策略 1.2.3 使用 DominantRes…
阅读更多...
MySQL 常用程序介绍

MySQL 常用程序介绍

以下是一些常用的MySQL程序&#xff1a; 程序名作⽤mysqldMySQL的守护进程即 MySQL 服务器&#xff0c;要使⽤MySQL 服务器 mysqld必须正在运⾏状态mysql MySQL客⼾端程序&#xff0c;⽤于交互式输⼊ SQL 语句或以批处理模式从⽂件执⾏SQL的命令⾏⼯具 mysqlcheck⽤于检查、修…
阅读更多...
利用Gurobi追溯模型不可行原因的四种方案及详细案例

利用Gurobi追溯模型不可行原因的四种方案及详细案例

文章目录 1. 引言2. 追溯不可行集的四种方法2.1 通过约束增减进行判断2.2 通过computeIIS函数获得冲突集2.3 利用 feasRelaxS() 或 feasRelax() 函数辅助排查2.4 利用 IIS Force 属性1. 引言 模型不可行是一个让工程师头疼的问题，对于复杂模型而言，导致模型不可行的原因可能…
阅读更多...
【机器学习】机器学习的基本分类-半监督学习（Semi-supervised Learning）

【机器学习】机器学习的基本分类-半监督学习（Semi-supervised Learning）

半监督学习是一种介于监督学习和无监督学习之间的机器学习方法。它利用少量的标注数据&#xff08;有监督数据&#xff09;和大量的未标注数据&#xff08;无监督数据&#xff09;来进行模型训练&#xff0c;从而在标注数据不足的情况下&#xff0c;提升模型的性能。 半监督学习…
阅读更多...
西门子200smart实现TCP服务器源码分享

西门子200smart实现TCP服务器源码分享

1、创建TCP服务器 2、服务器故障复位 3、TCP发送数据 4、TCP接收数据
阅读更多...
攻防世界web第二题unseping

攻防世界web第二题unseping

这是题目 <?php highlight_file(__FILE__);class ease{private $method;private $args;function __construct($method, $args) {$this->method $method;$this->args $args;}function __destruct(){if (in_array($this->method, array("ping"))) {cal…
阅读更多...
Java如何避免过度打印日志导致性能问题

Java如何避免过度打印日志导致性能问题

文章目录 Java如何避免过度打印日志导致性能问题前言日常开发如何打印日志1、优化日志结构&#xff08;1&#xff09;不要打印一些没用的日志&#xff0c;也不要在 for 循环里面去打印&#xff08;2&#xff09;不要做字符串的拼接&#xff0c;多用占位符&#xff08;3&#xf…
阅读更多...
sentinel笔记9- 限流规则持久化（上）

sentinel笔记9- 限流规则持久化（上）

之前的在sentinel 控制台配置的规则&#xff0c;重启后就消失了&#xff0c;sentinel 限流保护-笔记-CSDN博客 本篇还是在之前的demo做验证&#xff0c;使用nacos做持久化。 规则集成Nacos 1 引入依赖 <!--nacos-discovery 注册中心依赖--><dependency><gr…
阅读更多...
服务器数据恢复—Lustre分布式文件系统下服务器节点进水的数据恢复案例

服务器数据恢复—Lustre分布式文件系统下服务器节点进水的数据恢复案例

服务器数据恢复环境&故障&#xff1a; 5台节点服务器&#xff0c;每台节点服务器上有一组RAID5阵列。每组RAID5阵列上有6块硬盘&#xff08;其中1块硬盘设置为热备盘&#xff0c;其他5块硬盘为数据盘&#xff09;。上层系统环境为Lustre分布式文件系统。 机房天花板漏水导致…
阅读更多...
Windows、CentOS环境下搭建自己的版本管理资料库：GitBlit

Windows、CentOS环境下搭建自己的版本管理资料库：GitBlit

可以搭建属于公司内部或者个人的Git服务器&#xff0c;方便程序代码及文档版本管理。 官网&#xff1a;http://www.gitblit.com/ Windows环境下安装 提前已经安装好了JDK。 官网下载Windows版的GitBlit。 将zip包解压到自己想要放置的文件夹下。 建立版本库路径&#xff0c…
阅读更多...
《2023-2024网络安全产业发展核心洞察与趋势预测》

《2023-2024网络安全产业发展核心洞察与趋势预测》

2023年至2024年间&#xff0c;我国经济总体上逐步显现出复苏迹象&#xff0c;并开始释放向上增长的潜力。在此背景下&#xff0c;网络安全产业也经历了经济环境的深刻影响&#xff0c;不仅实现了阶段性的稳定发展&#xff0c;也展现出较强的韧性与适应能力&#xff0c;为未来的…
阅读更多...
WebRTC服务质量（08）- 重传机制（05) RTX机制

WebRTC服务质量（08）- 重传机制（05) RTX机制

WebRTC服务质量&#xff08;01&#xff09;- Qos概述 WebRTC服务质量&#xff08;02&#xff09;- RTP协议 WebRTC服务质量&#xff08;03&#xff09;- RTCP协议 WebRTC服务质量&#xff08;04&#xff09;- 重传机制&#xff08;01) RTX NACK概述 WebRTC服务质量&#xff08;…
阅读更多...
Kibana8.17.0在mac上的安装

Kibana8.17.0在mac上的安装

1、Kibana是什么 Kibana是与elasticsearch配套使用的数据分析与可视化工具&#xff0c;通过Kibana可以轻松与es中存储的数据进行高效的交互&#xff0c;包括数据写入、检索、删除等操作&#xff0c;并可以通过编写部分代码将数据做成各种报表&#xff0c;从而进行非常直观的统…
阅读更多...
【Linux】：线程安全 + 死锁问题

【Linux】：线程安全 + 死锁问题

&#x1f4c3;个人主页&#xff1a;island1314 &#x1f525;个人专栏&#xff1a;Linux—登神长阶 ⛺️ 欢迎关注&#xff1a;&#x1f44d;点赞 &#x1f442;&#x1f3fd;留言 &#x1f60d;收藏 &#x1f49e; &#x1f49e; &#x1f49e; 1. 线程安全和重入问题&…
阅读更多...
AI应用-本地模型实现AI生成PPT(简易版)

AI应用-本地模型实现AI生成PPT(简易版)

文章目录 前言技术栈效果展示 一、实现思路二、实现步骤1.本地安装marp-cli2.后端实现3.前端实现 三、代码地址及说明 前言 在许多项目中&#xff0c;生成 PPT 是常见的需求&#xff0c;尤其在教育和报告展示中。传统的生成 PPT 的方法需要手动创建&#xff0c;而使用生成模型…
阅读更多...
Linux 服务管理 service systemd systemctl

Linux 服务管理 service systemd systemctl

Linux 服务管理 service systemd systemctl 1 介绍1.1 概述1.2 发展历程1.3 特点1.4 linux 服务管理 发展历程Ubuntu 14.04及更早版本&#xff1a;使用upstart作为默认的init系统旧版本新版本 Ubuntu 15.04到16.04版本&#xff1a;默认使用systemd作为init系统&#xff0c;但仍…
阅读更多...
vue 基础学习

vue 基础学习

一、ref 和reactive 区别 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><title>Title</title> </head> <body><div id"app"><h1>{{Web.title}}</h1><h1&…
阅读更多...
光谱相机在农业中的具体应用案例

光谱相机在农业中的具体应用案例

作物生长监测与产量预测 美国爱荷华州玉米种植园&#xff1a;农场主使用无人机搭载高光谱相机&#xff0c;定期对玉米田进行拍摄。通过分析光谱数据&#xff0c;获取玉米的叶面积指数、叶绿素含量等生长参数。在玉米生长关键期&#xff0c;依据这些参数及时调整施肥和灌溉方案…
阅读更多...
LSTM-SVM时序预测 | Matlab基于LSTM-SVM基于长短期记忆神经网络-支持向量机时间序列预测

LSTM-SVM时序预测 | Matlab基于LSTM-SVM基于长短期记忆神经网络-支持向量机时间序列预测

LSTM-SVM时序预测 | Matlab基于LSTM-SVM基于长短期记忆神经网络-支持向量机时间序列预测 目录 LSTM-SVM时序预测 | Matlab基于LSTM-SVM基于长短期记忆神经网络-支持向量机时间序列预测预测效果基本介绍程序设计参考资料 预测效果 基本介绍 1.LSTM-SVM时序预测 | Matlab基于LSTM…
阅读更多...
05.HTTPS的实现原理-HTTPS的握手流程（TLS1.2）

05.HTTPS的实现原理-HTTPS的握手流程（TLS1.2）

05.HTTPS的实现原理-HTTPS的握手流程&#xff08;TLS1.2&#xff09; 简介1. TLS握手过程概述2. TLS握手过程细化3. 主密钥&#xff08;对称密钥&#xff09;生成过程4. 密码规范变更 简介 主要讲述了混合加密流程完成后&#xff0c;客户端和服务器如何共同获得相同的对称密钥…
阅读更多...
最新文章

Copyright @ 2022~2023