【MySQL】提高篇—视图与存储过程：使用触发器（Triggers）进行自动化操作
在关系数据库中，触发器（Trigger）是一种特殊的数据库对象，它是在特定事件发生时自动执行的程序。触发器可以在插入、更新或删除操作之前或之后自动运行，从而实现自动化的数据处理和业务逻辑。
触发器在实际应用中具有重要性，主要体现在以下几个方面：
-
自动化操作：触发器可以在特定事件发生时自动执行预定义的操作，减少手动干预的需要。
-
数据完整性：通过触发器，可以确保数据的一致性和完整性。例如，在删除记录时，可以自动更新相关表的数据。
-
审计与日志：触发器可以用于记录数据变更的历史，帮助进行审计和追踪。
-
业务逻辑实现：触发器可以封装复杂的业务逻辑，确保在数据变更时自动执行相关操作。
触发器的创建与使用
触发器的创建使用 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表中的所有记录，新的员工记录不会被插入。
总结
通过示例能够理解触发器的创建与使用方法：
-
触发器的定义：触发器是在特定事件发生时自动执行的程序。
-
创建触发器：使用
CREATE TRIGGER语句定义触发器，并指定触发条件和操作。 -
使用触发器记录变更：触发器可以用于自动记录数据变更，例如薪水变化的审计。
-
使用触发器限制数据：触发器可以用于确保数据符合特定规则，例如薪水不能低于某个值。
-
删除触发器：使用
DROP TRIGGER命令删除不再需要的触发器。