文章目录
- **MySQL 触发器开发说明**
- **1. 触发器的基本概念**
- **(1) 触发时机**
- **(2) 触发事件**
- **(3) 触发对象**
- **2. 触发器的语法**
- **关键点说明**
- **3. 触发器中的特殊变量**
- **4. 触发器的开发示例**
- **(1) 插入触发器**
- **(2) 更新触发器**
- **(3) 删除触发器**
- **5. 触发器的管理**
- **(1) 查看触发器**
- **(2) 删除触发器**
- **(3) 修改触发器**
- **6. 触发器的应用场景**
- **(1) 数据验证**
- **(2) 审计日志**
- **(3) 自动化任务**
- **7. 注意事项**
- **(1) 性能影响**
- **(2) 递归触发**
- **(3) 错误处理**
- **(4) 权限要求**
- **8. 示例：完整的触发器开发流程**
- **(1) 创建表结构**
- **(2) 创建触发器**
- **(3) 测试触发器**
MySQL 触发器开发说明
触发器（Trigger）是 MySQL 中一种特殊的存储程序，它会在指定的表上发生特定事件（如 INSERT、UPDATE 或 DELETE）时自动执行。触发器可以用于数据验证、审计日志记录、复杂业务逻辑处理等场景。
1. 触发器的基本概念
(1) 触发时机
触发器可以在以下两种时机执行：
- BEFORE：在触发事件（如插入、更新或删除）发生之前执行。
- AFTER：在触发事件完成之后执行。
(2) 触发事件
触发器可以响应以下三种事件：
- INSERT：当新记录插入到表中时触发。
- UPDATE：当表中的记录被更新时触发。
- DELETE：当表中的记录被删除时触发。
(3) 触发对象
触发器只能定义在表上，并且每个触发器只能针对一个表。
2. 触发器的语法
创建触发器的语法如下：
CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table_name
FOR EACH ROW
BEGIN
-- 触发器逻辑代码
END;
关键点说明
trigger_name：触发器的名称，必须唯一。{ BEFORE | AFTER }：指定触发时机。{ INSERT | UPDATE | DELETE }：指定触发事件。table_name：触发器关联的表。FOR EACH ROW：表示触发器会对每一行受影响的数据执行一次。BEGIN ... END：触发器的逻辑代码块。
3. 触发器中的特殊变量
在触发器中，可以通过以下两个特殊变量访问当前操作的行数据：
NEW：表示即将插入或更新的新行数据（仅适用于INSERT和UPDATE事件）。OLD：表示即将被更新或删除的旧行数据（仅适用于UPDATE和DELETE事件）。
示例：
- 在
INSERT触发器中，NEW.column_name表示新插入的列值。 - 在
UPDATE触发器中，OLD.column_name表示更新前的列值，NEW.column_name表示更新后的列值。 - 在
DELETE触发器中，OLD.column_name表示被删除的列值。
4. 触发器的开发示例
(1) 插入触发器
在插入新记录时，自动为某列生成默认值：
DELIMITER $$
CREATE TRIGGER before_insert_example
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NEW.salary IS NULL THEN
SET NEW.salary = 5000; -- 如果 salary 为空，则设置默认值为 5000
END IF;
END$$
DELIMITER ;
(2) 更新触发器
在更新记录时，记录变更日志：
DELIMITER $$
CREATE TRIGGER after_update_example
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_logs (employee_id, old_salary, new_salary, updated_at)
VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
END$$
DELIMITER ;
(3) 删除触发器
在删除记录时，将数据备份到另一个表中：
DELIMITER $$
CREATE TRIGGER before_delete_example
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_backup (id, name, salary, deleted_at)
VALUES (OLD.id, OLD.name, OLD.salary, NOW());
END$$
DELIMITER ;
5. 触发器的管理
(1) 查看触发器
查看某个数据库中的所有触发器：
SHOW TRIGGERS \G
(2) 删除触发器
删除指定的触发器：
DROP TRIGGER trigger_name;
(3) 修改触发器
MySQL 不支持直接修改触发器，需要先删除再重新创建：
DROP TRIGGER trigger_name;
-- 然后重新创建触发器
CREATE TRIGGER trigger_name ...
6. 触发器的应用场景
(1) 数据验证
在插入或更新数据时，确保数据符合某些规则。例如：
- 验证字段值是否在合理范围内。
- 检查外键约束。
(2) 审计日志
记录对表的操作历史，便于后续审计。例如：
- 记录谁在何时修改了哪些数据。
- 记录删除的数据以备恢复。
(3) 自动化任务
在某些操作发生时，自动执行相关任务。例如：
- 自动生成主键值。
- 同步更新其他表的数据。
7. 注意事项
(1) 性能影响
- 触发器会增加数据库操作的开销，尤其是在高并发场景下。
- 尽量避免在触发器中执行复杂的逻辑或耗时操作。
(2) 递归触发
- MySQL 默认不允许触发器递归调用（即触发器不能再次触发自身）。
- 如果需要递归触发，可以通过设置系统变量
log_slow_filter来控制。
(3) 错误处理
- 如果触发器中的代码抛出错误，整个事务可能会回滚。
- 可以通过
DECLARE CONTINUE HANDLER捕获异常并继续执行。
(4) 权限要求
- 创建触发器需要
TRIGGER权限。 - 触发器中使用的表需要相应的操作权限。
8. 示例：完整的触发器开发流程
假设有一个 orders 表和一个 order_logs 表，我们希望在每次更新订单状态时记录日志。
(1) 创建表结构
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
order_status VARCHAR(50),
amount DECIMAL(10, 2)
);
CREATE TABLE order_logs (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT,
old_status VARCHAR(50),
new_status VARCHAR(50),
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
(2) 创建触发器
DELIMITER $$
CREATE TRIGGER after_order_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
IF OLD.order_status != NEW.order_status THEN
INSERT INTO order_logs (order_id, old_status, new_status, updated_at)
VALUES (OLD.id, OLD.order_status, NEW.order_status, NOW());
END IF;
END$$
DELIMITER ;
(3) 测试触发器
-- 插入一条订单记录
INSERT INTO orders (order_status, amount) VALUES ('Pending', 100.00);
-- 更新订单状态
UPDATE orders SET order_status = 'Shipped' WHERE id = 1;
-- 查看日志表
SELECT * FROM order_logs;
通过以上步骤，你可以熟练掌握 MySQL 触发器的开发与使用。如果有任何问题或需要进一步优化，请随时提问！
