MySQL 8.0 创建触发器

触发器是一种特殊类型的存储过程，它会在特定的表上发生特定事件时自动执行。MySQL 支持在 INSERT、UPDATE 和 DELETE 语句执行前后触发操作。本教程将详细介绍如何创建各种类型的触发器，包括基本语法、示例代码和最佳实践。

触发器的基本概念

触发器是一种与表相关联的数据库对象，当表上发生特定事件（如 INSERT、UPDATE 或 DELETE）时，触发器会自动执行。触发器可以用于数据验证、数据审计、维护派生数据、实现复杂的业务规则等场景。

MySQL 支持以下类型的触发器：

BEFORE INSERT：在插入数据之前触发
AFTER INSERT：在插入数据之后触发
BEFORE UPDATE：在更新数据之前触发
AFTER UPDATE：在更新数据之后触发
BEFORE DELETE：在删除数据之前触发
AFTER DELETE：在删除数据之后触发

创建触发器的语法

创建触发器的基本语法如下：

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器执行的语句
END;

其中：

trigger_name：触发器的名称，在数据库中必须唯一
BEFORE | AFTER：指定触发器是在事件发生前还是发生后执行
INSERT | UPDATE | DELETE：指定触发事件的类型
table_name：触发器关联的表名
FOR EACH ROW：表示触发器对每行数据都执行一次

创建单执行语句的触发器

对于只有一条执行语句的触发器，可以省略 BEGIN 和 END 关键字。下面是一些示例：

创建 BEFORE INSERT 触发器

-- 示例：在插入员工数据前自动设置创建时间和更新时间
delimiter //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.created_at = NOW(), NEW.updated_at = NOW();
delimiter ;

创建 AFTER INSERT 触发器

-- 示例：在插入订单后自动更新产品库存
delimiter //
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
UPDATE products SET stock_quantity = stock_quantity - NEW.quantity WHERE product_id = NEW.product_id;
delimiter ;

创建 BEFORE UPDATE 触发器

-- 示例：在更新员工数据前自动更新更新时间
delimiter //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
SET NEW.updated_at = NOW();
delimiter ;

创建 AFTER UPDATE 触发器

-- 示例：在更新产品价格后记录价格变更历史
delimiter //
CREATE TRIGGER after_product_price_update
AFTER UPDATE ON products
FOR EACH ROW
IF OLD.price <> NEW.price THEN
    INSERT INTO product_price_history (product_id, old_price, new_price, changed_at)
    VALUES (OLD.product_id, OLD.price, NEW.price, NOW());
END IF;
delimiter ;

创建 BEFORE DELETE 触发器

-- 示例：在删除客户前检查是否有未完成的订单
delimiter //
CREATE TRIGGER before_customer_delete
BEFORE DELETE ON customers
FOR EACH ROW
IF EXISTS (SELECT 1 FROM orders WHERE customer_id = OLD.customer_id AND status = 'PENDING') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该客户有未完成的订单，无法删除';
END IF;
delimiter ;

创建 AFTER DELETE 触发器

-- 示例：在删除订单后恢复产品库存
delimiter //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
UPDATE products SET stock_quantity = stock_quantity + OLD.quantity WHERE product_id = OLD.product_id;
delimiter ;

创建有多个执行语句的触发器

对于包含多条执行语句的触发器，需要使用 BEGIN 和 END 关键字，并使用 DELIMITER 语句更改语句分隔符。下面是一些复杂触发器的示例：

创建有多个执行语句的AFTER INSERT触发器

-- 示例1：在插入订单后执行多个后续操作
delimiter //
CREATE TRIGGER after_order_insert_complex
AFTER INSERT ON orders
FOR EACH ROW
begin
    -- 更新产品库存
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity, 
        updated_at = NOW() 
    WHERE product_id = NEW.product_id;
    
    -- 创建订单历史记录
    INSERT INTO order_history (order_id, status, created_at) 
    VALUES (NEW.order_id, 'PENDING', NOW());
    
    -- 更新客户的订单统计
    UPDATE customers 
    SET order_count = order_count + 1, 
        total_spent = total_spent + NEW.total_amount 
    WHERE customer_id = NEW.customer_id;
    
    -- 更新销售统计
    INSERT INTO sales_statistics (product_id, sale_date, quantity_sold, revenue) 
    VALUES (NEW.product_id, CURDATE(), NEW.quantity, NEW.total_amount) 
    ON DUPLICATE KEY UPDATE 
        quantity_sold = quantity_sold + NEW.quantity, 
        revenue = revenue + NEW.total_amount;
    
    -- 检查库存是否低于预警值
    IF (SELECT stock_quantity FROM products WHERE product_id = NEW.product_id) < 10 THEN
        INSERT INTO low_stock_alerts (product_id, alert_date) 
        VALUES (NEW.product_id, NOW());
    END IF;
    
    -- 发送订单确认通知
    INSERT INTO notifications (user_id, message, created_at, is_read) 
    VALUES (NEW.customer_id, CONCAT('您的订单 #', NEW.order_id, ' 已创建成功'), NOW(), 0);
end//
delimiter ;

-- 示例2：在插入员工后执行多个后续操作
delimiter //
CREATE TRIGGER after_employee_insert_complex
AFTER INSERT ON employees
FOR EACH ROW
begin
    -- 更新部门员工数量
    UPDATE departments 
    SET employee_count = employee_count + 1 
    WHERE department_id = NEW.department_id;
    
    -- 记录员工创建日志
    INSERT INTO employee_logs (employee_id, action, action_time, performed_by) 
    VALUES (NEW.employee_id, 'CREATE', NOW(), CURRENT_USER());
    
    -- 发送新员工入职通知给部门经理
    INSERT INTO notifications (user_id, message, created_at, priority)
    SELECT manager_id, CONCAT('新员工 ', NEW.first_name, ' ', NEW.last_name, ' 已加入您的部门'), NOW(), 'MEDIUM'
    FROM departments 
    WHERE department_id = NEW.department_id;
    
    -- 初始化员工培训计划
    INSERT INTO employee_training (employee_id, training_name, status, due_date)
    VALUES (NEW.employee_id, '入职培训', 'PENDING', DATE_ADD(NOW(), INTERVAL 7 DAY));
end//
delimiter ;

-- 示例3：在插入客户后执行多个后续操作
delimiter //
CREATE TRIGGER after_customer_insert_complex
AFTER INSERT ON customers
FOR EACH ROW
begin
    -- 创建客户档案
    INSERT INTO customer_profiles (customer_id, created_at) 
    VALUES (NEW.customer_id, NOW());
    
    -- 创建客户账户
    INSERT INTO customer_accounts (customer_id, balance, status) 
    VALUES (NEW.customer_id, 0.00, 'ACTIVE');
    
    -- 分配客户到默认组
    INSERT INTO customer_groups (customer_id, group_id, joined_date) 
    VALUES (NEW.customer_id, 1, NOW());  -- 1表示普通客户组
    
    -- 记录客户创建日志
    INSERT INTO customer_logs (customer_id, action, action_time, performed_by) 
    VALUES (NEW.customer_id, 'CREATE', NOW(), CURRENT_USER());
    
    -- 发送欢迎邮件通知
    INSERT INTO email_queue (recipient_email, subject, body, priority, status)
    VALUES (NEW.email, '欢迎加入我们', CONCAT('尊敬的 ', NEW.first_name, ' ', NEW.last_name, ',\n\n欢迎加入我们的服务！'), 'HIGH', 'PENDING');
    
    -- 更新客户统计信息
    INSERT INTO customer_statistics (stat_date, new_customers) 
    VALUES (CURDATE(), 1) 
    ON DUPLICATE KEY UPDATE 
        new_customers = new_customers + 1;
end//
delimiter ;

-- 测试示例：插入订单数据，触发器会执行多个后续操作
INSERT INTO orders (customer_id, product_id, quantity, total_amount) 
VALUES (101, 501, 2, 199.98);

-- 查看相关表的数据变化，验证触发器是否生效
-- 1. 检查产品库存是否减少
SELECT product_id, stock_quantity FROM products WHERE product_id = 501;

-- 2. 检查订单历史是否有记录
SELECT * FROM order_history WHERE order_id = LAST_INSERT_ID();

-- 3. 检查客户的订单统计是否更新
SELECT customer_id, order_count, total_spent FROM customers WHERE customer_id = 101;

-- 4. 检查销售统计是否更新
SELECT * FROM sales_statistics WHERE product_id = 501 AND sale_date = CURDATE();

-- 5. 检查是否有库存预警（如果库存低于10）
SELECT * FROM low_stock_alerts WHERE product_id = 501;

-- 6. 检查是否有通知发送
SELECT * FROM notifications WHERE user_id = 101 ORDER BY created_at DESC LIMIT 1;

创建有多个执行语句的BEFORE UPDATE触发器

-- 示例1：在更新产品前执行多个验证和处理操作
delimiter //
CREATE TRIGGER before_product_update_complex
BEFORE UPDATE ON products
FOR EACH ROW
begin
    -- 验证价格是否合法
    IF NEW.price <= 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '产品价格必须大于0';
    END IF;
    
    -- 验证库存数量是否合法
    IF NEW.stock_quantity < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '库存数量不能为负数';
    END IF;
    
    -- 记录价格变更历史（如果价格发生变化）
    IF OLD.price <> NEW.price THEN
        INSERT INTO product_price_history (product_id, old_price, new_price, changed_at, changed_by) 
        VALUES (OLD.product_id, OLD.price, NEW.price, NOW(), CURRENT_USER());
    END IF;
    
    -- 记录库存变更历史（如果库存发生变化）
    IF OLD.stock_quantity <> NEW.stock_quantity THEN
        INSERT INTO product_inventory_history (product_id, old_quantity, new_quantity, changed_at, changed_by) 
        VALUES (OLD.product_id, OLD.stock_quantity, NEW.stock_quantity, NOW(), CURRENT_USER());
    END IF;
    
    -- 设置产品更新时间和更新人
    SET NEW.updated_at = NOW();
    SET NEW.updated_by = CURRENT_USER();
    
    -- 如果产品状态变为禁用，标记相关的未处理订单
    IF OLD.status = 'ACTIVE' AND NEW.status = 'INACTIVE' THEN
        UPDATE orders 
        SET need_attention = 1, 
            updated_at = NOW() 
        WHERE product_id = NEW.product_id 
        AND status = 'PENDING';
    END IF;
end//
delimiter ;

-- 示例2：在更新员工数据前执行多个验证和处理操作
delimiter //
CREATE TRIGGER before_employee_update_complex
BEFORE UPDATE ON employees
FOR EACH ROW
begin
    -- 验证年龄是否合法
    IF NEW.age < 18 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '员工年龄必须满18岁';
    END IF;
    
    -- 验证邮箱格式是否合法
    IF NEW.email NOT LIKE '%@%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '请输入有效的电子邮箱地址';
    END IF;
    
    -- 记录部门变更历史（如果部门发生变化）
    IF OLD.department_id <> NEW.department_id THEN
        INSERT INTO employee_department_history (employee_id, from_department_id, to_department_id, change_date, changed_by) 
        VALUES (OLD.employee_id, OLD.department_id, NEW.department_id, NOW(), CURRENT_USER());
        
        -- 更新原部门的员工数量
        UPDATE departments 
        SET employee_count = employee_count - 1 
        WHERE department_id = OLD.department_id;
        
        -- 更新新部门的员工数量
        UPDATE departments 
        SET employee_count = employee_count + 1 
        WHERE department_id = NEW.department_id;
    END IF;
    
    -- 记录职位变更历史（如果职位发生变化）
    IF OLD.position <> NEW.position THEN
        INSERT INTO employee_position_history (employee_id, old_position, new_position, change_date, changed_by) 
        VALUES (OLD.employee_id, OLD.position, NEW.position, NOW(), CURRENT_USER());
    END IF;
    
    -- 设置员工更新时间和更新人
    SET NEW.updated_at = NOW();
    SET NEW.updated_by = CURRENT_USER();
end//
delimiter ;

-- 示例3：在更新订单前执行多个验证和处理操作
delimiter //
CREATE TRIGGER before_order_update_complex
BEFORE UPDATE ON orders
FOR EACH ROW
begin
    -- 验证订单状态转换是否合法
    IF OLD.status = 'COMPLETED' AND NEW.status <> 'COMPLETED' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已完成的订单状态不能修改';
    END IF;
    
    IF OLD.status = 'CANCELLED' AND NEW.status <> 'CANCELLED' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '已取消的订单状态不能修改';
    END IF;
    
    -- 记录订单状态变更历史
    IF OLD.status <> NEW.status THEN
        INSERT INTO order_status_history (order_id, old_status, new_status, changed_at, changed_by) 
        VALUES (OLD.order_id, OLD.status, NEW.status, NOW(), CURRENT_USER());
        
        -- 如果订单状态变为已完成，设置完成时间
        IF NEW.status = 'COMPLETED' THEN
            SET NEW.completion_date = NOW();
        END IF;
        
        -- 如果订单状态变为已取消，设置取消原因和取消时间
        IF NEW.status = 'CANCELLED' AND NEW.cancellation_reason IS NULL THEN
            SET NEW.cancellation_reason = '用户取消';
            SET NEW.cancellation_date = NOW();
        END IF;
    END IF;
    
    -- 设置订单更新时间和更新人
    SET NEW.updated_at = NOW();
    SET NEW.updated_by = CURRENT_USER();
end//
delimiter ;

-- 测试示例：更新产品数据，触发器会执行多个验证和处理操作
-- 成功的情况
UPDATE products 
SET price = 129.99, 
    stock_quantity = 45 
WHERE product_id = 1001;

-- 失败的情况（价格不合法）
-- UPDATE products 
-- SET price = -10.00 
-- WHERE product_id = 1001;

-- 失败的情况（库存数量不合法）
-- UPDATE products 
-- SET stock_quantity = -5 
-- WHERE product_id = 1001;

-- 查看相关表的数据变化，验证触发器是否生效
-- 1. 检查产品价格变更历史
SELECT * FROM product_price_history WHERE product_id = 1001 ORDER BY changed_at DESC LIMIT 1;

-- 2. 检查产品库存变更历史
SELECT * FROM product_inventory_history WHERE product_id = 1001 ORDER BY changed_at DESC LIMIT 1;

-- 3. 检查产品的更新时间和更新人
SELECT updated_at, updated_by FROM products WHERE product_id = 1001;

创建有多个执行语句的AFTER UPDATE触发器

-- 示例1：在更新订单后执行多个后续操作
delimiter //
CREATE TRIGGER after_order_update_complex
AFTER UPDATE ON orders
FOR EACH ROW
begin
    -- 如果订单状态变更为已付款，更新库存和创建发货单
    IF OLD.status = 'PENDING' AND NEW.status = 'PAID' THEN
        -- 更新产品库存
        UPDATE products 
        SET stock_quantity = stock_quantity - NEW.quantity, 
            updated_at = NOW() 
        WHERE product_id = NEW.product_id;
        
        -- 创建发货单
        INSERT INTO shipments (order_id, shipment_status, created_at) 
        VALUES (NEW.order_id, 'PENDING', NOW());
        
        -- 发送订单已付款通知
        INSERT INTO notifications (user_id, message, created_at, is_read)
        VALUES (NEW.customer_id, CONCAT('您的订单 #', NEW.order_id, ' 已付款，我们将尽快为您发货'), NOW(), 0);
    END IF;
    
    -- 如果订单状态变更为已发货，更新发货信息和发送通知
    IF OLD.status = 'PAID' AND NEW.status = 'SHIPPED' THEN
        -- 更新发货单状态
        UPDATE shipments 
        SET shipment_status = 'SHIPPED', 
            shipped_date = NOW() 
        WHERE order_id = NEW.order_id;
        
        -- 发送订单已发货通知
        INSERT INTO notifications (user_id, message, created_at, is_read)
        VALUES (NEW.customer_id, CONCAT('您的订单 #', NEW.order_id, ' 已发货'), NOW(), 0);
    END IF;
    
    -- 如果订单状态变更为已完成，更新完成信息和客户统计
    IF OLD.status <> 'COMPLETED' AND NEW.status = 'COMPLETED' THEN
        -- 更新客户统计信息
        UPDATE customers 
        SET completed_order_count = completed_order_count + 1, 
            last_completed_order_date = NOW() 
        WHERE customer_id = NEW.customer_id;
        
        -- 发送订单已完成通知
        INSERT INTO notifications (user_id, message, created_at, is_read)
        VALUES (NEW.customer_id, CONCAT('您的订单 #', NEW.order_id, ' 已完成，感谢您的购买'), NOW(), 0);
        
        -- 插入客户购买历史
        INSERT INTO customer_purchase_history (customer_id, order_id, purchase_date, amount)
        VALUES (NEW.customer_id, NEW.order_id, NOW(), NEW.total_amount);
    END IF;
    
    -- 如果订单被取消，恢复库存和发送通知
    IF OLD.status <> 'CANCELLED' AND NEW.status = 'CANCELLED' THEN
        -- 恢复产品库存
        UPDATE products 
        SET stock_quantity = stock_quantity + NEW.quantity, 
            updated_at = NOW() 
        WHERE product_id = NEW.product_id;
        
        -- 发送订单已取消通知
        INSERT INTO notifications (user_id, message, created_at, is_read)
        VALUES (NEW.customer_id, CONCAT('您的订单 #', NEW.order_id, ' 已取消'), NOW(), 0);
    END IF;
    
    -- 记录订单更新日志
    INSERT INTO order_update_logs (order_id, updated_fields, updated_at, updated_by)
    VALUES (NEW.order_id, 'status', NOW(), CURRENT_USER());
end//
delimiter ;

-- 示例2：在更新客户数据后执行多个后续操作
delimiter //
CREATE TRIGGER after_customer_update_complex
AFTER UPDATE ON customers
FOR EACH ROW
begin
    -- 如果客户等级发生变化，记录等级变更历史
    IF OLD.customer_level <> NEW.customer_level THEN
        INSERT INTO customer_level_history (customer_id, old_level, new_level, change_date, changed_by) 
        VALUES (NEW.customer_id, OLD.customer_level, NEW.customer_level, NOW(), CURRENT_USER());
        
        -- 发送等级变更通知
        INSERT INTO notifications (user_id, message, created_at, is_read)
        VALUES (NEW.customer_id, CONCAT('恭喜！您的会员等级已升级为', NEW.customer_level), NOW(), 0);
    END IF;
    
    -- 如果客户状态变为禁用，处理相关业务
    IF OLD.status = 'ACTIVE' AND NEW.status = 'INACTIVE' THEN
        -- 取消所有未处理的订单
        UPDATE orders 
        SET status = 'CANCELLED', 
            cancellation_reason = '客户账户已禁用', 
            updated_at = NOW() 
        WHERE customer_id = NEW.customer_id 
        AND status IN ('PENDING', 'PROCESSING');
        
        -- 暂停所有订阅服务
        UPDATE subscriptions 
        SET status = 'PAUSED', 
            pause_reason = '客户账户已禁用', 
            updated_at = NOW() 
        WHERE customer_id = NEW.customer_id 
        AND status = 'ACTIVE';
        
        -- 记录账户禁用日志
        INSERT INTO account_disable_logs (user_id, disable_reason, disabled_at, disabled_by)
        VALUES (NEW.customer_id, NEW.inactive_reason, NOW(), CURRENT_USER());
    END IF;
    
    -- 如果客户状态变为激活，处理相关业务
    IF OLD.status = 'INACTIVE' AND NEW.status = 'ACTIVE' THEN
        -- 恢复暂停的订阅服务
        UPDATE subscriptions 
        SET status = 'ACTIVE', 
            updated_at = NOW() 
        WHERE customer_id = NEW.customer_id 
        AND status = 'PAUSED' 
        AND pause_reason = '客户账户已禁用';
        
        -- 记录账户激活日志
        INSERT INTO account_activate_logs (user_id, activated_at, activated_by)
        VALUES (NEW.customer_id, NOW(), CURRENT_USER());
    END IF;
    
    -- 同步更新客户档案信息
    UPDATE customer_profiles 
    SET last_name = NEW.last_name, 
        first_name = NEW.first_name, 
        email = NEW.email, 
        phone = NEW.phone, 
        updated_at = NOW() 
    WHERE customer_id = NEW.customer_id;
    
    -- 记录客户更新日志
    INSERT INTO customer_update_logs (customer_id, updated_at, updated_by)
    VALUES (NEW.customer_id, NOW(), CURRENT_USER());
end//
delimiter ;

-- 示例3：在更新库存后执行多个后续操作
delimiter //
CREATE TRIGGER after_inventory_update_complex
AFTER UPDATE ON inventory
FOR EACH ROW
begin
    -- 如果库存低于阈值，创建库存预警
    IF NEW.quantity < 10 AND OLD.quantity >= 10 THEN
        INSERT INTO inventory_alerts (product_id, current_quantity, alert_type, created_at)
        VALUES (NEW.product_id, NEW.quantity, 'LOW_STOCK', NOW());
        
        -- 发送库存预警通知给仓库管理员
        INSERT INTO notifications (user_id, message, created_at, priority, is_read)
        SELECT user_id, CONCAT('产品 ', (SELECT product_name FROM products WHERE product_id = NEW.product_id), ' 库存不足（当前库存：', NEW.quantity, '）'), NOW(), 'HIGH', 0
        FROM users 
        WHERE user_role = 'WAREHOUSE_MANAGER';
    END IF;
    
    -- 如果库存从低于阈值恢复到正常水平，关闭库存预警
    IF NEW.quantity >= 10 AND OLD.quantity < 10 THEN
        UPDATE inventory_alerts 
        SET status = 'RESOLVED', 
            resolved_at = NOW() 
        WHERE product_id = NEW.product_id 
        AND alert_type = 'LOW_STOCK' 
        AND status = 'ACTIVE';
    END IF;
    
    -- 记录库存变更历史
    INSERT INTO inventory_history (product_id, old_quantity, new_quantity, change_type, changed_at, changed_by)
    VALUES (NEW.product_id, OLD.quantity, NEW.quantity, 'UPDATE', NOW(), CURRENT_USER());
    
    -- 更新产品统计信息
    UPDATE product_statistics 
    SET current_stock = NEW.quantity, 
        last_inventory_update = NOW() 
    WHERE product_id = NEW.product_id;
    
    -- 如果库存发生大幅变化（超过50%），发送特殊通知
    IF ABS(NEW.quantity - OLD.quantity) > OLD.quantity * 0.5 THEN
        INSERT INTO admin_notifications (message, created_at, priority)
        VALUES (CONCAT('产品 ', (SELECT product_name FROM products WHERE product_id = NEW.product_id), ' 库存发生大幅变化：从 ', OLD.quantity, ' 变为 ', NEW.quantity), NOW(), 'MEDIUM');
    END IF;
end//
delimiter ;

-- 测试示例：更新订单状态为已付款，触发器会执行多个后续操作
UPDATE orders 
SET status = 'PAID' 
WHERE order_id = 2001;

-- 查看相关表的数据变化，验证触发器是否生效
-- 1. 检查产品库存是否减少
SELECT product_id, stock_quantity FROM products WHERE product_id = (SELECT product_id FROM orders WHERE order_id = 2001);

-- 2. 检查是否创建了发货单
SELECT * FROM shipments WHERE order_id = 2001;

-- 3. 检查是否发送了通知
SELECT * FROM notifications WHERE user_id = (SELECT customer_id FROM orders WHERE order_id = 2001) ORDER BY created_at DESC LIMIT 1;

-- 4. 检查订单更新日志
SELECT * FROM order_update_logs WHERE order_id = 2001 ORDER BY updated_at DESC LIMIT 1;

创建有多个执行语句的BEFORE DELETE触发器

-- 示例1：在删除客户前执行多个验证和清理操作
delimiter //
CREATE TRIGGER before_customer_delete_complex
BEFORE DELETE ON customers
FOR EACH ROW
begin
    -- 禁止删除有未完成订单的客户
    IF EXISTS (SELECT 1 FROM orders WHERE customer_id = OLD.customer_id AND status NOT IN ('COMPLETED', 'CANCELLED')) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该客户有未完成的订单，无法删除';
    END IF;
    
    -- 禁止删除VIP客户
    IF OLD.customer_level = 'VIP' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'VIP客户不能删除，请联系管理员';
    END IF;
    
    -- 创建客户备份记录
    INSERT INTO deleted_customers (customer_id, first_name, last_name, email, phone, registration_date, deleted_at, deleted_by) 
    VALUES (OLD.customer_id, OLD.first_name, OLD.last_name, OLD.email, OLD.phone, OLD.registration_date, NOW(), CURRENT_USER());
    
    -- 备份客户订单历史
    INSERT INTO deleted_customer_orders (order_id, customer_id, order_date, total_amount, status, deleted_at)
    SELECT order_id, customer_id, order_date, total_amount, status, NOW()
    FROM orders 
    WHERE customer_id = OLD.customer_id;
    
    -- 备份客户账户信息
    INSERT INTO deleted_customer_accounts (account_id, customer_id, balance, status, deleted_at)
    SELECT account_id, customer_id, balance, status, NOW()
    FROM customer_accounts 
    WHERE customer_id = OLD.customer_id;
    
    -- 记录客户删除日志
    INSERT INTO customer_deletion_logs (customer_id, deletion_reason, deleted_at, deleted_by)
    VALUES (OLD.customer_id, '用户请求删除', NOW(), CURRENT_USER());
    
    -- 发送客户删除通知给管理员
    INSERT INTO admin_notifications (message, created_at, priority)
    VALUES (CONCAT('客户 ', OLD.first_name, ' ', OLD.last_name, ' (ID:', OLD.customer_id, ') 已被删除'), NOW(), 'MEDIUM');
end//
delimiter ;

-- 示例2：在删除产品前执行多个验证和清理操作
delimiter //
CREATE TRIGGER before_product_delete_complex
BEFORE DELETE ON products
FOR EACH ROW
begin
    -- 禁止删除有库存的产品
    IF OLD.stock_quantity > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该产品仍有库存，无法删除';
    END IF;
    
    -- 禁止删除畅销产品
    IF (SELECT COUNT(*) FROM order_items WHERE product_id = OLD.product_id) > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该产品为畅销产品，无法删除';
    END IF;
    
    -- 创建产品备份记录
    INSERT INTO deleted_products (product_id, product_name, category_id, price, stock_quantity, created_at, deleted_at, deleted_by) 
    VALUES (OLD.product_id, OLD.product_name, OLD.category_id, OLD.price, OLD.stock_quantity, OLD.created_at, NOW(), CURRENT_USER());
    
    -- 备份产品图片
    INSERT INTO deleted_product_images (image_id, product_id, image_url, deleted_at)
    SELECT image_id, product_id, image_url, NOW()
    FROM product_images 
    WHERE product_id = OLD.product_id;
    
    -- 备份产品评论
    INSERT INTO deleted_product_reviews (review_id, product_id, user_id, rating, comment, review_date, deleted_at)
    SELECT review_id, product_id, user_id, rating, comment, review_date, NOW()
    FROM product_reviews 
    WHERE product_id = OLD.product_id;
    
    -- 记录产品删除日志
    INSERT INTO product_deletion_logs (product_id, deletion_reason, deleted_at, deleted_by)
    VALUES (OLD.product_id, '产品已停产', NOW(), CURRENT_USER());
    
    -- 更新产品类别统计
    UPDATE product_categories 
    SET product_count = product_count - 1 
    WHERE category_id = OLD.category_id;
end//
delimiter ;

-- 示例3：在删除项目前执行多个验证和清理操作
delimiter //
CREATE TRIGGER before_project_delete_complex
BEFORE DELETE ON projects
FOR EACH ROW
begin
    -- 禁止删除进行中的项目
    IF OLD.status = 'IN_PROGRESS' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '进行中的项目无法删除';
    END IF;
    
    -- 禁止删除有未完成任务的项目
    IF EXISTS (SELECT 1 FROM tasks WHERE project_id = OLD.project_id AND status <> 'COMPLETED') THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该项目有未完成的任务，无法删除';
    END IF;
    
    -- 创建项目备份记录
    INSERT INTO deleted_projects (project_id, project_name, description, start_date, end_date, status, created_at, deleted_at, deleted_by) 
    VALUES (OLD.project_id, OLD.project_name, OLD.description, OLD.start_date, OLD.end_date, OLD.status, OLD.created_at, NOW(), CURRENT_USER());
    
    -- 备份项目成员
    INSERT INTO deleted_project_members (project_id, user_id, role, joined_date, deleted_at)
    SELECT project_id, user_id, role, joined_date, NOW()
    FROM project_members 
    WHERE project_id = OLD.project_id;
    
    -- 备份项目任务
    INSERT INTO deleted_project_tasks (task_id, project_id, task_name, description, status, assignee_id, due_date, deleted_at)
    SELECT task_id, project_id, task_name, description, status, assignee_id, due_date, NOW()
    FROM tasks 
    WHERE project_id = OLD.project_id;
    
    -- 记录项目删除日志
    INSERT INTO project_deletion_logs (project_id, deletion_reason, deleted_at, deleted_by)
    VALUES (OLD.project_id, '项目已取消', NOW(), CURRENT_USER());
    
    -- 发送项目删除通知给项目经理
    INSERT INTO notifications (user_id, message, created_at, priority)
    SELECT project_manager_id, CONCAT('项目 "', OLD.project_name, '" 已被删除'), NOW(), 'HIGH'
    FROM projects 
    WHERE project_id = OLD.project_id;
end//
delimiter ;

-- 测试示例：尝试删除有未完成订单的客户，触发器会阻止删除
-- DELETE FROM customers WHERE customer_id = 3001;

-- 测试示例：删除没有未完成订单的客户，触发器会执行多个验证和清理操作
-- 首先确保客户没有未完成订单
-- UPDATE orders SET status = 'COMPLETED' WHERE customer_id = 3002;

-- 然后删除客户
-- DELETE FROM customers WHERE customer_id = 3002;

-- 查看相关表的数据变化，验证触发器是否生效
-- 1. 检查是否创建了客户备份记录
-- SELECT * FROM deleted_customers WHERE customer_id = 3002;

-- 2. 检查是否备份了客户订单历史
-- SELECT * FROM deleted_customer_orders WHERE customer_id = 3002;

-- 3. 检查是否备份了客户账户信息
-- SELECT * FROM deleted_customer_accounts WHERE customer_id = 3002;

-- 4. 检查客户删除日志
-- SELECT * FROM customer_deletion_logs WHERE customer_id = 3002;

-- 5. 检查是否发送了管理员通知
-- SELECT * FROM admin_notifications WHERE message LIKE '%3002%' ORDER BY created_at DESC LIMIT 1;

创建有多个执行语句的AFTER DELETE触发器

-- 示例1：在删除订单后执行多个清理和后续操作
delimiter //
CREATE TRIGGER after_order_delete_complex
AFTER DELETE ON orders
FOR EACH ROW
begin
    -- 恢复产品库存
    UPDATE products 
    SET stock_quantity = stock_quantity + OLD.quantity, 
        updated_at = NOW() 
    WHERE product_id = OLD.product_id;
    
    -- 删除订单相关的订单项
    DELETE FROM order_items WHERE order_id = OLD.order_id;
    
    -- 删除订单相关的付款记录
    DELETE FROM payments WHERE order_id = OLD.order_id;
    
    -- 删除订单相关的发货记录
    DELETE FROM shipments WHERE order_id = OLD.order_id;
    
    -- 更新客户的订单统计信息
    UPDATE customers 
    SET order_count = order_count - 1, 
        total_spent = total_spent - OLD.total_amount 
    WHERE customer_id = OLD.customer_id;
    
    -- 从销售统计中减去该订单
    UPDATE sales_statistics 
    SET quantity_sold = quantity_sold - OLD.quantity, 
        revenue = revenue - OLD.total_amount 
    WHERE product_id = OLD.product_id 
    AND sale_date = DATE(OLD.order_date);
    
    -- 记录订单删除日志
    INSERT INTO order_deletion_logs (order_id, customer_id, total_amount, deleted_at, deleted_by)
    VALUES (OLD.order_id, OLD.customer_id, OLD.total_amount, NOW(), CURRENT_USER());
    
    -- 发送订单删除通知给客户
    INSERT INTO notifications (user_id, message, created_at, is_read)
    VALUES (OLD.customer_id, CONCAT('您的订单 #', OLD.order_id, ' 已被删除'), NOW(), 0);
end//
delimiter ;

-- 示例2：在删除员工后执行多个清理和后续操作
delimiter //
CREATE TRIGGER after_employee_delete_complex
AFTER DELETE ON employees
FOR EACH ROW
begin
    -- 删除员工相关的档案
    DELETE FROM employee_profiles WHERE employee_id = OLD.employee_id;
    
    -- 删除员工相关的工资记录
    DELETE FROM employee_salary WHERE employee_id = OLD.employee_id;
    
    -- 删除员工相关的培训记录
    DELETE FROM employee_training WHERE employee_id = OLD.employee_id;
    
    -- 删除员工相关的考勤记录
    DELETE FROM attendance_records WHERE employee_id = OLD.employee_id;
    
    -- 删除员工相关的绩效记录
    DELETE FROM performance_reviews WHERE employee_id = OLD.employee_id;
    
    -- 更新部门的员工数量
    UPDATE departments 
    SET employee_count = employee_count - 1 
    WHERE department_id = OLD.department_id;
    
    -- 记录员工删除日志
    INSERT INTO employee_deletion_logs (employee_id, first_name, last_name, department_id, deleted_at, deleted_by)
    VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, OLD.department_id, NOW(), CURRENT_USER());
    
    -- 发送员工删除通知给部门经理
    INSERT INTO notifications (user_id, message, created_at, priority)
    SELECT manager_id, CONCAT('员工 ', OLD.first_name, ' ', OLD.last_name, ' (ID:', OLD.employee_id, ') 已被删除'), NOW(), 'MEDIUM'
    FROM departments 
    WHERE department_id = OLD.department_id;
end//
delimiter ;

-- 示例3：在删除产品后执行多个清理和后续操作
delimiter //
CREATE TRIGGER after_product_delete_complex
AFTER DELETE ON products
FOR EACH ROW
begin
    -- 删除产品相关的图片
    DELETE FROM product_images WHERE product_id = OLD.product_id;
    
    -- 删除产品相关的评论
    DELETE FROM product_reviews WHERE product_id = OLD.product_id;
    
    -- 删除产品相关的标签
    DELETE FROM product_tags WHERE product_id = OLD.product_id;
    
    -- 删除产品相关的库存记录
    DELETE FROM inventory WHERE product_id = OLD.product_id;
    
    -- 删除产品相关的价格历史
    DELETE FROM product_price_history WHERE product_id = OLD.product_id;
    
    -- 更新产品类别的产品数量
    UPDATE product_categories 
    SET product_count = product_count - 1 
    WHERE category_id = OLD.category_id;
    
    -- 记录产品删除日志
    INSERT INTO product_deletion_logs (product_id, product_name, category_id, deleted_at, deleted_by)
    VALUES (OLD.product_id, OLD.product_name, OLD.category_id, NOW(), CURRENT_USER());
    
    -- 发送产品删除通知给管理员
    INSERT INTO admin_notifications (message, created_at, priority)
    VALUES (CONCAT('产品 "', OLD.product_name, '" (ID:', OLD.product_id, ') 已被删除'), NOW(), 'LOW');
end//
delimiter ;

-- 测试示例：删除订单，触发器会执行多个清理和后续操作
-- DELETE FROM orders WHERE order_id = 2001;

-- 查看相关表的数据变化，验证触发器是否生效
-- 1. 检查产品库存是否恢复
-- SELECT product_id, stock_quantity FROM products WHERE product_id = (SELECT product_id FROM deleted_orders WHERE order_id = 2001);

-- 2. 检查订单项是否被删除
-- SELECT COUNT(*) FROM order_items WHERE order_id = 2001;

-- 3. 检查客户的订单统计是否更新
-- SELECT customer_id, order_count, total_spent FROM customers WHERE customer_id = (SELECT customer_id FROM deleted_orders WHERE order_id = 2001);

-- 4. 检查订单删除日志
-- SELECT * FROM order_deletion_logs WHERE order_id = 2001;

-- 5. 检查是否发送了客户通知
-- SELECT * FROM notifications WHERE user_id = (SELECT customer_id FROM deleted_orders WHERE order_id = 2001) ORDER BY created_at DESC LIMIT 1;

分隔符的使用

在MySQL中，默认的语句分隔符是分号(;)。当创建包含多条SQL语句的触发器时，需要使用DELIMITER语句更改分隔符，以便MySQL能够正确解析触发器定义中的多条语句。

通常的做法是在创建触发器前使用DELIMITER语句将分隔符更改为//或$$，然后在触发器定义结束后再将分隔符改回分号(;)。

-- 更改分隔符为//
delimiter //

-- 创建包含多条语句的触发器
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
begin
    -- 多条SQL语句
    -- 第一条语句;
    -- 第二条语句;
    -- ...
end//

-- 将分隔符改回;
delimiter ;

创建触发器的最佳实践

以下是创建和使用触发器的一些最佳实践：

命名规范：使用有意义的名称，并遵循一致的命名约定，如[BEFORE|AFTER]_[TABLE_NAME]_[INSERT|UPDATE|DELETE]。
简洁性：触发器应该尽可能简洁，只包含必要的逻辑。复杂的业务逻辑应该放在存储过程中。
事务控制：触发器会成为触发它的语句的事务的一部分，因此不要在触发器中使用COMMIT或ROLLBACK语句。
性能考虑：避免在触发器中执行耗时的操作，如大量数据的查询或网络操作。
错误处理：使用SIGNAL语句在触发器中抛出有意义的错误信息。
可维护性：为触发器添加适当的注释，说明其目的和工作原理。
文档化：记录所有触发器的用途、触发条件和执行逻辑，以便其他开发人员理解。

触发器的限制和注意事项

在使用触发器时，需要注意以下限制和事项：

每个表最多可以有6个触发器（BEFORE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE、BEFORE DELETE、AFTER DELETE）。
触发器不能返回结果集。
触发器不能使用CALL语句调用存储过程，但可以调用不返回结果集的函数。
在MySQL 5.7.2及更高版本中，触发器可以使用SIGNAL语句抛出异常。
触发器中的OLD和NEW关键字不能用于VIEW。
对于分区表，触发器对每个分区分别执行。
如果表被删除，与该表相关的所有触发器也会被自动删除。

实际应用案例

数据审计和日志记录

触发器可以用于自动记录数据变更的历史，以便进行审计和追踪。

-- 创建审计日志表
delimiter //
CREATE TABLE user_audit_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(20),
    old_value TEXT,
    new_value TEXT,
    changed_by VARCHAR(50),
    changed_at TIMESTAMP
);
//

-- 创建触发器记录用户数据变更
delimiter //
CREATE TRIGGER after_user_update_audit
AFTER UPDATE ON users
FOR EACH ROW
begin
    INSERT INTO user_audit_log (user_id, action, old_value, new_value, changed_by, changed_at)
    VALUES (
        NEW.user_id,
        'UPDATE',
        CONCAT('name=', OLD.name, ', email=', OLD.email, ', role=', OLD.role),
        CONCAT('name=', NEW.name, ', email=', NEW.email, ', role=', NEW.role),
        CURRENT_USER(),
        NOW()
    );
end//
delimiter ;

数据完整性和一致性维护

触发器可以用于维护数据的完整性和一致性，特别是在复杂的业务场景中。

-- 创建触发器维护账户余额的一致性
delimiter //
CREATE TRIGGER before_transaction_insert
BEFORE INSERT ON transactions
FOR EACH ROW
begin
    -- 检查账户余额是否足够
    IF NEW.transaction_type = 'DEBIT' THEN
        IF (SELECT balance FROM accounts WHERE account_id = NEW.account_id) < NEW.amount THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '账户余额不足';
        END IF;
    END IF;
    
    -- 自动更新账户余额
    IF NEW.transaction_type = 'DEBIT' THEN
        UPDATE accounts SET balance = balance - NEW.amount WHERE account_id = NEW.account_id;
    ELSE
        UPDATE accounts SET balance = balance + NEW.amount WHERE account_id = NEW.account_id;
    END IF;
end//
delimiter ;

自动生成派生数据

触发器可以用于自动计算和生成派生数据，减少应用程序的复杂性。

-- 创建触发器自动计算订单总额
delimiter //
CREATE TRIGGER before_order_insert
BEFORE INSERT ON orders
FOR EACH ROW
begin
    -- 计算订单总额
    DECLARE total DECIMAL(10, 2);
    
    -- 从订单项表中计算总额
    SELECT SUM(quantity * price) INTO total
    FROM order_items
    WHERE order_id = NEW.order_id;
    
    -- 设置订单总额
    SET NEW.total_amount = total;
    
    -- 计算税额（假设税率为10%）
    SET NEW.tax_amount = total * 0.10;
    
    -- 计算最终应付金额
    SET NEW.grand_total = total + NEW.tax_amount;
end//
delimiter ;

创建触发器的常见问题和解决方案

问题	原因	解决方案
触发器创建后不执行	可能是触发条件不匹配或权限问题	检查触发事件类型是否正确，确保用户有执行触发器的权限
触发器执行报错	触发器中的SQL语句有语法错误或逻辑错误	检查触发器中的SQL语句，使用SIGNAL语句提供有意义的错误信息
性能问题	触发器中包含复杂或耗时的操作	简化触发器逻辑，将复杂操作移至存储过程或应用程序中
死锁	触发器与触发它的语句在同一事务中，可能导致死锁	避免在触发器中执行可能导致死锁的操作，如长时间持有锁
递归触发	触发器修改了另一个表，而该表又有触发器触发当前表的操作	重新设计触发器逻辑，避免循环触发，或使用标志字段控制递归

上一章：MySQL触发器下一章：查看触发器