PostgreSQL / 系统课时

创建触发器

创建触发器

本课解决“数据变化后必须自动校验或留痕”的问题。你将创建 BEFORE UPDATE 与 AFTER UPDATE 触发器,在余额更新前阻止非法数据,并在更新成功后写入审计记录。

学习目标

  • 理解触发器函数与触发器对象的关系
  • 区分 BEFORE 和 AFTER 触发器的职责
  • 识别触发器在批量写入、排错和性能上的隐式成本

触发器如何工作

PostgreSQL 触发器由两部分组成:触发器函数定义要执行的逻辑,CREATE TRIGGER 决定在什么表、什么事件和什么时机调用该函数。

行级触发器使用 FOR EACH ROW,每一行变化都会执行一次。触发器函数中,OLD 表示变更前的行,NEW 表示变更后的行,TG_OP 表示当前操作类型。

BEFORE 与 AFTER 的选择

BEFORE 在数据真正写入前执行,可以修改 NEW,也可以抛出异常阻止写入,因此适合数据修正与补充校验。AFTER 只在写入成功后执行,不能再改变已保存的行,适合审计、统计或派生记录。

同一需求不要同时放在应用代码和触发器中重复执行,否则容易产生双写。必须保证无论哪个客户端写表都执行的规则,才适合放入触发器。

创建余额校验与审计触发器

以下脚本可在空测试库中直接运行。它创建账户表和审计表,再用两个触发器处理更新前校验与更新后留痕。

sql
DROP TABLE IF EXISTS account_balance_audit;
DROP TABLE IF EXISTS account;

CREATE TABLE account (
    id bigint PRIMARY KEY,
    owner_name text NOT NULL,
    balance numeric(12, 2) NOT NULL,
    updated_at timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE TABLE account_balance_audit (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    account_id bigint NOT NULL,
    action text NOT NULL,
    old_balance numeric(12, 2) NOT NULL,
    new_balance numeric(12, 2) NOT NULL,
    changed_at timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE OR REPLACE FUNCTION validate_account_balance()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- BEFORE 触发器可以拒绝非法的新行
    IF NEW.balance < 0 THEN
        RAISE EXCEPTION 'balance cannot be negative';
    END IF;

    NEW.updated_at := current_timestamp;
    RETURN NEW;
END;
$$;

CREATE OR REPLACE FUNCTION audit_account_balance()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- 只在余额确实变化时写入一条审计记录
    IF NEW.balance IS DISTINCT FROM OLD.balance THEN
        INSERT INTO account_balance_audit (
            account_id, action, old_balance, new_balance
        )
        VALUES (
            NEW.id, TG_OP, OLD.balance, NEW.balance
        );
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_account_validate_before_update
BEFORE UPDATE ON account
FOR EACH ROW
EXECUTE FUNCTION validate_account_balance();

CREATE TRIGGER trg_account_audit_after_update
AFTER UPDATE ON account
FOR EACH ROW
EXECUTE FUNCTION audit_account_balance();

INSERT INTO account (id, owner_name, balance)
VALUES (1, 'Alice', 1000.00);

UPDATE account
SET balance = balance - 200.00
WHERE id = 1;

SELECT id, balance FROM account;
SELECT action, old_balance, new_balance
FROM account_balance_audit;

预期结果

text
UPDATE 1

 id | balance
----+---------
  1 |  800.00

 action | old_balance | new_balance
--------+-------------+------------
 UPDATE |     1000.00 |     800.00

客户端显示的边框和空格可能不同,但账户余额应为 800.00,审计表应新增一条从 1000.00 变为 800.00 的记录。

验证失败路径

执行 UPDATE account SET balance = -1 WHERE id = 1; 时,BEFORE 触发器会抛出 balance cannot be negative,整条更新语句回滚,AFTER 触发器不会执行,也不会产生审计记录。

触发器与原语句处于同一事务中。触发器内部任何未处理异常都会使当前语句失败,这保证了一致性,也意味着触发器不应执行不可控的重任务。

隐式成本与使用边界

触发器不会直接出现在应用调用代码中,排错时容易被忽略。行级触发器在一次更新一万行时可能执行一万次,因此要关注索引、触发次数和审计表增长速度。

远程通知、消息重试和跨系统编排不应直接写在触发器中。更稳妥的做法是让触发器或事务写入本地事件表,再由应用异步投递。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

创建触发器

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通