【MySQL】提高篇—视图与存储过程：使用触发器（Triggers）进行自动化操作

在关系数据库中，触发器（Trigger）是一种特殊的数据库对象，它是在特定事件发生时自动执行的程序。触发器可以在插入、更新或删除操作之前或之后自动运行，从而实现自动化的数据处理和业务逻辑。

触发器在实际应用中具有重要性，主要体现在以下几个方面：

1. 自动化操作：触发器可以在特定事件发生时自动执行预定义的操作，减少手动干预的需要。

2. 数据完整性：通过触发器，可以确保数据的一致性和完整性。例如，在删除记录时，可以自动更新相关表的数据。

3. 审计与日志：触发器可以用于记录数据变更的历史，帮助进行审计和追踪。

4. 业务逻辑实现：触发器可以封装复杂的业务逻辑，确保在数据变更时自动执行相关操作。

触发器的创建与使用

触发器的创建使用 CREATE TRIGGER 语句，基本语法如下：

CREATE TRIGGER trigger_name
AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

示例

假设我们有一个简单的员工表 employees，其结构如下：

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

我们可以插入一些示例数据：

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Engineering', 75000),
(2, 'Jane', 'Smith', 'Marketing', 60000),
(3, 'Alice', 'Johnson', 'Engineering', 80000),
(4, 'Bob', 'Brown', 'Sales', 55000);

创建触发器

示例 1：创建一个触发器以记录员工薪水的变化

我们希望创建一个触发器，当员工的薪水被更新时，自动记录薪水的变化到一个名为 salary_changes 的审计表中。

首先，我们需要创建一个审计表 salary_changes：

CREATE TABLE salary_changes (
    change_id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date DATETIME DEFAULT CURRENT_TIMESTAMP
);

解释：

• salary_changes 表用于存储薪水变化的记录，包括员工 ID、旧薪水、新薪水和变更日期。

接下来，我们创建触发器：

-- 创建触发器
DELIMITER //
CREATE TRIGGER before_salary_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    -- 插入薪水变化记录到审计表
    INSERT INTO salary_changes (employee_id, old_salary, new_salary)
    VALUES (OLD.employee_id, OLD.salary, NEW.salary);
END //
DELIMITER ;

解释：

• DELIMITER //：改变语句结束符，避免在触发器中出现的分号被误认为是语句结束。

• CREATE TRIGGER before_salary_update：定义一个名为 before_salary_update 的触发器，在 employees 表的更新操作之前触发。

• FOR EACH ROW：表示触发器对每一行的更新操作都会执行。

• INSERT INTO salary_changes ...：将旧薪水和新薪水记录插入到 salary_changes 表中。OLD 表示更新前的值，NEW 表示更新后的值。

更新员工薪水

现在，我们可以更新员工的薪水，触发器将自动记录薪水的变化。

-- 更新员工薪水
UPDATE employees
SET salary = 82000
WHERE employee_id = 3;

解释：

• 这个更新操作将员工 ID 为 3 的员工（即 Alice Johnson）的薪水更新为 82000。触发器将自动记录这次薪水的变化。

查询审计表

我们可以查询 salary_changes 表来验证薪水变化是否成功记录。

-- 查询薪水变化记录
SELECT * FROM salary_changes;

解释：

• 这个查询将返回 salary_changes 表中的所有记录，包括刚刚插入的薪水变化记录。

删除触发器

如果不再需要某个触发器，可以使用 DROP TRIGGER 命令删除它。

-- 删除触发器
DROP TRIGGER before_salary_update;

解释：

• 这个命令将删除名为 before_salary_update 的触发器，触发器定义将不再存在。

示例 2：创建触发器以限制员工的薪水

我们还可以创建一个触发器，以确保员工的薪水不会低于某个最低标准。例如，我们希望员工的薪水不能低于 50000。

-- 创建触发器以限制薪水
DELIMITER //
CREATE TRIGGER check_salary_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < 50000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be less than 50000';
    END IF;
END //
DELIMITER ;

解释：

• CREATE TRIGGER check_salary_before_insert：定义一个名为 check_salary_before_insert 的触发器，在 employees 表的插入操作之前触发。

• IF NEW.salary < 50000 THEN：检查即将插入的新薪水是否低于 50000。

• SIGNAL SQLSTATE '45000'：如果薪水低于 50000，则抛出一个错误，阻止插入操作，并显示相应的错误信息。

尝试插入不合规的薪水

我们可以尝试插入一个薪水低于 50000 的员工，触发器将阻止该操作。

-- 尝试插入薪水低于50000的员工
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (5, 'Charlie', 'Davis', 'HR', 45000);

解释：

• 这个插入操作将失败，因为薪水低于 50000，触发器会抛出错误。

查询员工表

我们可以查询 employees 表来验证插入操作是否成功。

-- 查询员工表
SELECT * FROM employees;

解释：

• 这个查询将返回 employees 表中的所有记录，新的员工记录不会被插入。

总结

通过示例能够理解触发器的创建与使用方法：

1. 触发器的定义：触发器是在特定事件发生时自动执行的程序。

2. 创建触发器：使用 CREATE TRIGGER 语句定义触发器，并指定触发条件和操作。

3. 使用触发器记录变更：触发器可以用于自动记录数据变更，例如薪水变化的审计。

4. 使用触发器限制数据：触发器可以用于确保数据符合特定规则，例如薪水不能低于某个值。

5. 删除触发器：使用 DROP TRIGGER 命令删除不再需要的触发器。