MySQL 触发器

MySQL 触发器

触发器是一种特殊的存储过程，无需手动调用，当数据库表发生特定操作（INSERT/UPDATE/DELETE）时，会自动触发执行，主要用于实现“数据的自动校验、同步、日志记录”等场景（如插入订单后自动减少库存、修改数据后自动记录操作日志）。

触发器的核心要素

要素	说明
触发时机	BEFORE（操作执行前触发，如插入前校验数据）、AFTER（操作执行后触发，如插入后记录日志）。
触发事件	INSERT（插入数据时）、UPDATE（修改数据时）、DELETE（删除数据时）。
触发对象	与具体表绑定，仅当该表发生触发事件时生效。
触发逻辑	触发器执行的 SQL 语句（可包含条件判断、多语句，但不支持参数传递和返回值）。

触发器的语法格式

-- 创建触发器
DELIMITER //
CREATE TRIGGER 触发器名
触发时机(BEFORE/AFTER) 触发事件(INSERT/UPDATE/DELETE)
ON 表名 FOR EACH ROW  -- FOR EACH ROW：行级触发器（每操作一行触发一次）
BEGIN
    -- 触发器逻辑（可使用 NEW/OLD 关键字引用数据）
    -- NEW：INSERT/UPDATE 时，代表“新数据”（如 NEW.id 是新插入的ID）；
    -- OLD：UPDATE/DELETE 时，代表“旧数据”（如 OLD.balance 是修改前的余额）；
    
    -- 示例：插入订单后，自动减少商品库存（AFTER INSERT）
    UPDATE product SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;  -- NEW.quantity 是订单中的购买数量
END //
DELIMITER ;

-- 查看触发器（所有触发器存储在 information_schema.TRIGGERS 表中）
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = '触发器名';

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

3. 常见触发器场景示例

场景1：插入订单后自动减少库存（AFTER INSERT）

-- 1. 准备表：product（商品表）、order（订单表）
CREATE TABLE product (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    stock INT NOT NULL DEFAULT 0  -- 库存
);
CREATE TABLE `order` (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,  -- 关联商品ID
    quantity INT NOT NULL,    -- 购买数量
    FOREIGN KEY (product_id) REFERENCES product(id)
);

-- 2. 创建触发器：插入订单后减少对应商品库存
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON `order` FOR EACH ROW
BEGIN
    UPDATE product 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
END //
DELIMITER ;

-- 3. 测试：插入订单（商品1初始库存100）
INSERT INTO product (name, stock) VALUES ('手机', 100);
INSERT INTO `order` (product_id, quantity) VALUES (1, 5);  -- 购买5台手机

-- 查看结果：商品1库存变为95
SELECT * FROM product WHERE id = 1;

场景2：修改余额前校验是否为负（BEFORE UPDATE）

-- 创建触发器：修改账户余额前，若余额为负则抛出错误
DELIMITER //
CREATE TRIGGER before_account_update
BEFORE UPDATE ON account FOR EACH ROW
BEGIN
    IF NEW.balance < 0 THEN
        -- 抛出错误（通过 SIGNAL 自定义错误信息，MySQL 5.5+ 支持）
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '错误：余额不能为负数';
    END IF;
END //
DELIMITER ;

-- 测试：尝试将余额改为-100（触发错误）
UPDATE account SET balance = -100 WHERE id = 1;
-- 执行结果：报错“错误：余额不能为负数”，修改操作被阻止