SQL 触发器

触发器是一种特殊的存储过程，当表上发生特定事件时自动执行。本章将详细介绍触发器的创建和使用。

什么是触发器？

触发器（Trigger）是与表关联的数据库对象，当对表执行 INSERT、UPDATE 或 DELETE 操作时，触发器会自动执行。

触发器的用途

• 数据验证：在插入或更新前验证数据
• 审计日志：记录数据的变更历史
• 级联更新：更新相关表的数据
• 数据同步：保持多个表的数据一致
• 业务规则：实现复杂的业务约束

触发器的优缺点

优点：

• 自动执行，无需手动调用
• 保证数据一致性
• 可以实现复杂的业务规则

缺点：

• 隐藏了业务逻辑，难以调试
• 增加数据库负担
• 可能导致性能问题
• 不易于移植

创建触发器

基本语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;

触发时机

• BEFORE：在事件发生之前执行
• AFTER：在事件发生之后执行

触发事件

• INSERT：插入数据时触发
• UPDATE：更新数据时触发
• DELETE：删除数据时触发

INSERT 触发器

BEFORE INSERT

在插入数据前执行，可以修改要插入的数据：

DELIMITER //

CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- 自动生成创建时间
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;
    
    -- 验证邮箱格式
    IF NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;
    
    -- 密码加密
    SET NEW.password = MD5(NEW.password);
END //

DELIMITER ;

-- 测试
INSERT INTO users (name, email, password) VALUES ('张三', '[email protected]', '123456');
-- 密码会被自动加密，created_at 会被自动设置

NEW 关键字：在 INSERT 触发器中，NEW 代表将要插入的新行。

AFTER INSERT

在插入数据后执行，适合记录日志：

DELIMITER //

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 记录订单日志
    INSERT INTO order_logs (order_id, action, action_time, user_id)
    VALUES (NEW.id, 'CREATE', NOW(), NEW.user_id);
    
    -- 更新用户订单统计
    UPDATE users 
    SET order_count = order_count + 1,
        total_spent = total_spent + NEW.total
    WHERE id = NEW.user_id;
END //

DELIMITER ;

UPDATE 触发器

BEFORE UPDATE

在更新数据前执行，可以验证或修改数据：

DELIMITER //

CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    -- 价格不能降低超过 50%
    IF NEW.price < OLD.price * 0.5 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '降价幅度不能超过 50%';
    END IF;
    
    -- 记录价格变更
    IF NEW.price != OLD.price THEN
        SET NEW.price_updated_at = NOW();
    END IF;
    
    -- 自动更新修改时间
    SET NEW.updated_at = NOW();
END //

DELIMITER ;

OLD 和 NEW 关键字：

• OLD：更新前的旧值
• NEW：更新后的新值

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 THEN
        INSERT INTO user_change_logs (
            user_id, 
            old_name, new_name, 
            old_email, new_email, 
            changed_at
        ) VALUES (
            NEW.id, 
            OLD.name, NEW.name, 
            OLD.email, NEW.email, 
            NOW()
        );
    END IF;
END //

DELIMITER ;

DELETE 触发器

BEFORE DELETE

在删除数据前执行，可以执行级联检查：

DELIMITER //

CREATE TRIGGER before_user_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    -- 检查用户是否有订单
    IF EXISTS (SELECT 1 FROM orders WHERE user_id = OLD.id) THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '该用户有订单记录，不能删除';
    END IF;
END //

DELIMITER ;

OLD 关键字：在 DELETE 触发器中，OLD 代表将要删除的行。

AFTER DELETE

在删除数据后执行，适合记录删除日志或清理关联数据：

DELIMITER //

CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
    -- 记录删除日志
    INSERT INTO order_logs (order_id, action, action_time)
    VALUES (OLD.id, 'DELETE', NOW());
    
    -- 恢复库存
    UPDATE products p
    JOIN order_items oi ON oi.order_id = OLD.id AND oi.product_id = p.id
    SET p.stock = p.stock + oi.quantity;
    
    -- 更新用户统计
    UPDATE users 
    SET order_count = order_count - 1,
        total_spent = total_spent - OLD.total
    WHERE id = OLD.user_id;
END //

DELIMITER ;

触发器中的 NEW 和 OLD

触发事件	NEW	OLD
INSERT	✓ 可读可写	✗ 不可用
UPDATE	✓ 可读可写	✓ 只读
DELETE	✗ 不可用	✓ 只读

-- 示例：记录所有数据变更
DELIMITER //

CREATE TRIGGER after_product_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_audit_log (
        product_id,
        field_name,
        old_value,
        new_value,
        changed_at
    ) VALUES
    (NEW.id, 'name', OLD.name, NEW.name, NOW()),
    (NEW.id, 'price', OLD.price, NEW.price, NOW()),
    (NEW.id, 'stock', OLD.stock, NEW.stock, NOW());
END //

DELIMITER ;

查看和管理触发器

查看触发器

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

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

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

-- 从信息模式查询
SELECT 
    TRIGGER_NAME,
    EVENT_MANIPULATION,
    EVENT_OBJECT_TABLE,
    ACTION_TIMING
FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database';

删除触发器

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

禁用和启用触发器

MySQL 不支持直接禁用触发器，但可以通过以下方式实现：

-- 方式 1：删除触发器
DROP TRIGGER IF EXISTS trigger_name;

-- 方式 2：使用条件判断
DELIMITER //

CREATE TRIGGER conditional_trigger
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    -- 通过变量控制是否执行
    IF @disable_triggers IS NULL OR @disable_triggers = 0 THEN
        -- 触发器逻辑
        SET NEW.created_at = NOW();
    END IF;
END //

DELIMITER ;

-- 禁用触发器
SET @disable_triggers = 1;
-- 执行操作...
INSERT INTO users (name) VALUES ('测试');

-- 启用触发器
SET @disable_triggers = 0;

触发器实战案例

案例 1：数据审计

-- 创建审计表
CREATE TABLE employee_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    action VARCHAR(10),
    old_data JSON,
    new_data JSON,
    changed_by VARCHAR(50),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器
DELIMITER //

CREATE TRIGGER employee_audit_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, new_data, changed_by)
    VALUES (
        NEW.id, 
        'INSERT',
        JSON_OBJECT('name', NEW.name, 'department', NEW.department, 'salary', NEW.salary),
        CURRENT_USER()
    );
END //

CREATE TRIGGER employee_update_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, old_data, new_data, changed_by)
    VALUES (
        NEW.id, 
        'UPDATE',
        JSON_OBJECT('name', OLD.name, 'department', OLD.department, 'salary', OLD.salary),
        JSON_OBJECT('name', NEW.name, 'department', NEW.department, 'salary', NEW.salary),
        CURRENT_USER()
    );
END //

CREATE TRIGGER employee_delete_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (employee_id, action, old_data, changed_by)
    VALUES (
        OLD.id, 
        'DELETE',
        JSON_OBJECT('name', OLD.name, 'department', OLD.department, 'salary', OLD.salary),
        CURRENT_USER()
    );
END //

DELIMITER ;

案例 2：库存管理

-- 订单明细表插入后减少库存
DELIMITER //

CREATE TRIGGER after_order_item_insert
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock = stock - NEW.quantity,
        updated_at = NOW()
    WHERE id = NEW.product_id;
    
    -- 检查库存是否不足
    IF (SELECT stock FROM products WHERE id = NEW.product_id) < 10 THEN
        INSERT INTO low_stock_alerts (product_id, current_stock, alert_time)
        VALUES (NEW.product_id, 
                (SELECT stock FROM products WHERE id = NEW.product_id), 
                NOW());
    END IF;
END //

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

DELIMITER ;

案例 3：数据同步

-- 同步用户状态到缓存表
DELIMITER //

CREATE TRIGGER sync_user_status
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO user_cache (user_id, status, updated_at)
        VALUES (NEW.id, NEW.status, NOW())
        ON DUPLICATE KEY UPDATE 
            status = NEW.status,
            updated_at = NOW();
    END IF;
END //

DELIMITER ;

案例 4：计算字段自动更新

-- 更新订单时自动计算总额
DELIMITER //

CREATE TRIGGER calculate_order_total_before_insert
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    -- 从订单明细计算总额
    SELECT COALESCE(SUM(price * quantity), 0) INTO NEW.total
    FROM order_items
    WHERE order_id = NEW.id;
END //

-- 订单明细变更后更新订单总额
CREATE TRIGGER update_order_total_after_item_change
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders o
    SET total = (
        SELECT COALESCE(SUM(price * quantity), 0)
        FROM order_items
        WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END //

DELIMITER ;

触发器最佳实践

1. 避免递归触发

-- 危险：可能导致无限循环
CREATE TRIGGER update_user_balance
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    -- 这会再次触发 UPDATE，造成无限循环！
    UPDATE users SET last_modified = NOW() WHERE id = NEW.id;
END;

-- 解决方案：使用 BEFORE 触发器
CREATE TRIGGER update_user_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.last_modified = NOW();
END;

2. 保持触发器简洁

-- 不好：触发器中包含复杂逻辑
CREATE TRIGGER complex_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    -- 大量复杂的业务逻辑...
    -- 难以调试和维护
END;

-- 好：触发器调用存储过程
CREATE TRIGGER process_order_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    CALL process_order(NEW.id);
END;

3. 错误处理

DELIMITER //

CREATE TRIGGER safe_insert_trigger
BEFORE INSERT ON critical_table
FOR EACH ROW
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 记录错误但不阻止插入
        INSERT INTO error_logs (error_message, created_at)
        VALUES (CONCAT('Trigger error for table critical_table'), NOW());
    END;
    
    -- 触发器逻辑
    IF NEW.value < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Value cannot be negative';
    END IF;
END //

DELIMITER ;

4. 性能考虑

-- 触发器会增加操作的开销
-- 对于批量操作，考虑使用存储过程代替

-- 不好：每行都触发
CREATE TRIGGER log_every_insert
AFTER INSERT ON large_table
FOR EACH ROW
BEGIN
    INSERT INTO logs (message) VALUES ('Row inserted');
END;

-- 好：在应用层批量处理，或使用定时任务

MySQL 8.0 触发器新特性

多触发器支持

MySQL 8.0 允许同一事件有多个触发器：

-- 第一个触发器
CREATE TRIGGER before_user_insert_1
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END;

-- 第二个触发器（MySQL 8.0+）
CREATE TRIGGER before_user_insert_2
BEFORE INSERT ON users
FOR EACH ROW FOLLOWS before_user_insert_1
BEGIN
    SET NEW.updated_at = NOW();
END;

-- 使用 PRECEDES 或 FOLLOWS 指定执行顺序

小结

本章我们学习了：

1. 触发器概念：自动执行的存储过程
2. 触发时机：BEFORE 和 AFTER
3. 触发事件：INSERT、UPDATE、DELETE
4. NEW 和 OLD：访问新值和旧值
5. 触发器管理：查看、创建、删除
6. 实战案例：审计、库存管理、数据同步

练习

1. 创建一个触发器，在用户注册时自动设置创建时间
2. 创建一个触发器，记录产品价格变更历史
3. 创建一个触发器，防止删除有订单的用户
4. 实现一个完整的审计系统，记录所有表的变更

参考资源

• MySQL 官方文档 - 触发器
• MySQL 官方文档 - CREATE TRIGGER