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

ℹ️大家好，我是练小杰，这周是春节前的最后一周了，现在一双手数都能数得过来了！！
本播客将学习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_eventON 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 | BEGINIF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THENSET 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 | BEGINIF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THENSET 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)