MySQL触发器 - 当发网

什么是触发器？

触发器是数据库中的一种特殊存储过程，它在特定事件（INSERT、UPDATE、DELETE）发生时自动执行。触发器与表相关联，当表发生数据变更时自动触发。

触发器的特点

自动执行，无需手动调用
与特定表关联
在数据变更前或后执行
可以访问变更前后的数据
用于实现数据完整性、审计、日志记录等

创建触发器

基本语法

                        SQL示例
                    

                        CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
BEGIN
    -- 触发器逻辑
END;
                    

触发时间

BEFORE - 在事件执行前触发
AFTER - 在事件执行后触发

触发事件

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

触发器示例

BEFORE INSERT 触发器

                        SQL示例
                    

                        CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
    SET NEW.updated_at = NOW();
END;
                    

AFTER INSERT 触发器

                        SQL示例
                    

                        CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, table_name, record_id, changed_at)
    VALUES ('INSERT', 'employees', NEW.employee_id, NOW());
END;
                    

BEFORE UPDATE 触发器

                        SQL示例
                    

                        CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    SET NEW.updated_at = NOW();
    
    -- 记录薪资变更
    IF NEW.salary != OLD.salary THEN
        INSERT INTO salary_history (employee_id, old_salary, new_salary, changed_at)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
    END IF;
END;
                    

AFTER DELETE 触发器

                        SQL示例
                    

                        CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees FOR EACH ROW
BEGIN
    INSERT INTO deleted_employees (employee_id, first_name, last_name, deleted_at)
    VALUES (OLD.employee_id, OLD.first_name, OLD.last_name, NOW());
END;
                    

OLD和NEW关键字

在触发器中，可以使用OLD和NEW关键字来访问变更前后的数据：

触发器类型	OLD	NEW
INSERT	NULL	要插入的新数据
UPDATE	更新前的旧数据	更新后的新数据
DELETE	删除前的旧数据	NULL

使用OLD和NEW的示例

                        SQL示例
                    

                        CREATE TRIGGER prevent_salary_decrease
BEFORE UPDATE ON employees FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '薪资不能降低';
    END IF;
END;
                    

多个触发器的执行顺序

MySQL 5.7+允许在同一表、同一事件上定义多个触发器，使用FOLLOWS/PRECEDES指定顺序：

                        SQL示例
                    
                        CREATE TRIGGER trigger1 BEFORE INSERT ON t FOR EACH ROW ...
CREATE TRIGGER trigger2 BEFORE INSERT ON t FOR EACH ROW FOLLOWS trigger1 ...

触发器中调用存储过程和函数

                        SQL示例
                    

                        CREATE TRIGGER call_proc_example
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    CALL update_summary(NEW.product_id, NEW.quantity);
END;
                    

管理触发器

查看触发器

                        SQL示例
                    
                        -- 查看所有触发器
SHOW TRIGGERS;

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

删除触发器

                        SQL示例
                    
                        DROP TRIGGER IF EXISTS trigger_name;

触发器的应用场景

数据完整性

                        SQL示例
                    

                        CREATE TRIGGER validate_email_format
BEFORE INSERT ON users FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '邮箱格式不正确';
    END IF;
END;
                    

审计日志

                        SQL示例
                    

                        CREATE TRIGGER audit_customer_changes
AFTER UPDATE ON customers FOR EACH ROW
BEGIN
    IF NEW.name != OLD.name THEN
        INSERT INTO customer_audit (customer_id, field_name, old_value, new_value, changed_at)
        VALUES (NEW.customer_id, 'name', OLD.name, NEW.name, NOW());
    END IF;
    
    IF NEW.email != OLD.email THEN
        INSERT INTO customer_audit (customer_id, field_name, old_value, new_value, changed_at)
        VALUES (NEW.customer_id, 'email', OLD.email, NEW.email, NOW());
    END IF;
END;
                    

数据同步

                        SQL示例
                    

                        CREATE TRIGGER sync_inventory
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    UPDATE products 
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE product_id = NEW.product_id;
END;
                    

自动生成业务编号

                        SQL示例
                    

                        CREATE TRIGGER generate_order_number
BEFORE INSERT ON orders FOR EACH ROW
BEGIN
    SET NEW.order_number = CONCAT('ORD-', DATE_FORMAT(NOW(), '%Y%m%d'), LPAD(NEW.id, 5, '0'));
END;
                    

触发器的限制和注意事项

触发器不能对已触发表进行修改，可能导致递归（除非设置合理的逻辑）
触发器中的错误会导致整个事务回滚
触发器不能返回结果集（不能使用SELECT ... INTO）
不能使用事务显式控制语句（COMMIT/ROLLBACK）
触发器内的语句不直接受自动提交影响，而是随触发事务提交或回滚

触发器与复制

在主从复制环境中，触发器行为取决于设置：

通常在从库上会执行触发器（除非设置replicate_wild_do_table等）
可能导致主从数据不一致，需要谨慎设计
建议在复制环境中测试触发器的行为

触发器元数据查询与监控

                        SQL示例
                    

                        -- 查询所有触发器及详细信息
SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_TIMING
FROM information_schema.TRIGGERS
WHERE TRIGGER_SCHEMA = 'your_database';
                    

触发器性能考虑

使用触发器时需要考虑以下性能因素：

触发器会增加数据库负载
复杂的触发器逻辑可能影响性能
触发器中的错误可能导致事务回滚
避免在触发器中执行耗时操作
批量操作时，触发器每行执行一次，效率较低

触发器最佳实践

保持触发器逻辑简单
避免在触发器中执行复杂查询
注意触发器的执行顺序
测试触发器在各种情况下的行为
记录触发器的用途和逻辑
尽量避免使用递归触发器（除非必要且可控）
定期审查触发器是否仍然必要

注意事项：触发器虽然功能强大，但过度使用会影响数据库性能。建议只在必要时使用触发器，并确保触发器逻辑简单高效。

下一步学习

掌握了触发器知识后，您可以继续学习：

性能优化 - 学习更多数据库性能优化技巧
安全管理 - 了解MySQL安全管理和权限控制
备份与恢复 - 学习数据库备份和恢复策略