数据库触发器

1. 触发器基础概念

定义

• 触发器是与表关联的数据库对象，当表发生特定事件（INSERT、UPDATE、DELETE）时自动执行。
• 用于实现数据完整性约束、自动审计、业务规则等。

关键特性

2. 触发器典型应用场景

1. 数据完整性约束

   • 禁止删除关键数据（如删除前检查条件）
   • 自动计算字段值（如插入时自动填充时间戳）

2. 审计日志

   • 记录数据变更历史（如谁在何时修改了哪些数据）

3. 级联操作

   • 自动更新关联表（如订单状态变更时同步更新库存）

4. 业务规则强制

   • 限制数据修改范围（如薪资调整幅度不能超过20%）

3. 触发器案例

案例1：自动记录审计日志

-- 创建日志表
CREATE TABLE employee_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    modified_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建触发器（在薪资更新后记录日志）
DELIMITER $$
CREATE TRIGGER trg_audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary != NEW.salary THEN
        INSERT INTO employee_audit (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END$$
DELIMITER ;

-- 测试：更新薪资触发日志记录
UPDATE employees SET salary = 6000 WHERE id = 1;

案例2：禁止删除关键数据

-- 创建触发器（阻止删除在职员工）
DELIMITER $$
CREATE TRIGGER trg_block_employee_delete
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
    IF OLD.status = 'active' THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '不能删除在职员工';
    END IF;
END$$
DELIMITER ;

-- 测试：尝试删除在职员工会报错
DELETE FROM employees WHERE id = 1;

案例3：自动更新关联表

-- 创建订单表和库存表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT
);

CREATE TABLE inventory (
    product_id INT PRIMARY KEY,
    stock INT
);

-- 插入初始库存
INSERT INTO inventory VALUES (1, 100);

-- 创建触发器（订单创建后自动扣减库存）
DELIMITER $$
CREATE TRIGGER trg_update_inventory
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    UPDATE inventory 
    SET stock = stock - NEW.quantity 
    WHERE product_id = NEW.product_id;
END$$
DELIMITER ;

-- 测试：创建订单触发库存更新
INSERT INTO orders VALUES (101, 1, 5);

案例4：数据验证（限制薪资调整幅度）

DELIMITER $$
CREATE TRIGGER trg_validate_salary
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary > OLD.salary * 1.2 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '薪资调整不能超过20%';
    END IF;
END$$
DELIMITER ;

-- 测试：尝试大幅调整薪资会失败
UPDATE employees SET salary = 10000 WHERE id = 1; -- 假设原薪资为5000

4. 触发器管理命令

5. 注意事项

1. 性能影响：复杂触发器可能导致数据操作变慢，避免在触发器中执行耗时操作。
2. 递归触发：触发器内部的操作可能再次触发自身，需谨慎设计逻辑。
3. 兼容性：不同数据库（MySQL、Oracle、SQL Server）的触发器语法略有差异。
4. 调试难度：触发器自动执行，调试时需结合日志和错误信息排查问题。

6. 触发器 vs 存储过程