SQL触发器

SQL 触发器 (Trigger)

触发器是存储在数据库中的特殊类型的存储过程，它会在指定的表或视图上发生特定事件（如INSERT、UPDATE或DELETE）时自动执行。触发器常用于维护数据完整性、实施业务规则或自动执行相关操作。

触发器的基本语法

创建触发器

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW | FOR EACH STATEMENT]
BEGIN
    -- 触发器逻辑
END;

删除触发器

DROP TRIGGER IF EXISTS trigger_name;

触发器类型

1. 按执行时机分类

• BEFORE 触发器：在事件执行前触发，可用于验证或修改即将插入/更新的数据
• AFTER 触发器：在事件执行后触发，可用于执行后续操作或审计

2. 按事件类型分类

• INSERT 触发器：在插入新记录时触发
• UPDATE 触发器：在更新记录时触发
• DELETE 触发器：在删除记录时触发

3. 按作用行数分类

• 行级触发器 (FOR EACH ROW)：对受影响的每一行数据执行一次
• 语句级触发器 (FOR EACH STATEMENT)：对每个SQL语句执行一次（无论影响多少行）

触发器示例

示例1：BEFORE INSERT 触发器（数据验证）

CREATE TRIGGER validate_employee_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.hire_date > CURRENT_DATE() THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Hire date cannot be in the future';
    END IF;
END;

示例2：AFTER INSERT 触发器（自动创建关联记录）

CREATE TRIGGER create_default_salary
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salaries (emp_no, salary, from_date, to_date)
    VALUES (NEW.emp_no, 50000, NEW.hire_date, '9999-01-01');
END;

示例3：BEFORE UPDATE 触发器（审计日志）

CREATE TRIGGER log_salary_changes
BEFORE UPDATE ON salaries
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (emp_no, old_salary, new_salary, change_date)
        VALUES (NEW.emp_no, OLD.salary, NEW.salary, CURRENT_DATE());
    END IF;
END;

示例4：AFTER DELETE 触发器（级联删除）

CREATE TRIGGER cleanup_after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    DELETE FROM dept_emp WHERE emp_no = OLD.emp_no;
    DELETE FROM salaries WHERE emp_no = OLD.emp_no;
    DELETE FROM titles WHERE emp_no = OLD.emp_no;
END;

特殊变量

在行级触发器中，可以使用以下特殊变量：

• NEW：引用即将插入或更新后的新行
• OLD：引用更新前或删除前的旧行

触发器使用场景

1. 数据验证：确保数据符合业务规则（如示例1）
2. 自动计算：自动计算派生字段的值
3. 审计跟踪：记录数据变更历史（如示例3）
4. 级联操作：自动维护相关表的数据一致性（如示例4）
5. 复制数据：将数据变更复制到其他表或系统
6. 防止非法操作：阻止某些删除或更新操作

不同数据库的触发器语法差异

特性	MySQL/MariaDB	SQL Server	Oracle	PostgreSQL
语句级触发器	不支持	支持	支持	支持
触发器内事务控制	有限	支持	支持	不支持
多个事件触发器	单个事件	多个事件	多个事件	多个事件
触发器执行顺序	有限控制	完全控制	完全控制	有限控制

最佳实践

1. 保持简单：触发器逻辑应尽可能简单，复杂的业务逻辑应放在应用层
2. 避免递归：注意触发器不要导致无限递归调用
3. 考虑性能：触发器会对所有符合条件的操作执行，可能影响性能
4. 文档记录：记录触发器的存在和用途，因为它们是"隐藏"的逻辑
5. 测试充分：触发器错误可能导致主操作失败，需全面测试
6. 避免在触发器中调用存储过程：可能导致维护困难和性能问题

触发器与存储过程的比较

特性	触发器	存储过程
调用方式	自动执行	显式调用
参数	有限（通常使用NEW/OLD）	可定义多个参数
返回值	无	可通过OUT参数返回值
事务控制	与主语句同一事务	可包含独立事务控制
使用场景	数据完整性、自动操作	业务逻辑封装

触发器是维护数据库完整性和自动执行相关操作的强大工具，但应谨慎使用以避免意外的副作用。