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;

管理触发器

查看触发器

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;

触发器性能考虑

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

  • 触发器会增加数据库负载
  • 复杂的触发器逻辑可能影响性能
  • 触发器中的错误可能导致事务回滚
  • 避免在触发器中执行耗时操作

触发器最佳实践

  • 保持触发器逻辑简单
  • 避免在触发器中执行复杂查询
  • 注意触发器的执行顺序
  • 测试触发器在各种情况下的行为
  • 记录触发器的用途和逻辑
注意事项：触发器虽然功能强大，但过度使用会影响数据库性能。建议只在必要时使用触发器，并确保触发器逻辑简单高效。

下一步学习

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