Appearance
MySQL触发器
触发器是在特定数据库事件（如INSERT、UPDATE、DELETE）发生时自动执行的特殊存储程序。它们提供了一种强大而灵活的方式来维护数据完整性、实施业务规则和自动化数据库操作。
触发器基础
触发器概念
触发器是与表相关联的数据库对象，当表上发生特定事件时自动执行。触发器具有以下特征：
- 自动执行：无需手动调用
- 事件驱动：响应INSERT、UPDATE、DELETE操作
- 表级关联：每个触发器都与特定表关联
- 时机控制：可在事件发生前或后执行
创建触发器语法
sql
DELIMITER //
CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
-- 触发器逻辑
END //
DELIMITER ;
-- trigger_time: BEFORE 或 AFTER
-- trigger_event: INSERT、UPDATE 或 DELETE
触发器类型
INSERT触发器
在插入新记录时执行的触发器。
sql
-- 创建用户表
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 创建INSERT触发器：自动设置创建时间
DELIMITER //
CREATE TRIGGER tr_users_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
SET NEW.updated_at = NOW();
-- 记录操作日志
INSERT INTO audit_log (table_name, operation, record_id, timestamp)
VALUES ('users', 'INSERT', NEW.id, NOW());
END //
DELIMITER ;
UPDATE触发器
在更新记录时执行的触发器。
sql
-- 创建UPDATE触发器：记录更新前后值的变化
DELIMITER //
CREATE TRIGGER tr_users_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
-- 如果邮箱发生变化，记录变更日志
IF OLD.email != NEW.email THEN
INSERT INTO change_log (table_name, record_id, field_name, old_value, new_value, changed_at)
VALUES ('users', NEW.id, 'email', OLD.email, NEW.email, NOW());
END IF;
END //
DELIMITER ;
DELETE触发器
在删除记录时执行的触发器。
sql
-- 创建DELETE触发器：软删除而不是物理删除
DELIMITER //
CREATE TRIGGER tr_users_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
-- 插入到历史表而不是真正删除
INSERT INTO users_history (id, username, email, created_at, updated_at, deleted_at)
VALUES (OLD.id, OLD.username, OLD.email, OLD.created_at, OLD.updated_at, NOW());
-- 记录删除操作
INSERT INTO audit_log (table_name, operation, record_id, timestamp)
VALUES ('users', 'DELETE', OLD.id, NOW());
-- 阻止物理删除
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请勿直接删除用户，请使用更新状态的方式';
END //
DELIMITER ;
-- 实际实现软删除的触发器
DELIMITER //
CREATE TRIGGER tr_users_soft_delete
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
-- 软删除：更新状态而不是物理删除
UPDATE users SET status = 'deleted', deleted_at = NOW() WHERE id = OLD.id;
-- 记录操作
INSERT INTO audit_log (table_name, operation, record_id, timestamp)
VALUES ('users', 'SOFT_DELETE', OLD.id, NOW());
-- 阻止实际删除操作
SET @delete_blocked = 1;
END //
DELIMITER ;
-- 更实用的删除触发器：将记录移动到归档表
CREATE TABLE users_archive LIKE users;
DELIMITER //
CREATE TRIGGER tr_users_archive_delete
AFTER DELETE ON users
FOR EACH ROW
BEGIN
INSERT INTO users_archive (id, username, email, created_at, updated_at)
VALUES (OLD.id, OLD.username, OLD.email, OLD.created_at, OLD.updated_at);
END //
DELIMITER ;
NEW和OLD关键字
在触发器中，NEW和OLD关键字用于引用受影响的行：
- NEW：在INSERT和UPDATE触发器中，引用即将插入或更新的行
- OLD：在UPDATE和DELETE触发器中，引用更新前或删除前的行
sql
-- 示例：验证更新操作
DELIMITER //
CREATE TRIGGER tr_validate_user_update
BEFORE UPDATE 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;
-- 防止重要用户信息被意外修改
IF OLD.username != NEW.username AND OLD.username LIKE 'admin%' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '管理员用户名不能被修改';
END IF;
END //
DELIMITER ;
实际应用场景
1. 审计日志
sql
-- 创建审计日志表
CREATE TABLE audit_log (
id INT AUTO_INCREMENT PRIMARY KEY,
table_name VARCHAR(50),
operation VARCHAR(10), -- INSERT, UPDATE, DELETE
record_id INT,
old_values JSON,
new_values JSON,
user_name VARCHAR(50),
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 创建通用审计触发器
DELIMITER //
CREATE TRIGGER tr_users_audit
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO audit_log (table_name, operation, record_id, old_values, new_values, user_name)
VALUES (
'users',
'UPDATE',
NEW.id,
JSON_OBJECT('username', OLD.username, 'email', OLD.email),
JSON_OBJECT('username', NEW.username, 'email', NEW.email),
USER()
);
END //
DELIMITER ;
2. 数据验证
sql
-- 订单表
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2),
total_amount DECIMAL(10,2),
status ENUM('pending', 'confirmed', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 库存表
CREATE TABLE inventory (
product_id INT PRIMARY KEY,
stock_quantity INT DEFAULT 0
);
-- 订单插入前验证库存
DELIMITER //
CREATE TRIGGER tr_validate_order_stock
BEFORE INSERT ON orders
FOR EACH ROW
BEGIN
DECLARE available_stock INT DEFAULT 0;
-- 获取可用库存
SELECT IFNULL(stock_quantity, 0) INTO available_stock
FROM inventory
WHERE product_id = NEW.product_id;
-- 检查库存是否充足
IF available_stock < NEW.quantity THEN
SET @error_message = CONCAT('库存不足，当前库存：', available_stock, '，需要：', NEW.quantity);
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @error_message;
END IF;
-- 计算总金额
SET NEW.total_amount = NEW.unit_price * NEW.quantity;
END //
DELIMITER ;
-- 订单更新前验证
DELIMITER //
CREATE TRIGGER tr_validate_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
-- 防止已发货订单被修改数量
IF OLD.status IN ('shipped', 'delivered') AND OLD.quantity != NEW.quantity THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已发货或已交付订单不能修改数量';
END IF;
-- 重新计算总金额
SET NEW.total_amount = NEW.unit_price * NEW.quantity;
END //
DELIMITER ;
3. 自动计算字段
sql
-- 购物车表
CREATE TABLE cart_items (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
product_id INT,
quantity INT DEFAULT 1,
unit_price DECIMAL(10,2),
line_total DECIMAL(10,2)
);
-- 自动计算行总计
DELIMITER //
CREATE TRIGGER tr_calculate_line_total
BEFORE INSERT ON cart_items
FOR EACH ROW
BEGIN
SET NEW.line_total = NEW.unit_price * NEW.quantity;
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_update_line_total
BEFORE UPDATE ON cart_items
FOR EACH ROW
BEGIN
SET NEW.line_total = NEW.unit_price * NEW.quantity;
END //
DELIMITER ;
4. 数据同步
sql
-- 用户统计表
CREATE TABLE user_stats (
user_id INT PRIMARY KEY,
total_orders INT DEFAULT 0,
total_spent DECIMAL(12,2) DEFAULT 0.00,
last_order_date DATE,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- 订单完成后更新用户统计
DELIMITER //
CREATE TRIGGER tr_update_user_stats
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
-- 只有当订单状态变为已完成时才更新统计
IF OLD.status != 'delivered' AND NEW.status = 'delivered' THEN
INSERT INTO user_stats (user_id, total_orders, total_spent, last_order_date)
VALUES (NEW.user_id, 1, NEW.total_amount, DATE(NEW.created_at))
ON DUPLICATE KEY UPDATE
total_orders = total_orders + 1,
total_spent = total_spent + NEW.total_amount,
last_order_date = DATE(NEW.created_at);
END IF;
-- 如果订单被取消，减少统计（如果订单之前已完成）
IF OLD.status = 'delivered' AND NEW.status = 'cancelled' THEN
UPDATE user_stats SET
total_orders = total_orders - 1,
total_spent = total_spent - NEW.total_amount
WHERE user_id = NEW.user_id;
END IF;
END //
DELIMITER ;
触发器管理
查看触发器
sql
-- 查看所有触发器
SHOW TRIGGERS;
-- 查看特定表的触发器
SHOW TRIGGERS WHERE `Table` = 'users';
-- 查看特定数据库的触发器
SHOW TRIGGERS FROM your_database_name;
-- 从information_schema查看
SELECT
TRIGGER_NAME,
EVENT_MANIPULATION,
EVENT_OBJECT_TABLE,
ACTION_TIMING,
ACTION_STATEMENT
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database_name';
修改和删除触发器
sql
-- 删除触发器
DROP TRIGGER IF EXISTS tr_users_insert;
-- 要修改触发器，需要先删除再重新创建
DROP TRIGGER IF EXISTS tr_users_update;
DELIMITER //
CREATE TRIGGER tr_users_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.updated_at = NOW();
-- 增强的更新逻辑
IF OLD.email != NEW.email THEN
INSERT INTO email_change_log (user_id, old_email, new_email, change_time)
VALUES (NEW.id, OLD.email, NEW.email, NOW());
END IF;
END //
DELIMITER ;
触发器最佳实践
1. 性能考虑
sql
-- 避免在触发器中执行耗时操作
DELIMITER //
CREATE TRIGGER tr_efficient_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- 快速操作：只记录必要信息
INSERT INTO order_stats_queue (order_id, action, created_at)
VALUES (NEW.id, 'process', NOW());
-- 复杂处理交给后台任务或存储过程
END //
DELIMITER ;
2. 错误处理
sql
DELIMITER //
CREATE TRIGGER tr_safe_trigger
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
-- 数据验证
IF NEW.email IS NOT NULL AND NEW.email != '' THEN
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 IF;
-- 业务规则验证
IF NEW.username REGEXP '[^a-zA-Z0-9_]' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '用户名只能包含字母、数字和下划线';
END IF;
END //
DELIMITER ;
3. 避免无限循环
sql
-- 错误示例：可能导致无限循环
-- DELIMITER //
-- CREATE TRIGGER tr_bad_loop
-- AFTER UPDATE ON users
-- FOR EACH ROW
-- BEGIN
-- UPDATE users SET updated_at = NOW() WHERE id = NEW.id; -- 这会再次触发此触发器！
-- END //
-- DELIMITER ;
-- 正确示例：避免循环
DELIMITER //
CREATE TRIGGER tr_avoid_loop
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
-- 只在特定条件下更新，避免无限循环
IF NEW.status = 'premium' AND OLD.status != 'premium' THEN
INSERT INTO premium_activation_log (user_id, activated_at)
VALUES (NEW.id, NOW());
END IF;
END //
DELIMITER ;
高级触发器应用
1. 复杂业务规则
sql
-- 创建优惠券表
CREATE TABLE coupons (
id INT AUTO_INCREMENT PRIMARY KEY,
code VARCHAR(50) UNIQUE,
discount_percent DECIMAL(5,2),
max_uses INT,
used_count INT DEFAULT 0,
expires_at DATE
);
-- 订单使用优惠券时验证规则
DELIMITER //
CREATE TRIGGER tr_validate_coupon_use
BEFORE INSERT ON order_coupons
FOR EACH ROW
BEGIN
DECLARE coupon_valid BOOLEAN DEFAULT FALSE;
DECLARE remaining_uses INT DEFAULT 0;
-- 检查优惠券是否有效
SELECT
(expires_at >= CURDATE() AND used_count < max_uses) AS is_valid,
(max_uses - used_count) AS remaining
INTO coupon_valid, remaining_uses
FROM coupons
WHERE id = NEW.coupon_id;
IF NOT coupon_valid THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '优惠券无效或已过期';
END IF;
IF remaining_uses <= 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '优惠券使用次数已达上限';
END IF;
-- 更新优惠券使用次数
UPDATE coupons SET used_count = used_count + 1 WHERE id = NEW.coupon_id;
END //
DELIMITER ;
2. 数据一致性维护
sql
-- 分类表
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
slug VARCHAR(100) UNIQUE,
product_count INT DEFAULT 0
);
-- 产品表
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200),
category_id INT,
price DECIMAL(10,2)
);
-- 当产品插入/删除时更新分类的产品数量
DELIMITER //
CREATE TRIGGER tr_update_category_count_insert
AFTER INSERT ON products
FOR EACH ROW
BEGIN
UPDATE categories SET product_count = product_count + 1 WHERE id = NEW.category_id;
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_update_category_count_delete
AFTER DELETE ON products
FOR EACH ROW
BEGIN
UPDATE categories SET product_count = product_count - 1 WHERE id = OLD.category_id;
END //
DELIMITER ;
DELIMITER //
CREATE TRIGGER tr_update_category_count_update
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
-- 如果分类改变了，更新旧分类和新分类的计数
IF OLD.category_id != NEW.category_id THEN
UPDATE categories SET product_count = product_count - 1 WHERE id = OLD.category_id;
UPDATE categories SET product_count = product_count + 1 WHERE id = NEW.category_id;
END IF;
END //
DELIMITER ;
触发器的限制和注意事项
1. 限制
- 不能使用CALL语句调用存储过程中的动态SQL
- 不能返回结果集
- 不能使用某些SQL语句（如ALTER TABLE）
- 在某些情况下可能影响性能
2. 注意事项
sql
-- 考虑使用存储过程替代复杂触发器
DELIMITER //
CREATE PROCEDURE ProcessOrderWithValidation(
IN p_user_id INT,
IN p_product_id INT,
IN p_quantity INT
)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
RESIGNAL;
END;
START TRANSACTION;
-- 所有验证和处理都在存储过程中完成
-- 这样更容易测试和维护
COMMIT;
END //
DELIMITER ;
调试触发器
sql
-- 创建调试日志表
CREATE TABLE trigger_debug_log (
id INT AUTO_INCREMENT PRIMARY KEY,
trigger_name VARCHAR(100),
operation VARCHAR(20),
record_id INT,
debug_info TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 在触发器中记录调试信息
DELIMITER //
CREATE TRIGGER tr_debug_example
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
INSERT INTO trigger_debug_log (trigger_name, operation, record_id, debug_info)
VALUES ('tr_debug_example', 'INSERT', NEW.id, CONCAT('New order created: ', NEW.id));
END //
DELIMITER ;
总结
MySQL触发器是实现复杂业务逻辑和数据完整性的重要工具。在使用触发器时需要注意：
- 性能影响：触发器会增加每个相关操作的时间开销
- 调试困难：触发器逻辑隐藏在数据库层，难以调试
- 维护复杂性：触发器逻辑分散在数据库中，增加维护难度
- 执行顺序：多个触发器的执行顺序需要特别注意
- 错误处理：触发器中的错误可能阻止原始操作完成
触发器最适合用于：
- 数据验证和约束
- 审计日志记录
- 自动计算字段
- 数据同步和维护
在设计触发器时，应该权衡其便利性和潜在的复杂性，确保它们确实增加了价值而不是引入了不必要的复杂度。