MySql数据库触发器如何使用

MySQL触发器是依附于表存在的数据库对象，当表执行INSERT、UPDATE、DELETE操作时，触发器会自动触发执行预设的SQL逻辑，无需外部程序调用，能够有效减少业务代码的重复逻辑，保证数据操作的规范性。

触发器的核心概念

触发器由几个关键要素组成，理解这些要素是使用触发器的基础：

• 触发事件：触发触发器执行的操作类型，分为INSERT、UPDATE、DELETE三种。
• 触发时机：分为BEFORE和AFTER两种，BEFORE表示在触发事件执行之前触发，AFTER表示在触发事件执行之后触发。
• 触发对象：触发器依附的表，一张表可以对同一种触发事件设置不同触发时机的多个触发器。

触发器的创建语法

创建MySQL触发器需要使用CREATE TRIGGER语句，基础语法如下：

-- 创建触发器的基本语法
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- 触发器执行的逻辑SQL
END;

其中trigger_time的取值为BEFORE或者AFTER，trigger_event的取值为INSERT、UPDATE、DELETE。FOR EACH ROW表示触发器是行级触发器，每影响一行数据就会执行一次触发器逻辑。

需要注意，如果触发器逻辑中包含多条SQL语句，需要使用BEGIN和END包裹，同时需要临时修改语句结束符，避免和触发器内部的分号冲突，示例如下：

-- 修改语句结束符为$$
DELIMITER $$
CREATE TRIGGER trigger_name
BEFORE INSERT ON user_table FOR EACH ROW
BEGIN
    -- 多条SQL逻辑
    SET NEW.create_time = NOW();
    SET NEW.status = 1;
END$$
-- 恢复语句结束符为分号
DELIMITER ;

触发器中的NEW和OLD关键字

在触发器的逻辑中，可以通过NEW和OLD关键字获取触发事件影响的数据：

• INSERT事件中，只能用NEW关键字，获取即将插入的新数据，NEW.列名可以获取对应列的值。
• DELETE事件中，只能用OLD关键字，获取即将删除的旧数据，OLD.列名可以获取对应列的值。
• UPDATE事件中，OLD关键字可以获取更新前的旧数据，NEW关键字可以获取更新后的新数据。

常见使用场景示例

场景1：插入数据前自动补全字段

比如用户表插入数据时，自动设置创建时间和默认状态，不需要在插入语句中手动指定这两个字段：

-- 创建用户表
CREATE TABLE user_table (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    status TINYINT DEFAULT 0,
    create_time DATETIME
);

-- 创建BEFORE INSERT触发器
DELIMITER $$
CREATE TRIGGER before_user_insert
BEFORE INSERT ON user_table FOR EACH ROW
BEGIN
    -- 自动设置创建时间为当前时间
    SET NEW.create_time = NOW();
    -- 自动设置默认状态为1
    SET NEW.status = 1;
END$$
DELIMITER ;

-- 插入测试数据，不需要指定create_time和status
INSERT INTO user_table (username) VALUES ('test_user');

-- 查询数据，可以看到字段已经被自动填充
SELECT * FROM user_table;

场景2：更新数据后记录变更日志

当更新用户表的username字段时，自动把变更记录插入到日志表中：

-- 创建日志表
CREATE TABLE user_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    old_username VARCHAR(50),
    new_username VARCHAR(50),
    update_time DATETIME
);

-- 创建AFTER UPDATE触发器
DELIMITER $$
CREATE TRIGGER after_user_update
AFTER UPDATE ON user_table FOR EACH ROW
BEGIN
    -- 只有username字段发生变更时才记录日志
    IF OLD.username != NEW.username THEN
        INSERT INTO user_log (user_id, old_username, new_username, update_time)
        VALUES (OLD.id, OLD.username, NEW.username, NOW());
    END IF;
END$$
DELIMITER ;

-- 更新用户名称
UPDATE user_table SET username = 'new_test_user' WHERE id = 1;

-- 查询日志表，可以看到变更记录
SELECT * FROM user_log;

场景3：删除数据前校验业务规则

比如删除用户前，校验用户是否有未完成的订单，如果有则不允许删除：

-- 创建订单表
CREATE TABLE order_table (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    order_status TINYINT DEFAULT 0
);

-- 插入测试订单数据
INSERT INTO order_table (user_id, order_status) VALUES (1, 0);

-- 创建BEFORE DELETE触发器
DELIMITER $$
CREATE TRIGGER before_user_delete
BEFORE DELETE ON user_table FOR EACH ROW
BEGIN
    -- 查询用户是否有未完成的订单
    DECLARE order_count INT;
    SELECT COUNT(*) INTO order_count FROM order_table WHERE user_id = OLD.id AND order_status = 0;
    -- 如果有未完成的订单，抛出错误中断删除操作
    IF order_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '用户存在未完成的订单，无法删除';
    END IF;
END$$
DELIMITER ;

-- 尝试删除用户，会触发错误
DELETE FROM user_table WHERE id = 1;

触发器的查看与删除

查看触发器

可以通过SHOW TRIGGERS语句查看当前数据库中的所有触发器，也可以查询information_schema.TRIGGERS系统表获取指定触发器的详细信息：

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

-- 查看指定表的触发器
SHOW TRIGGERS LIKE 'user_table';

-- 查询系统表获取触发器详情
SELECT TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING, ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE EVENT_OBJECT_TABLE = 'user_table' AND TRIGGER_SCHEMA = '当前数据库名';

删除触发器

删除触发器使用DROP TRIGGER语句，如果触发器不存在可以加上IF EXISTS避免报错：

-- 删除指定触发器
DROP TRIGGER before_user_insert;

-- 安全删除，触发器不存在也不会报错
DROP TRIGGER IF EXISTS before_user_insert;

使用触发器的注意事项

• 触发器执行会占用额外的数据库资源，过多或者逻辑复杂的触发器会影响数据操作的性能，不建议在高频操作的表上设置复杂触发器。
• 触发器中不能返回结果集，也不能使用动态SQL语句。
• 同一个表同一个触发事件同一个触发时机只能创建一个触发器，如果需要多个逻辑可以合并到一个触发器中。
• 触发器的逻辑错误可能会导致触发它的数据操作失败，比如BEFORE触发器抛出错误，对应的INSERT、UPDATE、DELETE操作会被回滚。
• 不同数据库的触发器语法存在差异，本文的语法仅适用于MySQL数据库。

MySQL触发器数据库BEFORE_triggerAFTER_trigger修改时间：2026-06-14 15:48:18