触发器：数据库的"自动响应"机制-阿里云开发者社区

💡 触发器 = 数据库的"自动触发器"，事件发生时自动执行预定义操作！

大家好呀！我是数据库小学妹👋

昨天我们学了存储过程，可以把一堆SQL打包，然后手动 CALL 调用。但今天遇到了一个自动化需求👇

我想自动记录“每次有人修改订单表，就把旧值和新值存到日志表” —— 难道每次更新都要手动调用一个存储过程吗？万一忘写了怎么办？

数据库早就想到了这个问题，它提供了一种 “自动触发” 的机制——触发器。就像你设置的闹钟，到了点自动响，不用你去按。

今天我就把自己学会的触发器分享出来，保证你看完也能让数据库帮你“自动干活”。

一、什么是触发器？

触发器是一种特殊的存储过程，它在特定的数据库操作（INSERT、UPDATE、DELETE）发生时自动触发执行，无需手动调用。

💡 类比：你养了一只猫，每次它跳上桌子（事件），你就喷水（动作）。触发器就是这种“当A发生时，自动做B”的规则。

✅ 触发器的优点

自动化：事件发生时自动执行
数据一致性：强制执行业务规则
审计追踪：自动记录数据变更
业务逻辑封装：复杂逻辑在数据库层实现
安全性：限制非法数据操作

⚠️ 触发器的限制

调试困难：不像存储过程容易调试
性能影响：触发器执行会增加操作时间
维护成本：逻辑分散在数据库和应用层
不可见性；应用层不知道触发器存在
递归触发：触发器可能触发其他触发器

二、触发器实战：三步创建触发器

🎯 基础语法

-- 创建触发器
CREATE TRIGGER 触发器名
BEFORE/AFTER INSERT/UPDATE/DELETE ON 表名
FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
-- 查看触发器
SHOW TRIGGERS;
-- 删除触发器
DROP TRIGGER 触发器名;

💡 完整示例

-- 创建测试表
CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE order_audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    action VARCHAR(20),  -- create/update/delete
    old_status VARCHAR(20),
    new_status VARCHAR(20),
    operator VARCHAR(50),
    action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- ========================
-- 方案1：没有触发器（每次都要手动记录）
-- ========================
-- 创建订单
INSERT INTO orders (user_id, product_id, amount, status) 
VALUES (1, 101, 2999.00, 'pending');
-- 手动记录日志
INSERT INTO order_audit_log (order_id, action, old_status, new_status, operator) 
VALUES (LAST_INSERT_ID(), 'create', NULL, 'pending', 'admin');
-- ========================
-- 方案2：有触发器（自动记录，无需手动）
-- ========================
-- 1. 创建订单创建触发器
DELIMITER $$
CREATE TRIGGER trg_order_after_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit_log (order_id, action, old_status, new_status, operator)
    VALUES (NEW.id, 'create', NULL, NEW.status, 'system');
END$$
DELIMITER ;
-- 2. 创建订单更新触发器
DELIMITER $$
CREATE TRIGGER trg_order_after_update
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != NEW.status THEN
        INSERT INTO order_audit_log (order_id, action, old_status, new_status, operator)
        VALUES (NEW.id, 'update', OLD.status, NEW.status, 'system');
    END IF;
END$$
DELIMITER ;
-- 3. 创建订单删除触发器
DELIMITER $$
CREATE TRIGGER trg_order_before_delete
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit_log (order_id, action, old_status, new_status, operator)
    VALUES (OLD.id, 'delete', OLD.status, NULL, 'system');
END$$
DELIMITER ;
-- 测试：现在只需要操作orders表，日志自动记录！
INSERT INTO orders (user_id, product_id, amount, status) 
VALUES (2, 102, 1999.00, 'pending');
UPDATE orders SET status = 'paid' WHERE id = 1;
DELETE FROM orders WHERE id = 1;
-- 查看审计日志
SELECT * FROM order_audit_log;

三、触发器的类型和时机

📊 触发器类型

📊 触发时机

💡 时机选择示例

DELIMITER $$
-- BEFORE INSERT：自动填充创建时间
CREATE TRIGGER trg_user_before_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.created_by = 'system';
END$$
-- AFTER INSERT：记录日志
CREATE TRIGGER trg_user_after_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit_log (user_id, action, action_time)
    VALUES (NEW.id, 'create', NOW());
END$$
-- BEFORE UPDATE：记录修改时间
CREATE TRIGGER trg_user_before_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    SET NEW.updated_by = 'system';
END$$
-- AFTER UPDATE：记录变更详情
CREATE TRIGGER trg_user_after_update
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    IF OLD.email != NEW.email THEN
        INSERT INTO user_change_log (user_id, field_name, old_value, new_value, change_time)
        VALUES (NEW.id, 'email', OLD.email, NEW.email, NOW());
    END IF;
END$$
DELIMITER ;

四、触发器的实际应用场景

🎯 场景1：自动填充字段

DELIMITER $$
-- 自动填充创建和修改时间
CREATE TRIGGER trg_auto_timestamp
BEFORE INSERT ON any_table
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END$$
CREATE TRIGGER trg_auto_update_timestamp
BEFORE UPDATE ON any_table
FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
END$$
DELIMITER ;

🎯 场景2：数据验证

DELIMITER $$
-- 验证订单金额不能为负数
CREATE TRIGGER trg_order_before_insert_validate
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '订单金额不能为负数！';
    END IF;
    
    IF NEW.amount = 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '订单金额不能为0！';
    END IF;
END$$
-- 验证邮箱格式
CREATE TRIGGER trg_user_before_insert_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email NOT REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '邮箱格式不正确！';
    END IF;
END$$
DELIMITER ;

🎯 场景3：级联操作

DELIMITER $$
-- 删除用户时，级联删除相关数据
CREATE TRIGGER trg_user_before_delete_cascade
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    -- 删除用户的订单
    DELETE FROM orders WHERE user_id = OLD.id;
    
    -- 删除用户的评论
    DELETE FROM comments WHERE user_id = OLD.id;
    
    -- 删除用户的收藏
    DELETE FROM favorites WHERE user_id = OLD.id;
END$$
-- 更新产品时，同步更新相关表
CREATE TRIGGER trg_product_after_update_sync
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    -- 更新订单中的产品名称（如果产品名称变了）
    IF OLD.name != NEW.name THEN
        UPDATE order_items 
        SET product_name = NEW.name 
        WHERE product_id = NEW.id;
    END IF;
END$$
DELIMITER ;

🎯 场景4：业务规则强制执行

DELIMITER $$
-- 库存不足时，禁止创建订单
CREATE TRIGGER trg_order_before_insert_stock_check
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
    DECLARE available_stock INT;
    
    SELECT stock INTO available_stock 
    FROM products 
    WHERE id = NEW.product_id;
    
    IF available_stock < NEW.quantity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '库存不足，无法创建订单！';
    END IF;
    
    -- 扣减库存
    UPDATE products 
    SET stock = stock - NEW.quantity 
    WHERE id = NEW.product_id;
END$$
-- 订单完成后，自动增加用户积分
CREATE TRIGGER trg_order_after_update_points
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
    IF OLD.status != 'completed' AND NEW.status = 'completed' THEN
        UPDATE users 
        SET points = points + FLOOR(NEW.amount / 100) 
        WHERE id = NEW.user_id;
    END IF;
END$$
DELIMITER ;

🎯 场景5：数据审计和追踪

DELIMITER $$
-- 全面的用户操作审计
CREATE TRIGGER trg_user_full_audit
AFTER INSERT ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, details, audit_time)
    VALUES (
        NEW.id, 
        'INSERT', 
        CONCAT('创建用户: ', NEW.username, ', 邮箱: ', NEW.email),
        NOW()
    );
END$$
CREATE TRIGGER trg_user_update_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
    DECLARE changes TEXT DEFAULT '';
    
    IF OLD.username != NEW.username THEN
        SET changes = CONCAT(changes, '用户名: ', OLD.username, ' -> ', NEW.username, '; ');
    END IF;
    
    IF OLD.email != NEW.email THEN
        SET changes = CONCAT(changes, '邮箱: ', OLD.email, ' -> ', NEW.email, '; ');
    END IF;
    
    IF OLD.status != NEW.status THEN
        SET changes = CONCAT(changes, '状态: ', OLD.status, ' -> ', NEW.status, '; ');
    END IF;
    
    IF changes != '' THEN
        INSERT INTO user_audit (user_id, action, details, audit_time)
        VALUES (NEW.id, 'UPDATE', changes, NOW());
    END IF;
END$$
CREATE TRIGGER trg_user_delete_audit
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, action, details, audit_time)
    VALUES (
        OLD.id, 
        'DELETE', 
        CONCAT('删除用户: ', OLD.username, ', 邮箱: ', OLD.email),
        NOW()
    );
END$$
DELIMITER ;

五、触发器与存储过程的区别

一句话总结：

需要“自动响应” → 触发器
需要“主动调用” → 存储过程

六、新手避坑指南（血泪总结😭）

七、今日学习心得

今天的内容总结成三句话：

触发器是自动执行的，当表发生 INSERT/UPDATE/DELETE 时触发
BEFORE 用于校验和修改，AFTER 用于记录日志和同步
适合审计、校验、自动同步，但别滥用，避免性能陷阱

👋 我是数据库小学妹一个用设计师思维学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕

本文为个人学习总结，所有命令均在MySQL 8.0环境下验证。触发器虽方便，但请谨慎使用，保持数据库逻辑清晰。

触发器：数据库的"自动响应"机制