SQL基础⑮ | 触发器

0 序言

本文将介绍MySQL触发器的核心知识，包括其定义、创建方法、查看与删除操作，以及优缺点。

通过学习，你将理解触发器如何由事件自动触发操作，掌握其在保证数据完整性、记录日志等场景的应用，明确使用时的注意事项。

1 触发器概述

在实际开发中，关联表的操作需保证原子性，若手动维护易出现数据缺失。

触发器可解决这一问题，实现操作的自动触发。

1.1 定义

触发器是嵌入MySQL服务器的一段程序，由INSERT、UPDATE、DELETE事件触发自动执行，用于在数据执行增、删、改操作时自动执行关联逻辑。

MySQL从5.0.2版本开始支持触发器。

1.2 作用

当对数据表执行插入、更新、删除操作时，自动执行预设的数据库逻辑，确保数据完整性、记录操作日志或进行数据合法性检查。

2 触发器的创建

2.1 创建语法

CREATE TRIGGER 触发器名称 
{BEFORE|AFTER} {INSERT|UPDATE|DELETE} ON 表名
FOR EACH ROW 
触发器执行的语句块;

• 触发器名称：自定义名称，标识触发器。
• BEFORE|AFTER：触发时间，BEFORE指事件前触发，AFTER指事件后触发。
• INSERT|UPDATE|DELETE：触发事件，分别对应插入、更新、删除操作。
• 表名：触发器监控的表。
• FOR EACH ROW：对每一行数据的操作都触发该触发器。
• 语句块：可包含单条语句或BEGIN...END包裹的复合语句。

2.2 代码举例

2.2.1 示例1：BEFORE INSERT触发器

1. 创建数据表：

CREATE TABLE test_trigger (
 id INT PRIMARY KEY AUTO_INCREMENT,
 t_note VARCHAR(30)
);
CREATE TABLE test_trigger_log (
 id INT PRIMARY KEY AUTO_INCREMENT,
 t_log VARCHAR(30)
);

2. 创建触发器：在test_trigger插入数据前，向test_trigger_log插入日志。

DELIMITER //
CREATE TRIGGER before_insert
BEFORE INSERT ON test_trigger 
FOR EACH ROW
BEGIN
  INSERT INTO test_trigger_log (t_log) VALUES('before_insert');
END //
DELIMITER ;

3. 触发操作：向test_trigger插入数据。

INSERT INTO test_trigger (t_note) VALUES ('测试 BEFORE INSERT 触发器');

4. 查看结果：test_trigger_log中新增一条before_insert日志。

SELECT * FROM test_trigger_log; 

2.2.2 示例2：AFTER INSERT触发器

1. 创建触发器：在test_trigger插入数据后，向test_trigger_log插入日志。

DELIMITER //
CREATE TRIGGER after_insert
AFTER INSERT ON test_trigger
FOR EACH ROW
BEGIN
  INSERT INTO test_trigger_log (t_log) VALUES('after_insert');
END //
DELIMITER ;

2. 触发操作：向test_trigger插入数据。

INSERT INTO test_trigger (t_note) VALUES ('测试 AFTER INSERT 触发器');

3. 查看结果：test_trigger_log中新增after_insert日志。

SELECT * FROM test_trigger_log; 

这个结果可以看到id=3, t_log='after_insert

2.2.3 示例3：数据合法性检查触发器

创建触发器salary_check_trigger，在插入employees表数据前，检查新员工薪资是否超过其领导薪资，若超过则报错。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
  DECLARE mgrsalary DOUBLE;
  -- NEW代表新插入的记录，获取其领导的薪资
  SELECT salary INTO mgrsalary FROM employees WHERE employee_id = NEW.manager_id;
  IF NEW.salary > mgrsalary THEN
    -- 抛出错误，阻止插入
    SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资高于领导薪资错误';
  END IF;
END //
DELIMITER ;

比如这里插入的薪资比老板高，然后就触发错误了。

3 查看与删除触发器

3.1 查看触发器

3.1.1 方式1：查看当前数据库所有触发器

SHOW TRIGGERS;

3.1.2 方式2：查看指定触发器

SHOW CREATE TRIGGER 触发器名称;

3.1.3 方式3：从系统表查询

SELECT * FROM information_schema.TRIGGERS; -- 可加WHERE条件过滤

3.2 删除触发器

语法：

DROP TRIGGER IF EXISTS 触发器名称; -- IF EXISTS避免触发器不存在时报错

4 触发器的优缺点

4.1 优点

确保数据完整性
例如，进货单头表与明细表需保持合计数量和金额一致。
通过触发器，可在明细表插入、更新、删除时，自动重新计算并更新头表的合计值，
避免数据不一致。

记录操作日志
可记录数据变更的时间和内容，便于追溯操作场景，定位问题原因。
如会员储值金额修改时，触发器可自动记录修改日志。

数据合法性检查
在数据插入或更新前，通过触发器检查数据合法性。
如超市进货时，检查录入的价格是否合理，防止错误数据进入系统。

4.2 缺点

4.2.1 可读性差

触发器存储在数据库中，由事件驱动，不受应用层直接控制，增加系统维护难度。
例如，触发器出错可能导致操作失败，但排查时易误认为是应用层或表结构问题。

4.2.2 易受数据变更影响

表结构变更可能导致触发器失效，且排查困难。
如修改表字段后，触发器中引用该字段的语句可能报错。

4.3 注意点

若子表定义外键约束并指定ON UPDATE/DELETE CASCADE/SET NULL，修改父表数据引发子表变更时，子表上基于UPDATE/DELETE的触发器不会被激活。
仅手动执行子表的UPDATE/DELETE语句时才触发。

5 小结

触发器是MySQL中由INSERT、UPDATE、DELETE事件触发的自动执行程序，可用于保证数据完整性、记录日志和数据校验。

其创建需指定触发时间、事件和监控表，通过SHOW TRIGGERS等方式查看，用DROP TRIGGER删除。

虽触发器有诸多优势，但存在可读性差、易受结构变更影响等问题，使用时需权衡场景，避免过度依赖，以保证系统可维护性。