PostgreSQL - 触发器

PostgreSQL - 触发器 (Triggers)

触发器（Trigger）是一种特殊类型的存储函数，当表或视图上发生特定数据库事件（如 INSERT、UPDATE、DELETE 或 TRUNCATE）时，它会自动执行。

触发器的核心概念

• 事件时机：触发器可以配置为在指定事件 BEFORE（之前）、AFTER（之后）或 INSTEAD OF（取代）触发。
• 执行级别：触发器可以 FOR EACH ROW（对操作影响的每一行）触发，或 FOR EACH STATEMENT（对每个语句触发一次，无论修改了多少行）触发。
• 触发器函数：触发器的逻辑在一个单独的函数中定义，通常用 PL/pgSQL 编写，该函数必须返回 trigger 类型的值。
• 特殊变量：在行级触发器函数中，您可以使用 OLD 和 NEW 记录分别访问行的旧数据和新数据（NEW 用于 INSERT/UPDATE，OLD 用于 UPDATE/DELETE）。

语法

创建触发器是一个两步过程：首先，定义触发器函数；其次，将其绑定到表。

1. 定义触发器函数

CREATE OR REPLACE FUNCTION function_name()
RETURNS TRIGGER AS $$
BEGIN
    -- 触发器逻辑在此处编写
    RETURN NEW; -- 根据逻辑返回 NEW、OLD 或 NULL
END;
$$ LANGUAGE plpgsql;

2. 创建触发器

CREATE TRIGGER trigger_name
{ BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FOR EACH { ROW | STATEMENT } ]
EXECUTE FUNCTION function_name();

示例：审计表更改

触发器的一个经典用例是创建审计跟踪。让我们创建一个触发器，记录 employees 表的每次薪资更新。

步骤 1：设置表

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    salary NUMERIC(10, 2) NOT NULL
);

CREATE TABLE salary_audits (
    audit_id SERIAL PRIMARY KEY,
    employee_id INT NOT NULL,
    old_salary NUMERIC(10, 2),
    new_salary NUMERIC(10, 2),
    changed_by TEXT DEFAULT current_user,
    changed_at TIMESTAMPTZ DEFAULT now()
);

步骤 2：创建触发器函数

如果薪资发生变化，此函数将向 salary_audits 插入一条记录。

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查 UPDATE 操作是否确实更改了薪资
    -- TG_OP 是一个特殊变量，用于保存操作类型（'UPDATE'、'INSERT' 等）
    IF TG_OP = 'UPDATE' AND NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audits(employee_id, old_salary, new_salary)
        VALUES(OLD.id, OLD.salary, NEW.salary);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤 3：创建触发器

现在，我们将此函数绑定到 employees 表，以便在更新后触发。

CREATE TRIGGER employees_salary_update_trigger
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

步骤 4：测试触发器

-- 首先，插入一个示例员工
INSERT INTO employees (name, salary) VALUES ('Alice', 60000.00);

-- 现在，更新薪资
UPDATE employees SET salary = 65000.00 WHERE name = 'Alice';

-- 检查审计日志
SELECT * FROM salary_audits;

SELECT 查询将在 salary_audits 中显示一条新行，记录从 60000.00 到 65000.00 的更改。

列出和删除触发器

在 psql 中，您可以使用 \d table_name 命令查看表上的触发器。要删除触发器，请使用 DROP TRIGGER 命令。

DROP TRIGGER trigger_name ON table_name;

潜在陷阱

• 性能影响：触发器会增加 DML（数据操作语言）操作的开销。复杂的触发器逻辑会显著降低 INSERT、UPDATE 和 DELETE 语句的速度。
• 调试：触发器中隐藏的逻辑可能使应用程序行为的调试变得困难。触发器正在修改数据这一点并非总是显而易见的。
• 级联触发器：请注意不要创建导致其他触发器触发的触发器，这可能导致复杂且难以预测的行为。