PostgreSQL

Appearance

触发器的典型使用场景 ​

本文聚焦 PostgreSQL 触发器在真实业务中的典型用法，不讲抽象语法，尽量用“问题场景 → SQL 示例 → 行为分析”的方式来理解。

1. 审计与操作日志 ​

1.1 业务问题 ​

在实际系统中，经常会遇到这样的需求：

  • 谁在什么时候修改了哪条数据？
  • 修改前是什么值，修改后变成了什么？
  • 删除了一条记录，还能不能查到它曾经存在过？

如果完全依赖应用层“记日志”：

  • 每个更新/删除的地方都要额外写一次日志代码，容易漏；
  • 直接连数据库写 SQL（如 DBA 修数据、脚本导入）时，很可能绕过应用逻辑，不记日志。

1.2 使用触发器解决 ​

策略：在业务表上定义 AFTER INSERT/UPDATE/DELETE 触发器，把变更记录写入审计表，保证只要数据变动就一定有记录。

示例：用户表审计 ​

1）原始业务表结构

sql
CREATE TABLE app_user (
    id          BIGSERIAL PRIMARY KEY,
    username    TEXT        NOT NULL,
    email       TEXT        NOT NULL,
    status      TEXT        NOT NULL DEFAULT 'active',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

2）审计日志表结构

sql
CREATE TABLE app_user_audit (
    audit_id      BIGSERIAL PRIMARY KEY,
    user_id       BIGINT      NOT NULL,
    op            TEXT        NOT NULL,      -- 'INSERT' / 'UPDATE' / 'DELETE'
    op_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
    op_by         TEXT,                      -- 操作人，业务可以通过 session_variable 传入
    old_row       JSONB,                     -- 变更前数据快照
    new_row       JSONB                      -- 变更后数据快照
);

3）触发器函数

sql
CREATE OR REPLACE FUNCTION trg_app_user_audit()
RETURNS TRIGGER AS $$
DECLARE
    v_op_by TEXT;
BEGIN
    -- 从当前会话中取出“操作人”，实际项目中可以通过
    -- SELECT set_config('app.current_user', 'alice', false); 传入
    BEGIN
        v_op_by := current_setting('app.current_user');
    EXCEPTION WHEN others THEN
        v_op_by := NULL; -- 如果没设置就留空
    END;

    IF TG_OP = 'INSERT' THEN
        INSERT INTO app_user_audit(user_id, op, op_by, old_row, new_row)
        VALUES (NEW.id, 'INSERT', v_op_by, NULL, to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO app_user_audit(user_id, op, op_by, old_row, new_row)
        VALUES (NEW.id, 'UPDATE', v_op_by, to_jsonb(OLD), to_jsonb(NEW));
        RETURN NEW;

    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO app_user_audit(user_id, op, op_by, old_row, new_row)
        VALUES (OLD.id, 'DELETE', v_op_by, to_jsonb(OLD), NULL);
        RETURN OLD;
    END IF;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

4）创建触发器

sql
CREATE TRIGGER app_user_audit_trg
AFTER INSERT OR UPDATE OR DELETE ON app_user
FOR EACH ROW
EXECUTE FUNCTION trg_app_user_audit();

1.3 行为过程演示 ​

原始数据（app_user）

text
id | username | email             | status  | created_at          | updated_at
---+----------+-------------------+---------+---------------------+---------------------
 1 | alice    | alice@example.com | active  | 2025-11-01 10:00:00 | 2025-11-01 10:00:00

场景：alice 修改邮箱，之后被删除

sql
-- 设置操作人
SELECT set_config('app.current_user', 'admin_01', false);

-- 修改邮箱
UPDATE app_user
   SET email = 'alice@new.com',
       updated_at = now()
 WHERE id = 1;

-- 删除用户
DELETE FROM app_user WHERE id = 1;

触发器分步效果

  1. UPDATE 时向 app_user_audit 插入一条 op='UPDATE' 记录，old_row 是修改前状态，new_row 是修改后状态。
  2. DELETE 时再插入一条 op='DELETE' 记录，old_row 保留被删前的完整数据。

最终，查询 app_user_audit 可以完整还原整个生命周期，满足审计追踪需求。

审计类场景非常适合用触发器：规则简单统一、与业务强相关、必须保证“任何写操作都被记录”。

2. 自动填充与派生字段 ​

2.1 业务问题 ​

常见字段：created_at、updated_at、created_by、updated_by 等，如果依赖应用层维护，会出现：

  • 某些地方忘记填或填错时间；
  • 多个微服务重复写同样的代码；
  • 直接 SQL 操作数据库时，完全绕过了这些约定。

2.2 使用触发器解决 ​

策略：在 BEFORE INSERT/UPDATE 触发器中统一填充或修改这些字段。

示例：统一维护时间戳 ​

sql
CREATE TABLE article (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT        NOT NULL,
    content     TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

触发器函数

sql
CREATE OR REPLACE FUNCTION trg_article_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        -- 如果应用没有显式传 created_at，就用当前时间
        IF NEW.created_at IS NULL THEN
            NEW.created_at := now();
        END IF;
        NEW.updated_at := NEW.created_at;

    ELSIF TG_OP = 'UPDATE' THEN
        NEW.updated_at := now();
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER article_timestamp_trg
BEFORE INSERT OR UPDATE ON article
FOR EACH ROW
EXECUTE FUNCTION trg_article_timestamp();

2.3 行为过程演示 ​

插入数据（应用只管业务字段）

sql
INSERT INTO article(title, content)
VALUES ('触发器使用场景', '正文内容...');

触发器自动填充

  • 插入时自动填 created_at 和 updated_at 为当前时间；
  • 更新任意字段时，自动把 updated_at 刷为当前时间。

所有入口（应用、脚本、DBA 手工 SQL）都会统一遵守这条规则，避免“有的行有时间，有的行没时间”的脏数据。

3. 复杂业务约束 / 数据质量校验 ​

3.1 业务问题 ​

有些约束无法用简单的 CHECK / 外键直接表达，例如：

  • 订单状态为“已发货”后，不允许修改商品数量和单价；
  • 账户余额不能小于某个动态额度；
  • 同一时间段内，同一个资源不能被重复占用（如会议室、设备等）。

如果只在应用里写这些判断：

  • 多个服务/脚本必须全部实现同样的逻辑，容易不一致；
  • 直接操作数据库时可能绕过校验，破坏数据质量。

3.2 使用触发器解决 ​

在 BEFORE INSERT/UPDATE/DELETE 中执行业务校验，不满足时直接抛异常，阻止写入。

示例：订单发货后禁止修改明细 ​

订单表+订单明细表

sql
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    status      TEXT NOT NULL, -- draft / paid / shipped / cancelled
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_item (
    id          BIGSERIAL PRIMARY KEY,
    order_id    BIGINT      NOT NULL REFERENCES orders(id),
    product_id  BIGINT      NOT NULL,
    quantity    INTEGER     NOT NULL,
    unit_price  NUMERIC(10,2) NOT NULL
);

触发器函数：更新/删除明细前校验订单状态

sql
CREATE OR REPLACE FUNCTION trg_order_item_check_status()
RETURNS TRIGGER AS $$
DECLARE
    v_status TEXT;
BEGIN
    -- 找到这条明细对应的订单状态
    SELECT status INTO v_status
      FROM orders
     WHERE id = COALESCE(NEW.order_id, OLD.order_id);

    IF v_status IN ('shipped', 'cancelled') THEN
        RAISE EXCEPTION '订单状态为 %，不允许再修改明细', v_status
            USING ERRCODE = 'check_violation';
    END IF;

    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER order_item_check_status_trg
BEFORE UPDATE OR DELETE ON order_item
FOR EACH ROW
EXECUTE FUNCTION trg_order_item_check_status();

3.3 行为过程演示 ​

  • 当订单为 draft 或 paid 状态时，可以正常调整明细；
  • 一旦订单状态改为 shipped，再尝试修改或删除 order_item，触发器就会抛异常，阻止操作。

这类“必须保证数据层不出现非法状态”的规则，非常适合下沉到触发器中做保护。

4. 同步 / 派生数据表（简单汇总） ​

4.1 业务问题 ​

有些场景需要维护“冗余表”或“汇总表”来加速查询，例如：

  • 库存系统：根据流水表维护一张当前库存表；
  • 报表系统：根据订单明细实时维护按天、按门店的销售汇总表。

如果完全依赖定时任务或应用代码：

  • 实时性差（需要等一段时间跑任务）；
  • 容易出现“流水表和汇总表不一致”的问题。

4.2 使用触发器解决 ​

在流水或主表上增加 AFTER INSERT/UPDATE/DELETE 触发器，对汇总表做增量更新。

示例：库存流水 → 当前库存 ​

库存变动流水表

sql
CREATE TABLE stock_log (
    id          BIGSERIAL PRIMARY KEY,
    sku_id      BIGINT      NOT NULL,
    change_qty  INTEGER     NOT NULL, -- 正数入库，负数出库
    reason      TEXT        NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

当前库存表

sql
CREATE TABLE stock_current (
    sku_id      BIGINT PRIMARY KEY,
    qty         INTEGER NOT NULL
);

触发器函数：每插入一条流水，增量更新当前库存

sql
CREATE OR REPLACE FUNCTION trg_stock_log_update_current()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO stock_current(sku_id, qty)
        VALUES (NEW.sku_id, NEW.change_qty)
        ON CONFLICT (sku_id)
        DO UPDATE SET qty = stock_current.qty + EXCLUDED.qty;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER stock_log_update_current_trg
AFTER INSERT ON stock_log
FOR EACH ROW
EXECUTE FUNCTION trg_stock_log_update_current();

4.3 行为过程演示 ​

原始数据（stock_log）

text
id | sku_id | change_qty | reason    | created_at
---+--------+------------+-----------+---------------------
 1 | 1001   |       100  | init      | 2025-11-01 09:00:00
 2 | 1001   |       -10  | sale      | 2025-11-01 10:00:00
 3 | 1001   |       -20  | sale      | 2025-11-01 11:00:00

触发器对 stock_current 的效果

text
sku_id | qty
-------+-----
 1001  | 70

高并发、大批量更新的场景下，这类触发器可能成为瓶颈，需要配合合理的锁粒度设计、分库分表，或使用异步消费方案。

5. 软删除与归档 ​

5.1 业务问题 ​

很多系统要求“删除记录但又要保留历史”，常见设计：

  • 在主表增加 deleted_at 或 is_deleted 字段做软删除；
  • 或定期将历史数据归档到历史表，避免主表太大影响性能。

如果完全靠应用：

  • 删除时容易忘记归档；
  • 归档逻辑散落在多处，难以统一维护。

5.2 使用触发器解决 ​

在 BEFORE DELETE 或 AFTER INSERT/UPDATE 中实现统一归档逻辑。

示例：订单删除时归档 ​

订单主表 & 历史表

sql
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     BIGINT      NOT NULL,
    status      TEXT        NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders_history (
    id           BIGINT      NOT NULL,
    user_id      BIGINT      NOT NULL,
    status       TEXT        NOT NULL,
    total_amount NUMERIC(10,2) NOT NULL,
    created_at   TIMESTAMPTZ NOT NULL,
    deleted_at   TIMESTAMPTZ NOT NULL,
    PRIMARY KEY(id, deleted_at)
);

触发器函数：删除前归档

sql
CREATE OR REPLACE FUNCTION trg_orders_archive_before_delete()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO orders_history(id, user_id, status, total_amount, created_at, deleted_at)
    VALUES (OLD.id, OLD.user_id, OLD.status, OLD.total_amount, OLD.created_at, now());

    RETURN OLD; -- 允许后续真正 DELETE
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER orders_archive_before_delete_trg
BEFORE DELETE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_archive_before_delete();

5.3 行为过程演示 ​

  • 应用执行 DELETE FROM orders WHERE id = 123;；
  • 触发器先向 orders_history 写入一条包含所有字段的记录；
  • 然后真实删除主表中的记录；
  • 查历史时只需要查 orders_history 即可看到所有删除记录。

对外表现“已经删除”，对内仍能保留完整历史，适用于日志量比较可控的场景。

6. 触发外部事件 / 异步任务 ​

6.1 业务问题 ​

某些数据变更需要通知其他系统或异步任务，例如：

  • 订单状态变为“已支付”时通知发货系统；
  • 用户信息变更时刷新搜索引擎索引或缓存。

6.2 使用触发器 + LISTEN/NOTIFY ​

PostgreSQL 提供 LISTEN / NOTIFY 机制，可以在触发器中发出通知，外部服务用长连接监听即可。

示例：订单状态变更发事件 ​

sql
CREATE OR REPLACE FUNCTION trg_orders_notify_status_change()
RETURNS TRIGGER AS $$
DECLARE
    payload JSONB;
BEGIN
    IF TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status THEN
        payload := jsonb_build_object(
            'order_id', NEW.id,
            'old_status', OLD.status,
            'new_status', NEW.status,
            'changed_at', now()
        );
        PERFORM pg_notify('orders_status_changed', payload::text);
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER orders_notify_status_change_trg
AFTER UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_orders_notify_status_change();

外部服务（如 Node.js、Java、Go 等）可以：

  • 建立到 PostgreSQL 的连接；
  • 执行 LISTEN orders_status_changed;；
  • 在回调中收到 payload，触发后续业务逻辑（比如推送消息、调用其他微服务）。

触发器里只负责“轻量发送事件”，复杂耗时逻辑建议放在外部服务中执行，避免阻塞数据库事务。

7. 安全控制与脱敏 ​

7.1 业务问题 ​

  • 敏感字段（手机号、身份证号、银行卡号等）需要统一脱敏或加密；
  • 某些字段只能由特定角色修改，不能随便改。

7.2 使用触发器实现底线保护 ​

示例：手机号统一脱敏存储 ​

sql
CREATE TABLE customer (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT        NOT NULL,
    phone_raw   TEXT,        -- 原始手机号（加密存储更安全）
    phone_mask  TEXT         -- 脱敏后显示用，如 138****0000
);

触发器函数：写入时自动生成脱敏字段

sql
CREATE OR REPLACE FUNCTION mask_phone(p_phone TEXT)
RETURNS TEXT AS $$
BEGIN
    IF p_phone IS NULL OR length(p_phone) < 7 THEN
        RETURN p_phone;
    END IF;
    RETURN substr(p_phone, 1, 3) || '****' || substr(p_phone, length(p_phone) - 3, 4);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION trg_customer_mask_phone()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.phone_raw IS NOT NULL THEN
        NEW.phone_mask := mask_phone(NEW.phone_raw);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

创建触发器

sql
CREATE TRIGGER customer_mask_phone_trg
BEFORE INSERT OR UPDATE OF phone_raw ON customer
FOR EACH ROW
EXECUTE FUNCTION trg_customer_mask_phone();

这样应用层只需要关心 phone_raw 的写入，数据库会统一生成 phone_mask，查询展示时只查脱敏字段即可。

8. 什么时候不该用触发器 ​

触发器很强大，但也有明显风险：

  • 逻辑隐蔽：业务行为藏在数据库里，不看触发器就难以理解数据变化路径；
  • 调试困难：多个触发器互相调用时，排查 bug 成本很高；
  • 性能风险：在高并发、大批量写入场景，复杂触发器会拖慢整体性能；
  • 迁移困难：大量业务逻辑固化在触发器上后，跨数据库迁移成本很大。

比较推荐的实践：

  • 触发器主要承担“底线防护类逻辑”：审计、时间戳维护、关键约束、简单汇总等；
  • 复杂业务流程（如工作流、长事务、跨系统协调）放在应用层/服务层；
  • 严格控制每个表触发器数量和复杂度，并为它们写清晰的文档。

可以把触发器当作“数据库侧的 AOP（面向切面）工具”：

  • 用它做与数据强相关、可以局部统一的横切逻辑；
  • 避免在其中写大量分支和耗时操作。

9. 小结 ​

  • 审计日志：记录是谁在什么时候改了什么数据，非常适合触发器。
  • 自动填充/派生字段：统一维护时间戳、操作人、脱敏字段等，减少应用重复劳动。
  • 复杂约束与数据质量：必须在数据层防止出现“非法状态”时，用触发器强制约束。
  • 同步/汇总表：简单实时汇总、维护当前状态（如库存）时可以由触发器增量更新。
  • 软删除与归档：删除前自动写入历史表，实现对外删除、对内保留。
  • 外部事件 & 安全控制：通过 NOTIFY 或字段校验，与外部系统/安全逻辑衔接。

随着你对 PostgreSQL 更熟悉，可以在测试环境多尝试为现有表增加小型触发器（例如统一更新时间戳），从这些“安全的、小而美”的场景开始，逐步体会触发器的威力与成本。