MySQL 二轮学习笔记·进阶篇·(六) 触发器

触发器

触发器（Trigger）是预先定义在表上的 “自动执行规则”：当表发生特定操作（如INSERT插入、UPDATE更新、DELETE删除）时，数据库会自动触发并执行触发器中定义的 SQL 逻辑，无需手动调用。简单来说，触发器是数据库层面的 “监听器”—— 监听表的指定事件，事件发生则自动执行预设逻辑。

1.介绍

创建触发器，必须明确以下 4 个要素，缺一不可：

触发器的限制

• 仅能绑定到表（不能绑定到视图、临时表）；
• 触发逻辑中不能返回结果集（如SELECT * FROM 表，否则报错）；
• 避免 “循环触发”（如 A 表触发器修改 B 表，B 表触发器又修改 A 表，导致无限循环）。

适用场景

数据一致性维护：如 “用户表删除数据后，自动删除关联的订单表数据”，避免数据冗余或孤立。

数据日志记录：如 “每次更新商品库存后，自动记录库存变动日志”，便于追溯操作历史。

业务规则强制：如 “插入订单时，自动检查商品库存是否充足，不足则阻止插入”，提前拦截非法操作。

数据自动计算：如 “更新订单金额后，自动同步计算用户的累计消费金额”，减少手动维护成本。

2.创建触发器

(1)insert

1. 场景需求

现有两张表：

• orders（订单表）：存储订单核心信息，字段为order_id（订单 ID，主键）、user_id（用户 ID）、order_amount（订单金额）、create_time（创建时间）；
• order_logs（订单日志表）：记录订单插入操作，字段为log_id（日志 ID，主键自增）、order_id（关联订单 ID）、log_content（日志内容）、log_time（日志时间）。

需求：当向orders表插入一条新订单时，自动向order_logs表插入一条对应的操作日志。

2. 创建INSERT触发器（AFTER INSERT时机）

-- 1. 先创建订单日志表（若未创建）
CREATE TABLE IF NOT EXISTS order_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    log_content VARCHAR(100),
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP  -- 默认取当前时间
);

-- 2. 创建INSERT类型触发器（绑定orders表，插入后触发）
DELIMITER //  -- 临时修改结束符（避免与触发器内“;”冲突）
CREATE TRIGGER trig_orders_after_insert
AFTER INSERT  -- 触发时机：插入订单后执行
ON orders     -- 触发对象：orders表
FOR EACH ROW  -- 触发粒度：每插入1行数据，就触发1次（必写）
BEGIN
    -- 触发逻辑：向order_logs插入日志，使用“NEW”关键字获取插入的新数据
    INSERT INTO order_logs (order_id, log_content)
    VALUES (
        NEW.order_id,  -- NEW.字段名：获取INSERT操作中“新插入行”的指定字段值
        CONCAT('新增订单，订单ID：', NEW.order_id, '，金额：', NEW.order_amount)
    );
END //
DELIMITER ;  -- 恢复结束符

3. 测试触发器

向orders表插入一条订单数据，观察order_logs是否自动生成日志：

-- 插入订单
INSERT INTO orders (order_id, user_id, order_amount, create_time)
VALUES (1001, 1, 299.9, '2024-05-01 10:00:00');

-- 查询日志表（会发现自动新增1条日志，内容为“新增订单，订单ID：1001，金额：299.9”）
SELECT * FROM order_logs;

关键知识点：NEW关键字

在INSERT触发器中，NEW代表 “新插入的行”，通过NEW.字段名可获取新行中任意字段的值（如NEW.order_id获取新订单的 ID），是INSERT触发器中获取数据的核心方式。

(2)update

1. 场景需求

现有两张表：

• products（商品表）：存储商品信息，字段为prod_id（商品 ID，主键）、prod_name（商品名）、stock（库存数量）；
• stock_logs（库存日志表）：记录库存变动，字段为log_id（日志 ID，自增）、prod_id（商品 ID）、old_stock（更新前库存）、new_stock（更新后库存）、log_time（日志时间）。

需求：当更新products表的商品库存时，自动向stock_logs表记录 “更新前后的库存对比”。

2. 创建UPDATE触发器（AFTER UPDATE时机）

-- 1. 先创建库存日志表（若未创建）
CREATE TABLE IF NOT EXISTS stock_logs (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    prod_id INT,
    old_stock INT,
    new_stock INT,
    log_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 2. 创建UPDATE类型触发器（绑定products表，更新后触发）
DELIMITER //
CREATE TRIGGER trig_products_after_update
AFTER UPDATE  -- 触发时机：更新商品数据后执行
ON products   -- 触发对象：products表
FOR EACH ROW  -- 每更新1行，触发1次
BEGIN
    -- 仅当“库存字段（stock）被修改”时，才记录日志（避免其他字段更新时冗余日志）
    IF OLD.stock != NEW.stock THEN
        INSERT INTO stock_logs (prod_id, old_stock, new_stock)
        VALUES (
            NEW.prod_id,    -- 商品ID（更新前后不变，用OLD或NEW均可）
            OLD.stock,      -- OLD.字段名：获取“更新前”的字段值（旧库存）
            NEW.stock       -- NEW.字段名：获取“更新后”的字段值（新库存）
        );
    END IF;
END //
DELIMITER ;

3. 测试触发器

更新商品库存，观察日志表是否记录变动：

-- 1. 先插入一条商品数据
INSERT INTO products (prod_id, prod_name, stock) VALUES (2001, '无线耳机', 100);

-- 2. 更新该商品的库存（从100改为80）
UPDATE products SET stock = 80 WHERE prod_id = 2001;

-- 3. 查询日志表（会发现1条日志：old_stock=100，new_stock=80）
SELECT * FROM stock_logs;

-- 4. 测试“更新非库存字段”（如商品名），日志表不会新增记录（符合IF条件过滤）
UPDATE products SET prod_name = '无线耳机（降噪版）' WHERE prod_id = 2001;
SELECT * FROM stock_logs;  -- 日志数量不变

关键知识点：OLD与NEW关键字

在UPDATE触发器中：

• OLD：代表 “更新前的行”，仅能读取（不能修改），用于获取更新前的字段值（如OLD.stock）；
• NEW：代表 “更新后的行”，可读取也可修改（若触发器为BEFORE UPDATE，可修改NEW的值来干预最终更新结果）。

(3)delete

1. 场景需求

现有两张关联表（通过user_id关联）：

• users（用户表）：user_id（主键）、user_name（用户名）；
• orders（订单表）：order_id（主键）、user_id（外键，关联用户表）、order_amount（金额）。

需求：当从users表删除某个用户时，自动删除该用户在orders表中的所有关联订单（避免 “用户已删，订单残留” 的孤立数据）。

2. 创建DELETE触发器（BEFORE DELETE时机）

-- 创建DELETE类型触发器（绑定users表，删除前触发）
DELIMITER //
CREATE TRIGGER trig_users_before_delete
BEFORE DELETE  -- 触发时机：删除用户前执行（先删订单，再删用户，避免外键约束报错）
ON users       -- 触发对象：users表
FOR EACH ROW   -- 每删除1个用户，触发1次
BEGIN
    -- 删除该用户的所有关联订单（用OLD.user_id获取“待删除用户”的ID）
    DELETE FROM orders WHERE user_id = OLD.user_id;
END //
DELIMITER ;

3. 测试触发器

删除用户，观察关联订单是否自动删除：

-- 1. 先准备测试数据（1个用户+2个关联订单）
INSERT INTO users (user_id, user_name) VALUES (1, '张三');
INSERT INTO orders (order_id, user_id, order_amount) VALUES (1001, 1, 299.9), (1002, 1, 599);

-- 2. 删除用户“张三”（user_id=1）
DELETE FROM users WHERE user_id = 1;

-- 3. 验证结果
SELECT * FROM users WHERE user_id = 1;  -- 结果为空（用户已删除）
SELECT * FROM orders WHERE user_id = 1;  -- 结果为空（关联订单已被触发器自动删除）

关键知识点：OLD关键字与触发时机选择

• OLD关键字：在DELETE触发器中，NEW关键字不可用（删除后无新行），仅能通过OLD.字段名获取 “待删除行” 的字段值（如OLD.user_id获取待删用户的 ID）。
• 触发时机选择：此处用BEFORE DELETE（删除前触发），原因是：若先删用户（AFTER DELETE），订单表的外键约束会检测到 “关联的用户已不存在”，直接报错；而BEFORE DELETE会先删订单（解除外键关联），再删用户，避免约束冲突。

3.查看与删除触发器

查看触发器

show triggers;

show create trigger 触发器名;

删除触发器

drop trigger if exists 触发器名;

触发器的核心价值在于 “自动化”—— 将重复的、需与表操作绑定的逻辑交给数据库自动执行，减少应用程序的代码量与维护成本，但需注意避免过度使用（如复杂逻辑建议用存储过程，触发器仅做简单响应），防止排查问题时难以定位逻辑来源。