MySQL触发器

最新推荐文章于 2025-04-10 08:07:04 发布

一头代码

最新推荐文章于 2025-04-10 08:07:04 发布

阅读量626

点赞数 14

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/gggfJJ/article/details/145555599

版权

在 MySQL 中，触发器（Trigger） 是一种数据库对象，它可以在对表进行某些操作（如 INSERT、UPDATE 或 DELETE）时自动执行一段预定义的 SQL 代码。触发器可以在操作之前或之后触发，帮助自动化某些任务，如数据验证、日志记录或同步操作。

触发器的工作原理

触发器与特定的表、特定的操作（插入、更新或删除）以及触发的时机（BEFORE 或 AFTER）相关联。当满足触发器的条件时，它会自动执行。

触发器的基本语法

sql

复制编辑

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN -- 触发器逻辑 END;

trigger_name：触发器的名称。
trigger_time：指定触发器的执行时机，常见的有：
- BEFORE：在操作发生之前触发。
- AFTER：在操作发生之后触发。
trigger_event：指定触发器触发的事件类型，通常为：
- INSERT：插入数据时触发。
- UPDATE：更新数据时触发。
- DELETE：删除数据时触发。
table_name：触发器关联的表名。
FOR EACH ROW：表示触发器会对每一行被影响的数据执行。
BEGIN ... END：定义触发器的逻辑，触发器执行的 SQL 语句。

触发器的常见应用场景

自动更新时间戳：在每次更新某一行数据时，自动更新 updated_at 字段。
sql

复制编辑

CREATE TRIGGER update_timestamp BEFORE UPDATE ON my_table FOR EACH ROW BEGIN SET NEW.updated_at = NOW(); END;
数据验证：在插入或更新数据之前，对数据进行验证。
sql

复制编辑

CREATE TRIGGER check_price BEFORE INSERT ON products FOR EACH ROW BEGIN IF NEW.price <= 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Price must be greater than zero'; END IF; END;
自动计算并插入日志记录：在删除某行数据时，自动将该数据的详细信息记录到一个日志表中。
sql

复制编辑

CREATE TRIGGER log_delete AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO employee_log (action, employee_id, deleted_at) VALUES ('DELETE', OLD.employee_id, NOW()); END;

`NEW` 和 `OLD` 关键字

NEW：用于访问插入或更新时的新值。NEW 只能在 INSERT 或 UPDATE 触发器中使用。
- 例如，NEW.column_name 获取插入或更新的数据行的新值。
OLD：用于访问删除或更新时的旧值。OLD 只能在 DELETE 或 UPDATE 触发器中使用。
- 例如，OLD.column_name 获取删除或更新前的数据行的旧值。

触发器的操作顺序

BEFORE INSERT：数据插入前，触发器先执行。
AFTER INSERT：数据插入后，触发器才执行。
BEFORE UPDATE：数据更新前，触发器先执行。
AFTER UPDATE：数据更新后，触发器才执行。
BEFORE DELETE：数据删除前，触发器先执行。
AFTER DELETE：数据删除后，触发器才执行。

触发器的限制

每个表最多有 6 个触发器：每个事件类型（INSERT、UPDATE、DELETE）最多可以有一个 BEFORE 触发器和一个 AFTER 触发器。
不能在触发器内调用 DDL（数据定义语言）命令：如 CREATE, DROP, ALTER 等命令不能在触发器中使用。
性能问题：过多的触发器可能影响数据库性能，特别是在处理大量数据时。

示例：创建一个触发器

假设我们有一个员工表 employees，我们想在每次删除员工数据时，自动记录到日志表中：

sql

复制编辑

CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100), position VARCHAR(100) ); CREATE TABLE employee_log ( action VARCHAR(20), employee_id INT, deleted_at DATETIME ); CREATE TRIGGER log_employee_deletion AFTER DELETE ON employees FOR EACH ROW BEGIN INSERT INTO employee_log (action, employee_id, deleted_at) VALUES ('DELETE', OLD.employee_id, NOW()); END;