PostgreSQL 触发器自动化

介绍

在这个实验中，你将学习如何使用触发器在 PostgreSQL 中自动化任务。本实验的重点是使用 PL/pgSQL 创建一个触发器函数，以自动将 employees 表中的薪资更改记录到单独的 salary_changes 表中。

你将获得指导，完成编写触发器函数、将其绑定到表事件（特别是 employees 表上的更新）、测试触发器的行为，以及最后禁用和删除触发器的过程。

Skills Graph

创建表和触发器函数

在此步骤中，你将创建 employees 表和 salary_changes 表，然后使用 PL/pgSQL 定义 log_salary_change 函数。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库：

sudo -u postgres psql

现在，创建 employees 表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2)
);

此命令创建一个包含 id（主键）、name 和 salary 列的表。

接下来，创建 salary_changes 表以存储薪资更改的历史记录：

CREATE TABLE salary_changes (
    id SERIAL PRIMARY KEY,
    employee_id INTEGER,
    old_salary DECIMAL(10, 2),
    new_salary DECIMAL(10, 2),
    change_date TIMESTAMP
);

现在，让我们使用 PL/pgSQL 编写触发器函数。此函数将在 employees 表上执行更新操作之前执行。它会将旧的和新的薪资值记录在 salary_changes 表中。

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes (employee_id, old_salary, new_salary, change_date)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

这段代码定义了一个名为 log_salary_change 的函数。它检查薪资是否已更改。如果已更改，它会在 salary_changes 表中插入一个新行，其中包含员工 ID、旧薪资、新薪资和当前时间戳（timestamp）。

创建触发器

在此步骤中，你将 log_salary_change 函数绑定到 employees 表，以便在发生薪资更新时自动执行该函数。

使用以下 SQL 命令创建触发器：

CREATE TRIGGER salary_change_trigger
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

此命令创建一个名为 salary_change_trigger 的触发器。它指定触发器应在 employees 表上的 UPDATE 操作之前激活。 FOR EACH ROW 子句指示应该为每个更新的行执行触发器函数。

测试触发器

在此步骤中，你将测试触发器的行为。你将数据插入到 employees 表中，然后更新薪资，以查看触发器是否正确地将更改记录在 salary_changes 表中。

首先，将一些初始数据插入到 employees 表中：

INSERT INTO employees (name, salary) VALUES ('Alice', 50000.00);
INSERT INTO employees (name, salary) VALUES ('Bob', 60000.00);

现在，更新 Alice 的薪资：

UPDATE employees SET salary = 55000.00 WHERE name = 'Alice';

要验证触发器是否正常工作，请查询 salary_changes 表：

SELECT * FROM salary_changes;

你应该在 salary_changes 表中看到一行，其中包含有关 Alice 薪资更改的信息。

接下来，将 Bob 的薪资更新为与其当前值相同的值：

UPDATE employees SET salary = 60000.00 WHERE name = 'Bob';

由于薪资实际上没有改变，因此触发器不应在 salary_changes 表中插入新行。让我们验证一下：

SELECT * FROM salary_changes;

你应该仍然只看到与 Alice 的薪资更改相关的一行。

最后，插入一个新员工：

INSERT INTO employees (name, salary) VALUES ('Charlie', 70000.00);

插入新员工不应触发 log_salary_change 触发器，因为它仅设置为在 UPDATE 事件上触发。让我们通过再次检查 salary_changes 表来确认这一点：

SELECT * FROM salary_changes;

你应该仍然只看到与 Alice 的薪资更改相关的一行。

禁用和删除触发器

在此步骤中，你将学习如何在 PostgreSQL 中禁用和删除触发器。

首先，禁用 salary_change_trigger：

ALTER TABLE employees DISABLE TRIGGER salary_change_trigger;

现在，再次更新 Alice 的薪资：

UPDATE employees SET salary = 60000.00 WHERE name = 'Alice';

检查 salary_changes 表：

SELECT * FROM salary_changes;

你应该仍然只看到之前更新的一行。这证实了触发器已被禁用。

要重新启用触发器，你可以使用以下命令：

ALTER TABLE employees ENABLE TRIGGER salary_change_trigger;

最后，要永久删除触发器，请使用以下命令删除它：

DROP TRIGGER salary_change_trigger ON employees;

总结

在这个实验中，你学习了如何使用触发器在 PostgreSQL 中自动化任务。你使用 PL/pgSQL 创建了一个触发器函数，用于将 employees 表中的薪资更改记录到 salary_changes 表中。你编写了函数，将其绑定到表事件，对其进行了测试，并禁用了/删除了它。