MYSQL-触发器（Trigger）

运维小路

发布于 2026-01-26 11:53:33

1120

文章被收录于专栏：运维小路运维小路

作者介绍：简历上没有一个精通的运维工程师，下面的思维导图也是预计更新的内容和当前进度(不定时更新)。

数据库是一个系统（应用）最重要的资产之一，所以我们的数据库将从以下几个数据库来进行介绍。

MySQL（本章节）

PostgreSQL

MongoDB

Redis

Etcd

前面介绍了视图和存储过程，今天我们讲解另外一个数据库对象：触发器（Trigger）。

一.什么是触发器？

触发器是一种特殊的存储过程，它自动执行以响应特定的数据库事件。你可以把它理解为数据库的"事件监听器"或"自动响应机制"。

核心特征：

自动触发：不需要手动调用，由数据库事件自动触发。
事件驱动：响应特定的数据操作（INSERT、UPDATE、DELETE）
与事务绑定：在触发语句的同一事务中执行

二.触发器的类型

2.1 DML 触发器（数据操作语言）

响应表上的 INSERT、UPDATE、DELETE 操作。

语法分类：

AFTER 触发器（或 FOR 触发器）：在操作完成后执行
INSTEAD OF 触发器：代替原操作执行

2.2 DDL 触发器（数据定义语言）

响应 CREATE、ALTER、DROP 等结构变更操作。

2.3 LOGON 触发器

响应登录事件。

三.触发器案例

我们基于下面的表创建触发器。

mysql> desc users;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
| email | varchar(100) | YES  | UNI | NULL    |                |
| age   | int(11)      | YES  |     | 0       |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.1插入前数据验证触发器

-- 创建 BEFORE INSERT 触发器，在插入前验证数据
DELIMITER //

CREATE TRIGGER before_user_insert
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    -- 验证姓名不能为空（虽然表结构已经是NOT NULL，这里做额外处理）
    IF NEW.name IS NULL OR TRIM(NEW.name) = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户名不能为空';
    END IF;

    -- 验证邮箱格式
    IF NEW.email IS NOT NULL AND NEW.email NOT LIKE '%_@__%.__%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;

    -- 验证年龄范围（0-150岁）
    IF NEW.age < 0 OR NEW.age > 150 THEN
        SET NEW.age = 0; -- 自动修正为默认值
    END IF;

    -- 自动将姓名首字母大写
    SET NEW.name = CONCAT(UPPER(SUBSTRING(NEW.name, 1, 1)), LOWER(SUBSTRING(NEW.name, 2)));
END//

DELIMITER ;

-- 正常插入
INSERT INTO users (name, email, age) VALUES ('john doe', 'john@example.com', 25);
-- 结果：Name 会变成 "John doe", age 25

-- 触发年龄自动修正
INSERT INTO users (name, email, age) VALUES ('jane smith', 'jane@example.com', -5);
-- 结果：age 会被自动设置为 0

-- 触发错误（邮箱格式不正确）
INSERT INTO users (name, email, age) VALUES ('bob', 'invalid-email', 30);
-- 结果：报错 "邮箱格式不正确"

mysql> INSERT INTO users (name, email, age) VALUES ('bob', 'invalid-email', 30);
ERROR 1644 (45000): 邮箱格式不正确
mysql> select * from users;
+----+------------+------------------+------+
| id | name       | email            | age  |
+----+------------+------------------+------+
|  1 | John doe   | john@example.com |   25 |
|  2 | Jane smith | jane@example.com |    0 |
+----+------------+------------------+------+
2 rows in set (0.00 sec)

3.2 更新审计日志触发器

-- 创建审计日志表
CREATE TABLE user_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(20) NOT NULL,
    old_data JSON,
    new_data JSON,
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建 AFTER UPDATE 触发器记录变更
DELIMITER //

CREATE TRIGGER after_user_update
    AFTER UPDATE ON users
    FOR EACH ROW
BEGIN
    -- 检查是否有实际变更
    IF OLD.name != NEW.name OR OLD.email != NEW.email OR OLD.age != NEW.age THEN
        INSERT INTO user_audit_log (user_id, action, old_data, new_data, changed_by)
        VALUES (
            OLD.id,
            'UPDATE',
            JSON_OBJECT(
                'name', OLD.name,
                'email', OLD.email,
                'age', OLD.age
            ),
            JSON_OBJECT(
                'name', NEW.name,
                'email', NEW.email,
                'age', NEW.age
            ),
            CURRENT_USER()
        );
    END IF;
END//

DELIMITER ;

mysql> -- 查看审计日志
mysql> SELECT * FROM user_audit_log;
+----+---------+--------+--------------------------------------------------------------+----------------------------------------------------------------+----------------+---------------------+
| id | user_id | action | old_data                                                     | new_data                                                       | changed_by     | changed_at          |
+----+---------+--------+--------------------------------------------------------------+----------------------------------------------------------------+----------------+---------------------+
|  1 |       1 | UPDATE | {"age": 25, "name": "John doe", "email": "john@example.com"} | {"age": 26, "name": "John Smith", "email": "john@example.com"} | root@localhost | 2025-10-15 23:01:26 |
+----+---------+--------+--------------------------------------------------------------+----------------------------------------------------------------+----------------+---------------------+
1 row in set (0.00 sec)

3.3 防止邮箱重复的加强触发器

-- 创建 BEFORE INSERT/UPDATE 触发器加强唯一性检查
DELIMITER //

CREATE TRIGGER before_user_email_check
    BEFORE INSERT ON users
    FOR EACH ROW
BEGIN
    DECLARE email_count INT;

    -- 检查邮箱是否已存在（不区分大小写）
    IF NEW.email IS NOT NULL THEN
        SELECT COUNT(*) INTO email_count 
        FROM users 
        WHERE LOWER(email) = LOWER(NEW.email);

        IF email_count > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '邮箱地址已存在';
        END IF;
    END IF;
END//

CREATE TRIGGER before_user_email_update_check
    BEFORE UPDATE ON users
    FOR EACH ROW
BEGIN
    DECLARE email_count INT;

    -- 只有邮箱发生变化时才检查
    IF NEW.email IS NOT NULL AND OLD.email != NEW.email THEN
        SELECT COUNT(*) INTO email_count 
        FROM users 
        WHERE LOWER(email) = LOWER(NEW.email) AND id != OLD.id;

        IF email_count > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '邮箱地址已被其他用户使用';
        END IF;
    END IF;
END//

DELIMITER ;

四. 查看和管理触发器

-- 查看所有触发器
SHOW TRIGGERS;

-- 查看特定表的触发器
SHOW TRIGGERS LIKE 'users';

-- 查看触发器定义
SHOW CREATE TRIGGER before_user_insert;

-- 删除触发器
DROP TRIGGER IF EXISTS before_user_insert;

注意：我们前面介绍过的视图（View），存储过程（Stored Procedure），还有今天的触发器（Trigger）都是属于数据库的对象，是依附具体的某一个库的，所以我们备份的时候只要备份了全部数据，就会把他们同时备份。但是如果使用只备份表结构和数据，则默认不会备份视图，存储过程及触发器。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-10-15，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库