当前位置： 首页 > article >正文

我在广州学Mysql 系列——触发器的使用

article 2025/1/22 23:51:54

ℹ️大家好，我是练小杰，这周是春节前的最后一周了，现在一双手数都能数得过来了！！
本播客将学习MYSQL中触发器的相关概念以及基础命令~~
回顾：👉【MYSQL视图相关例题】
数据库专栏👉【数据库专栏】~
想要了解更多内容，主页 【练小杰的CSDN】

在这里插入图片描述

文章目录

  • 触发器
    • 触发器的类型
    • 触发的条件
    • 触发器的优缺点
  • 创建触发器
    • 创建只有一个执行语句的触发器
    • 创建有多个执行语句的触发器
      • 一个触发器示例说明
  • 查看触发器
    • SHOW TRIGGERS语句查看触发器信息
    • `triggers`表中查看触发器信息
  • 删除触发器
  • 综合案例——触发器使用
    • 步骤1：创建persons表
    • 步骤2：创建一个销售额表sales
    • 步骤3：创建一个触发器
    • 步骤4：向persons表中插入记录
    • 步骤5：查看表persons和 sales
    • 步骤6：查看触发器

在这里插入图片描述

触发器

  • 定义：触发器是一种与表关联的数据库事件处理程序，当发生特定事件（如插入、更新、删除等）时，触发器会自动执行相关的SQL语句。
  • 同时，触发器的执行不是由程序调用或手工启动，而是由事件来触发的。
  • 在某些触发程序的用法中，可用于检查插入到表中的值，或对更新涉及的值进行计算。

触发器的类型

  • MySQL支持以下几种类型的触发器：

BEFORE触发器： 在指定操作（如INSERT、UPDATE、DELETE）发生之前执行。
AFTER触发器： 在指定操作发生之后执行。

触发的条件

  • 针对每种操作类型，触发器可以针对以下表的操作定义：
  • INSERT：当向表中插入新行时触发。
  • UPDATE：当更新表中的行时触发。
  • DELETE：当从表中删除行时触发。

触发器的优缺点

  • 优点：

自动化：触发器可以自动执行某些操作，减少手动操作的错误和工作量。
数据完整性：触发器可以加强数据的完整性约束，确保数据的合法性。

  • 缺点：

性能影响：触发器会在表操作时立即执行，复杂的业务逻辑会增加操作延迟。
调试困难：触发器的调试相对复杂，建议在开发阶段充分测试。
逻辑复杂：避免在触发器中编写过于复杂的逻辑，以免影响数据库的稳定性。

创建触发器

  • 基本语法如下：
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
trigger_body;
  • 命令说明：

trigger_name：触发器的名称。
BEFORE | AFTER：指定触发时机。
INSERT | UPDATE | DELETE ：指定触发事件。
table_name：触发器作用的表名。
FOR EACH ROW：表示触发器针对表中的每一行数据执行。
trigger_body ：定义触发器的业务逻辑，可以包含多个SQL语句。

创建只有一个执行语句的触发器

  • 创建一个触发器，语法如下:
CREATE TRIGGER trigger_name trigger_time  trigger_event
	ON tbl_name FOR EACH ROW trigger_stmt

创建有多个执行语句的触发器

  • 当需要在触发器中执行多个操作时，可以使用BEGIN...END块来包含这些语句。

一个触发器示例说明

假设我们有一个employees表和一个audit_log表。我们希望在向employees表中插入、更新或删除数据时，自动在audit_log表中记录相应的操作日志。

  • 定义表的结构
    创建employees和audit_log表：
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    position VARCHAR(100),
    salary DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(10),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details VARCHAR(255)
);
  • 触发器1：插入操作后记录日志

向employees表中插入新员工时，记录日志。

DELIMITER $$

CREATE TRIGGER after_insert_employees
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- 插入审计日志
    INSERT INTO audit_log (employee_id, action, details)
    VALUES (NEW.id, 'INSERT', CONCAT('Added new employee: ', NEW.name));

    -- 这里还可以添加更多操作，例如发送通知等
END$$

DELIMITER ;
  • 触发器2：更新操作后记录日志

每次更新employees表中的员工信息时，都记录日志。

DELIMITER $$

CREATE TRIGGER after_update_employees
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 检查是否有字段被修改
    IF NEW.name <> OLD.name OR NEW.position <> OLD.position OR NEW.salary <> OLD.salary THEN
        -- 插入审计日志
        INSERT INTO audit_log (employee_id, action, details)
        VALUES (NEW.id, 'UPDATE', CONCAT('Updated employee: ', NEW.name, 
                                         ', Position: ', NEW.position, 
                                         ', Salary: ', NEW.salary));
    END IF;
 
END$$

DELIMITER ;
  • 触发器3：删除操作后记录日志

从employees表中删除员工时，记录到日志里。

DELIMITER $$

CREATE TRIGGER after_delete_employees
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    -- 插入审计日志
    INSERT INTO audit_log (employee_id, action, details)
    VALUES (OLD.id, 'DELETE', CONCAT('Deleted employee: ', OLD.name));
  
END$$

DELIMITER ;

查看触发器

SHOW TRIGGERS语句查看触发器信息

  • 使用命令SHOW TRIGGERS查看触发器
SHOW TRIGGERS;

triggers表中查看触发器信息

  • 在MySQL中，所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT来查看。
  • 查询INFORMATION_SCHEMA表：
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'your_database_name';
  • 查询数据库sys的触发器信息
 SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = 'sys';
+-----------------+----------------+----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+----------------------+----------------------+--------------------+
| TRIGGER_CATALOG | TRIGGER_SCHEMA | TRIGGER_NAME               | EVENT_MANIPULATION | EVENT_OBJECT_CATALOG | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_ORDER | ACTION_CONDITION | ACTION_STATEMENT                                                                                                                  | ACTION_ORIENTATION | ACTION_TIMING | ACTION_REFERENCE_OLD_TABLE | ACTION_REFERENCE_NEW_TABLE | ACTION_REFERENCE_OLD_ROW | ACTION_REFERENCE_NEW_ROW | CREATED                | SQL_MODE                                                                                                              | DEFINER             | CHARACTER_SET_CLIENT | COLLATION_CONNECTION | DATABASE_COLLATION |
+-----------------+----------------+----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+----------------------+----------------------+--------------------+
| def             | sys            | sys_config_insert_set_user | INSERT             | def                  | sys                 | sys_config         |            1 |             NULL | BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END | ROW                | BEFORE        |                       NULL |                       NULL | OLD                      | NEW                      | 2024-12-25 19:56:15.08 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | mysql.sys@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
| def             | sys            | sys_config_update_set_user | UPDATE             | def                  | sys                 | sys_config         |            1 |             NULL | BEGIN
    IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN
        SET NEW.set_by = USER();
    END IF;
END | ROW                | BEFORE        |                       NULL |                       NULL | OLD                      | NEW                      | 2024-12-25 19:56:15.09 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | mysql.sys@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------------+----------------+----------------------------+--------------------+----------------------+---------------------+--------------------+--------------+------------------+-----------------------------------------------------------------------------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------+--------------------------+--------------------------+------------------------+-----------------------------------------------------------------------------------------------------------------------+---------------------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)

删除触发器

  • 使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器。
DROP TRIGGER IF EXISTS trigger_name;

综合案例——触发器使用

以下是一个简单的触发器案例，可以帮助我们快速学习触发器的基础命令！！！

步骤1：创建persons表

CREATE TABLE persons (name VARCHAR(40), num int);
Query OK, 0 rows affected (0.04 sec)

步骤2：创建一个销售额表sales

CREATE TABLE sales (name VARCHAR(40), sum int);
Query OK, 0 rows affected (0.04 sec)

步骤3：创建一个触发器

CREATE TRIGGER num_sum AFTER INSERT ON  persons 
    FOR EACH ROW INSERT  INTO  sales VALUES (NEW.name,7*NEW.num);
Query OK, 0 rows affected (0.01 sec)

可以看到，这里创建的触发器是当在数据表persons中插入数据后，把列 num 的数值乘以7之后，最后插入到表sales中的列sum

步骤4：向persons表中插入记录

INSERT INTO persons VALUES ('xiaojie',20),('eason',49);
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

步骤5：查看表persons和 sales

  • 查询表persons数据
SELECT * FROM persons;
+---------+------+
| name    | num  |
+---------+------+
| xiaojie |   20 |
| eason   |   49 |
+---------+------+
2 rows in set (0.00 sec)
  • 查询表sales数据
SELECT *FROM sales;
+---------+------+
| name    | sum  |
+---------+------+
| xiaojie |  140 |
| eason   |  343 |
+---------+------+
2 rows in set (0.00 sec)

步骤6：查看触发器

SHOW TRIGGERS;
+---------+--------+---------+-------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger | Event  | Table   | Statement                                       | Timing | Created                | sql_mode                                   | Definer        | character_set_client | collation_connection | Database Collation |
+---------+--------+---------+-------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
| num_sum | INSERT | persons | INSERT  INTO  sales VALUES (NEW.name,7*NEW.num) | AFTER  | 2025-01-20 15:26:23.78 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | root@localhost | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+---------+--------+---------+-------------------------------------------------+--------+------------------------+--------------------------------------------+----------------+----------------------+----------------------+--------------------+
1 row in set (0.01 sec)

本文有关mysql数据库触发器的使用先讲到这里，明天再见！！
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！


http://www.kler.cn/a/514149.html

相关文章：

  • 深入探索 Nginx 的高级用法：解锁 Web 服务器的强大潜能
  • 系统思考—转型
  • Spring 中的事件驱动模型
  • STM32单片机：GPIO模式
  • 使用Chrome和Selenium实现对Superset等私域网站的截图
  • 从 Spark 到 StarRocks：实现58同城湖仓一体架构的高效转型
  • AG32 FPGA 的 Block RAM 资源：M9K 使用
  • Go语言如何实现限制用户 1 分钟内最多请求 1000 次？
  • PHP礼品兑换系统小程序
  • 【漫话机器学习系列】054.极值（Extrema）
  • 接口（1）
  • 苍穹外卖项目总结（二）
  • MyBatis Plus 的 InnerInterceptor：更轻量级的 SQL 拦截器
  • Spark/Kafka
  • Docker 和 Kubernetes
  • NextJs - ServerAction获取文件并处理Excel
  • K8s master节点初始化失败报错
  • UI样式表（悬停hover状态样式和按下pressed）
  • FPGA 时钟树缓存布局布线
  • Linux C\C++编程-文件位置指针与读写文件数据块
  • HarmonyOS NEXT：华为分享-碰一碰开发分享
  • Linux内核源码目录结构
  • Linux：文件描述符fd、系统调用open
  • 【Unity3D】3D物体摆放、场景优化案例Demo
  • AI 在制造行业的探究
  • 使用 OpenCV 和 Python 轻松实现人脸检测