Tech Docs Station

Appearance

触发器 ​

触发器（Trigger）是MySQL中一种特殊的存储过程，它会在特定的数据库事件（如INSERT、UPDATE、DELETE）发生时自动执行。

什么是触发器 ​

触发器是与表相关的数据库对象，当表上发生特定事件时，触发器会自动被激活并执行。触发器不需要手动调用，而是由数据库系统自动触发。

触发器的特点 ​

sql
-- 触发器的特点：
-- 1. 自动执行：不需要手动调用，事件发生时自动触发
-- 2. 与表绑定：触发器必须绑定到特定的表
-- 3. 时机明确：可以在事件前(BEFORE)或后(AFTER)执行
-- 4. 事件特定：针对INSERT、UPDATE、DELETE三种事件
-- 5. 无法直接调用：不能像存储过程那样使用CALL调用

触发器的应用场景 ​

sql
-- 常见应用场景：
-- 1. 数据验证：在插入或更新前验证数据合法性
-- 2. 数据同步：自动同步相关表的数据
-- 3. 审计日志：记录数据的变更历史
-- 4. 业务规则：实现复杂的业务约束
-- 5. 数据派生：自动计算和更新派生数据

创建触发器 ​

基本语法 ​

sql
-- 创建触发器的基本语法
CREATE TRIGGER 触发器名称
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON 表名
FOR EACH ROW
BEGIN
    -- 触发器执行的SQL语句
END;

-- 参数说明：
-- BEFORE/AFTER：触发时机
-- INSERT/UPDATE/DELETE：触发事件
-- FOR EACH ROW：行级触发器，每影响一行触发一次
-- NEW：新数据（INSERT/UPDATE时可用）
-- OLD：旧数据（UPDATE/DELETE时可用）

INSERT触发器 ​

sql
-- 准备示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    department VARCHAR(50),
    created_at DATETIME,
    updated_at DATETIME
);

-- 创建INSERT触发器：自动设置创建时间
DELIMITER //

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees  -- 在插入之前触发
FOR EACH ROW
BEGIN
    -- 设置创建时间
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
    
    -- 验证薪资不能为负数
    IF NEW.salary < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪资不能为负数';
    END IF;
END //

DELIMITER ;

-- 测试触发器
INSERT INTO employees (name, salary, department) 
VALUES ('张三', 8000, '技术部');

-- 查看结果，created_at和updated_at已自动设置
SELECT * FROM employees WHERE name = '张三';

UPDATE触发器 ​

sql
-- 创建UPDATE触发器：自动更新修改时间，并记录薪资变更
DELIMITER //

CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 更新修改时间
    SET NEW.updated_at = NOW();
    
    -- 如果薪资变更，验证涨幅不超过50%
    IF NEW.salary > OLD.salary * 1.5 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪资涨幅不能超过50%';
    END IF;
    
    -- 薪资不能降低超过30%
    IF NEW.salary < OLD.salary * 0.7 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪资降幅不能超过30%';
    END IF;
END //

DELIMITER ;

-- 测试触发器
-- 正常更新
UPDATE employees SET salary = 10000 WHERE name = '张三';

-- 尝试超过50%涨幅（会报错）
UPDATE employees SET salary = 20000 WHERE name = '张三';

DELETE触发器 ​

sql
-- 创建员工备份表
CREATE TABLE employees_backup (
    id INT,
    name VARCHAR(50),
    salary DECIMAL(10, 2),
    department VARCHAR(50),
    deleted_at DATETIME,
    deleted_by VARCHAR(50)
);

-- 创建DELETE触发器：删除前备份员工数据
DELIMITER //

CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    -- 将删除的数据备份到备份表
    INSERT INTO employees_backup (id, name, salary, department, deleted_at)
    VALUES (OLD.id, OLD.name, OLD.salary, OLD.department, NOW());
END //

DELIMITER ;

-- 测试触发器
DELETE FROM employees WHERE name = '张三';

-- 查看备份表，数据已被备份
SELECT * FROM employees_backup;

NEW和OLD关键字 ​

在触发器中，可以使用NEW和OLD关键字访问变更前后的数据：

sql
-- NEW和OLD的使用规则：
-- +--------+--------+--------+
-- | 事件   | NEW    | OLD    |
-- +--------+--------+--------+
-- | INSERT | 可用   | 不可用 |
-- | UPDATE | 可用   | 可用   |
-- | DELETE | 不可用 | 可用   |
-- +--------+--------+--------+

-- INSERT触发器中使用NEW
DELIMITER //
CREATE TRIGGER demo_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- NEW代表新插入的行数据
    INSERT INTO audit_log (action, record_id, details)
    VALUES ('INSERT', NEW.id, CONCAT('新增员工：', NEW.name));
END //
DELIMITER ;

-- UPDATE触发器中使用NEW和OLD
DELIMITER //
CREATE TRIGGER demo_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- NEW代表更新后的数据，OLD代表更新前的数据
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_log (emp_id, old_salary, new_salary, change_date)
        VALUES (NEW.id, OLD.salary, NEW.salary, NOW());
    END IF;
END //
DELIMITER ;

-- DELETE触发器中使用OLD
DELIMITER //
CREATE TRIGGER demo_delete_trigger
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    -- OLD代表被删除的行数据
    INSERT INTO audit_log (action, record_id, details)
    VALUES ('DELETE', OLD.id, CONCAT('删除员工：', OLD.name));
END //
DELIMITER ;

触发时机：BEFORE vs AFTER ​

sql
-- BEFORE触发器：
-- 1. 在事件发生之前执行
-- 2. 可以修改NEW值（影响即将插入/更新的数据）
-- 3. 可以阻止操作（通过SIGNAL抛出错误）
-- 4. 适合用于数据验证和数据预处理

-- AFTER触发器：
-- 1. 在事件发生之后执行
-- 2. 不能修改NEW值（数据已经写入）
-- 3. 适合用于审计日志、数据同步等

-- BEFORE触发器示例：数据验证和预处理
DELIMITER //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- 自动生成订单号
    IF NEW.order_no IS NULL THEN
        SET NEW.order_no = CONCAT('ORD', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(FLOOR(RAND() * 10000), 4, '0'));
    END IF;
    
    -- 设置默认状态
    IF NEW.status IS NULL THEN
        SET NEW.status = 'pending';
    END IF;
    
    -- 验证订单金额
    IF NEW.total_amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '订单金额必须大于0';
    END IF;
END //
DELIMITER ;

-- AFTER触发器示例：审计日志
DELIMITER //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 记录订单创建日志
    INSERT INTO order_log (order_id, action, log_time, details)
    VALUES (NEW.id, 'CREATE', NOW(), CONCAT('创建订单：', NEW.order_no));
    
    -- 更新统计表
    INSERT INTO daily_stats (stat_date, order_count, total_amount)
    VALUES (DATE(NOW()), 1, NEW.total_amount)
    ON DUPLICATE KEY UPDATE
        order_count = order_count + 1,
        total_amount = total_amount + NEW.total_amount;
END //
DELIMITER ;

实战案例 ​

案例1：库存管理 ​

sql
-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 10
);

-- 订单明细表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

-- 库存预警表
CREATE TABLE stock_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    alert_time DATETIME,
    message VARCHAR(200)
);

-- 插入订单明细后自动扣减库存
DELIMITER //
CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    DECLARE current_stock INT;
    DECLARE min_stock_val INT;
    DECLARE product_name VARCHAR(100);
    
    -- 扣减库存
    UPDATE products 
    SET stock = stock - NEW.quantity
    WHERE id = NEW.product_id;
    
    -- 检查库存是否不足
    SELECT stock, min_stock, name 
    INTO current_stock, min_stock_val, product_name
    FROM products 
    WHERE id = NEW.product_id;
    
    -- 如果库存低于最小值，插入预警
    IF current_stock < min_stock_val THEN
        INSERT INTO stock_alerts (product_id, alert_time, message)
        VALUES (NEW.product_id, NOW(), 
            CONCAT('商品【', product_name, '】库存不足，当前库存：', current_stock));
    END IF;
END //
DELIMITER ;

-- 取消订单后恢复库存
DELIMITER //
CREATE TRIGGER after_order_item_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    -- 恢复库存
    UPDATE products 
    SET stock = stock + OLD.quantity
    WHERE id = OLD.product_id;
END //
DELIMITER ;

案例2：数据审计日志 ​

sql
-- 审计日志表
CREATE TABLE audit_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    table_name VARCHAR(50),
    action VARCHAR(10),
    record_id INT,
    old_values JSON,
    new_values JSON,
    changed_by VARCHAR(50),
    changed_at DATETIME
);

-- 创建通用的审计触发器
DELIMITER //
CREATE TRIGGER after_employee_insert_audit
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, new_values, changed_at)
    VALUES (
        'employees',
        'INSERT',
        NEW.id,
        JSON_OBJECT(
            'name', NEW.name,
            'salary', NEW.salary,
            'department', NEW.department
        ),
        NOW()
    );
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_employee_update_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_values, new_values, changed_at)
    VALUES (
        'employees',
        'UPDATE',
        NEW.id,
        JSON_OBJECT(
            'name', OLD.name,
            'salary', OLD.salary,
            'department', OLD.department
        ),
        JSON_OBJECT(
            'name', NEW.name,
            'salary', NEW.salary,
            'department', NEW.department
        ),
        NOW()
    );
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER after_employee_delete_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (table_name, action, record_id, old_values, changed_at)
    VALUES (
        'employees',
        'DELETE',
        OLD.id,
        JSON_OBJECT(
            'name', OLD.name,
            'salary', OLD.salary,
            'department', OLD.department
        ),
        NOW()
    );
END //
DELIMITER ;

案例3：级联数据同步 ​

sql
-- 部门表
CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    employee_count INT DEFAULT 0
);

-- 员工表（添加部门外键）
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT
);

-- 新增员工时更新部门人数
DELIMITER //
CREATE TRIGGER after_employee_insert_dept
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.department_id IS NOT NULL THEN
        UPDATE departments 
        SET employee_count = employee_count + 1
        WHERE id = NEW.department_id;
    END IF;
END //
DELIMITER ;

-- 员工调部门时更新人数
DELIMITER //
CREATE TRIGGER after_employee_update_dept
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 如果部门变更
    IF OLD.department_id <> NEW.department_id OR 
       (OLD.department_id IS NULL AND NEW.department_id IS NOT NULL) OR
       (OLD.department_id IS NOT NULL AND NEW.department_id IS NULL) THEN
        
        -- 原部门人数减1
        IF OLD.department_id IS NOT NULL THEN
            UPDATE departments 
            SET employee_count = employee_count - 1
            WHERE id = OLD.department_id;
        END IF;
        
        -- 新部门人数加1
        IF NEW.department_id IS NOT NULL THEN
            UPDATE departments 
            SET employee_count = employee_count + 1
            WHERE id = NEW.department_id;
        END IF;
    END IF;
END //
DELIMITER ;

-- 删除员工时更新部门人数
DELIMITER //
CREATE TRIGGER after_employee_delete_dept
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.department_id IS NOT NULL THEN
        UPDATE departments 
        SET employee_count = employee_count - 1
        WHERE id = OLD.department_id;
    END IF;
END //
DELIMITER ;

案例4：数据一致性校验 ​

sql
-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50),
    customer_id INT,
    total_amount DECIMAL(10, 2) DEFAULT 0,
    status ENUM('pending', 'paid', 'shipped', 'completed', 'cancelled'),
    created_at DATETIME
);

-- 订单明细表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10, 2),
    subtotal DECIMAL(10, 2)
);

-- 插入明细后自动更新订单总金额
DELIMITER //
CREATE TRIGGER after_order_item_insert_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = total_amount + NEW.subtotal
    WHERE id = NEW.order_id;
END //
DELIMITER ;

-- 更新明细后重新计算订单总金额
DELIMITER //
CREATE TRIGGER after_order_item_update_total
AFTER UPDATE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = total_amount - OLD.subtotal + NEW.subtotal
    WHERE id = NEW.order_id;
END //
DELIMITER ;

-- 删除明细后更新订单总金额
DELIMITER //
CREATE TRIGGER after_order_item_delete_total
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = total_amount - OLD.subtotal
    WHERE id = OLD.order_id;
END //
DELIMITER ;

查看和管理触发器 ​

查看触发器 ​

sql
-- 查看数据库中的所有触发器
SHOW TRIGGERS;

-- 查看特定表的触发器
SHOW TRIGGERS WHERE `Table` = 'employees';

-- 查看触发器的创建语句
SHOW CREATE TRIGGER before_employee_insert;

-- 从information_schema查询触发器详细信息
SELECT 
    TRIGGER_NAME AS '触发器名称',
    EVENT_MANIPULATION AS '触发事件',
    EVENT_OBJECT_TABLE AS '关联表',
    ACTION_TIMING AS '触发时机',
    ACTION_STATEMENT AS '触发器语句'
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = DATABASE();

删除触发器 ​

sql
-- 删除触发器
DROP TRIGGER IF EXISTS before_employee_insert;

-- 删除前检查触发器是否存在
DROP TRIGGER IF EXISTS trigger_name;

修改触发器 ​

sql
-- MySQL不支持直接修改触发器
-- 需要先删除再重新创建

-- 删除旧触发器
DROP TRIGGER IF EXISTS before_employee_insert;

-- 重新创建触发器
DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END //
DELIMITER ;

触发器的限制和注意事项 ​

限制条件 ​

sql
-- MySQL触发器的限制：

-- 1. 不能在触发器中调用返回结果集的存储过程
-- 错误示例：
DELIMITER //
CREATE TRIGGER bad_trigger_example
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- 这会导致错误
    CALL GetAllEmployees();  
END //
DELIMITER ;

-- 2. 不能在触发器中使用动态SQL
-- 错误示例：
DELIMITER //
CREATE TRIGGER bad_trigger_example2
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- 这会导致错误
    SET @sql = 'SELECT * FROM employees';
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
END //
DELIMITER ;

-- 3. 不能在触发器中修改触发该触发器的表
-- 这会导致无限递归
DELIMITER //
CREATE TRIGGER bad_trigger_example3
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- 这会导致错误（无限递归）
    INSERT INTO employees (name) VALUES ('test');
END //
DELIMITER ;

-- 4. 同一表同一事件只能有一个触发器
-- MySQL 5.7.2+ 支持同一事件多个触发器，使用FOLLOWS/PRECEDES指定顺序

性能考虑 ​

sql
-- 触发器的性能注意事项：

-- 1. 触发器会增加操作开销
-- 每次INSERT/UPDATE/DELETE都会执行触发器

-- 2. 避免在触发器中执行耗时操作
-- 不推荐：
DELIMITER //
CREATE TRIGGER slow_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 复杂查询会影响性能
    SELECT COUNT(*) INTO @count FROM orders WHERE customer_id = NEW.customer_id;
    -- 更多复杂操作...
END //
DELIMITER ;

-- 3. 批量操作时触发器会逐行触发
-- 对于批量插入，触发器会对每一行都执行一次
-- 这可能严重影响性能

-- 4. 使用触发器时要考虑锁的影响
-- 触发器中的操作会持有锁，可能影响并发性能

调试触发器 ​

sql
-- 调试触发器的方法：

-- 1. 使用临时表记录调试信息
CREATE TABLE trigger_debug (
    id INT AUTO_INCREMENT PRIMARY KEY,
    trigger_name VARCHAR(100),
    message TEXT,
    created_at DATETIME
);

DELIMITER //
CREATE TRIGGER debug_trigger_example
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    -- 记录调试信息
    INSERT INTO trigger_debug (trigger_name, message, created_at)
    VALUES ('debug_trigger_example', 
            CONCAT('插入员工：ID=', NEW.id, ', 姓名=', NEW.name),
            NOW());
END //
DELIMITER ;

-- 查看调试信息
SELECT * FROM trigger_debug ORDER BY id DESC;

-- 2. 使用SELECT输出调试信息（注意：在某些客户端可能不显示）
DELIMITER //
CREATE TRIGGER debug_select_example
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    SELECT CONCAT('已插入员工：', NEW.name) AS debug_info;
END //
DELIMITER ;

本章小结 ​

本章详细介绍了MySQL触发器的相关知识：

  1. 触发器基础：理解触发器的概念、特点和应用场景
  2. 创建触发器：掌握INSERT、UPDATE、DELETE三种触发器的创建方法
  3. NEW和OLD关键字：学会在触发器中访问变更前后的数据
  4. 触发时机：理解BEFORE和AFTER的区别和使用场景
  5. 实战案例：通过库存管理、审计日志、数据同步等案例掌握触发器的实际应用
  6. 管理触发器：学会查看、删除和修改触发器
  7. 限制和注意事项：了解触发器的限制条件和性能考虑

触发器是实现数据完整性约束和业务逻辑自动化的重要工具，但使用时需要注意性能影响和调试难度。下一章我们将学习事务的相关知识。