MySQL——触发器

一、什么是触发器？

触发器（Trigger） 是MySQL中一种特殊的存储过程，它会在指定的事件（INSERT、UPDATE、DELETE）发生时自动执行。与普通存储过程不同，触发器无需手动调用，而是由数据库自动触发。通过触发器，开发者可以实现数据的自动验证、级联操作和审计日志等复杂功能。

触发器的核心作用：

• 数据校验：在操作执行前验证数据合法性。

• 级联操作：自动更新或删除关联表中的数据。

• 审计追踪：记录数据变更历史。

• 业务规则封装：将复杂逻辑固化在数据库层。

二、触发器的类型与语法

1. 触发器的三种类型

2. 基本语法

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
    IF NEW.column < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '值不能为负';
    END IF;
END $$
DELIMITER ;

3. 核心关键字解析

• BEFORE/AFTER：决定触发时机，BEFORE用于数据验证，AFTER用于后续操作。

• NEW/OLD：虚拟表，NEW表示新数据（INSERT/UPDATE），OLD表示旧数据（UPDATE/DELETE）。

• FOR EACH ROW：行级触发，每行数据变化都会执行触发器。

三、实战案例：触发器的典型应用场景

案例1：数据验证（BEFORE INSERT）

场景：禁止插入年龄小于18岁的学生。

DELIMITER $$
CREATE TRIGGER check_age_before_insert
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
    IF TIMESTAMPDIFF(YEAR, NEW.birthdate, CURDATE()) < 18 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '学生年龄必须≥18岁';
    END IF;
END $$
DELIMITER ;

案例2：级联删除（AFTER DELETE）

场景：删除学生时自动删除其成绩记录。

DELIMITER $$
CREATE TRIGGER cascade_delete_scores
AFTER DELETE ON students
FOR EACH ROW
BEGIN
    DELETE FROM scores WHERE student_id = OLD.id;
END $$
DELIMITER ;

案例3：审计日志（AFTER UPDATE）

场景：记录成绩变更历史。

CREATE TABLE score_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    old_score DECIMAL(5,2),
    new_score DECIMAL(5,2),
    changed_at TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER log_score_change
AFTER UPDATE ON scores
FOR EACH ROW
BEGIN
    IF OLD.score != NEW.score THEN
        INSERT INTO score_audit 
        VALUES (NULL, NEW.student_id, OLD.score, NEW.score, NOW());
    END IF;
END $$
DELIMITER ;

四、触发器的进阶技巧与注意事项

1. 错误处理与自定义异常

使用SIGNAL抛出错误，阻止非法操作：

IF NEW.salary < 0 THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = '薪资不能为负';
END IF;

2. 动态修改数据（BEFORE触发器）

在BEFORE触发器中可修改即将写入的数据：

-- 自动填充创建时间
CREATE TRIGGER set_create_time
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

3. 触发器的限制与陷阱

• 性能影响：复杂触发器会降低数据库吞吐量。

• 递归触发：避免触发器A触发触发器B，再触发A的死循环。

• 事务一致性：在事务表中，触发器失败会导致整个事务回滚。

五、触发器管理：查看与删除

1. 查看所有触发器

SHOW TRIGGERS FROM database_name;

2. 查看触发器定义

SHOW CREATE TRIGGER trigger_name;

3. 删除触发器

DROP TRIGGER IF EXISTS trigger_name;

六、触发器的最佳实践

1. 精简逻辑：避免在触发器中编写复杂业务逻辑。

2. 幂等设计：确保触发器多次执行结果一致。

3. 权限隔离：仅授予必要的权限，防止意外修改。

4. 文档记录：为每个触发器添加注释，说明其用途。

七、总结：何时使用触发器？

• 数据完整性：强制执行业务规则（如年龄限制）。

• 自动化任务：级联删除、自动填充字段。

• 审计需求：追踪数据变更历史。

• 替代外键约束：实现跨表复杂校验。