MySQL 触发器详解：自动化数据库操作的利器

MySQL 触发器(Trigger)是一种特殊的存储过程，它在特定事件(INSERT、UPDATE、DELETE)发生时自动执行。触发器可以帮助我们实现数据完整性约束、审计跟踪、自动计算等需求。

触发器基本概念

触发器是与表相关联的数据库对象，当表发生特定事件时自动执行。触发器的主要特点：

• 与表绑定，不能单独存在
• 在数据变更前(BEFORE)或变更后(AFTER)触发
• 可以对INSERT、UPDATE、DELETE操作设置触发器
• 可以访问被修改的数据(通过OLD和NEW关键字)

触发器语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
trigger_body

触发器示例

示例1：自动记录修改时间

-- 创建测试表
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2),
    last_updated TIMESTAMP
);

-- 创建触发器：在更新时自动设置last_updated字段
DELIMITER //
CREATE TRIGGER update_product_timestamp
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    SET NEW.last_updated = CURRENT_TIMESTAMP;
END//
DELIMITER ;

-- 测试触发器
INSERT INTO products (name, price) VALUES ('Laptop', 999.99);
UPDATE products SET price = 899.99 WHERE name = 'Laptop';
-- 查看结果，last_updated字段会自动更新

示例2：数据完整性检查

-- 创建触发器：确保价格不为负
DELIMITER //
CREATE TRIGGER check_product_price
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END//
DELIMITER ;

-- 测试触发器
INSERT INTO products (name, price) VALUES ('Invalid Product', -10);
-- 这将触发错误：Price cannot be negative

示例3：审计日志记录

-- 创建审计日志表
CREATE TABLE product_audit (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    changed_by VARCHAR(100),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器：记录价格变更
DELIMITER //
CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO product_audit (
            product_id, old_price, new_price, changed_by
        ) VALUES (
            NEW.id, OLD.price, NEW.price, CURRENT_USER()
        );
    END IF;
END//
DELIMITER ;

-- 测试触发器
UPDATE products SET price = 799.99 WHERE name = 'Laptop';
-- 查看product_audit表，会有一条新记录

示例4：级联操作

-- 创建订单和订单明细表
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10,2) DEFAULT 0
);

CREATE TABLE order_items (
    item_id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_name VARCHAR(100),
    quantity INT,
    unit_price DECIMAL(10,2),
    subtotal DECIMAL(10,2),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

-- 创建触发器：插入订单明细时更新订单总额
DELIMITER //
CREATE TRIGGER update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = total_amount + NEW.subtotal
    WHERE order_id = NEW.order_id;
END//
DELIMITER ;

-- 创建触发器：删除订单明细时更新订单总额
DELIMITER //
CREATE TRIGGER update_order_total_delete
AFTER DELETE ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders 
    SET total_amount = total_amount - OLD.subtotal
    WHERE order_id = OLD.order_id;
END//
DELIMITER ;

-- 测试触发器
INSERT INTO orders (order_date) VALUES (CURRENT_TIMESTAMP);
INSERT INTO order_items (order_id, product_name, quantity, unit_price, subtotal)
VALUES (1, 'Laptop', 1, 799.99, 799.99);
-- 查看orders表，total_amount会自动更新

触发器管理

查看触发器

-- 查看所有触发器
SHOW TRIGGERS;

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

修改触发器

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

DROP TRIGGER IF EXISTS trigger_name;
-- 然后重新创建触发器

删除触发器

DROP TRIGGER [IF EXISTS] trigger_name;

触发器最佳实践

1. 保持简单：触发器逻辑应尽量简单，避免复杂业务逻辑
2. 注意性能：触发器会增加数据库负载，特别是在高频操作的表上
3. 避免递归：小心设计触发器，避免无限递归(如触发器A触发触发器B，B又触发A)
4. 文档记录：触发器是隐式执行的，应有良好文档说明其存在和用途
5. 测试验证：触发器可能影响数据一致性，应充分测试

总结

MySQL 触发器是强大的自动化工具，可以用于： - 自动维护数据完整性 - 实现审计跟踪 - 自动计算派生数据 - 执行复杂业务规则

合理使用触发器可以减少应用层代码，提高数据一致性，但也要注意避免过度使用导致系统难以维护。