PostgreSQL / 系统课时

选择数据库逻辑的实现位置

选择数据库逻辑的实现位置

本课解决“同一条业务规则应该写在哪里”的问题。你将按一致性范围、复用范围和副作用类型,在约束、函数、触发器与应用代码之间做出可解释的选择。

学习目标

  • 判断四种实现位置各自最适合承担的职责
  • 使用一致性、可见性和外部依赖评估方案
  • 避免规则重复、隐式执行和跨系统事务等常见问题

先判断规则属于哪一类

选择位置前,先问三个问题:规则是否对所有写入入口都必须成立,是否需要在单个数据库事务中完成,是否依赖网络、用户上下文或外部服务。

无条件成立的数据不变量应尽量靠近数据,用 NOT NULL、CHECK、UNIQUE 和外键表达。需要多步读写且必须原子执行的逻辑,可以封装为数据库函数。无法要求所有写入方主动调用、又必须自动执行的逻辑,才考虑触发器。

涉及权限页面、远程接口、消息重试、流程编排或频繁变化的业务策略,通常放在应用代码中。数据库事务不能可靠覆盖外部 HTTP、消息平台或第三方支付系统。

四种位置的职责边界

实现位置 适合承担 主要风险
约束 非空、范围、唯一性、引用完整性 难表达复杂多步流程
函数 原子计算、状态迁移、可复用数据操作 与数据库绑定更强
触发器 强制自动审计、派生字段、遗留入口兼容 调用隐蔽,批量执行成本高
应用代码 权限、流程、外部调用、重试与交互 多入口写库时可能绕过规则

优先级通常是:能用声明式约束就不用过程代码;需要显式调用时优先函数;只有自动执行不可替代时才用触发器;外部副作用与业务编排留在应用层。

一个分层实现示例

下面的脚本把金额和状态合法性放入约束,把取消订单封装为显式函数,把状态变化审计交给触发器。发送通知不放进数据库,而由应用在事务成功后处理。

sql
DROP TABLE IF EXISTS order_status_audit;
DROP TABLE IF EXISTS customer_order;

CREATE TABLE customer_order (
    id bigint PRIMARY KEY,
    merchant_no text NOT NULL,
    merchant_order_no text NOT NULL,
    amount numeric(12, 2) NOT NULL CHECK (amount > 0),
    status text NOT NULL CHECK (
        status IN ('PENDING', 'PAID', 'CANCELLED')
    ),
    UNIQUE (merchant_no, merchant_order_no)
);

CREATE TABLE order_status_audit (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id bigint NOT NULL,
    old_status text NOT NULL,
    new_status text NOT NULL,
    changed_at timestamptz NOT NULL DEFAULT current_timestamp
);

CREATE OR REPLACE FUNCTION audit_order_status()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN
        INSERT INTO order_status_audit (
            order_id, old_status, new_status
        )
        VALUES (
            NEW.id, OLD.status, NEW.status
        );
    END IF;

    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_order_status_audit
AFTER UPDATE OF status ON customer_order
FOR EACH ROW
EXECUTE FUNCTION audit_order_status();

CREATE OR REPLACE FUNCTION cancel_pending_order(p_order_id bigint)
RETURNS boolean
LANGUAGE plpgsql
AS $$
DECLARE
    v_affected_rows integer;
BEGIN
    -- 条件更新保证只有待处理订单能够被取消
    UPDATE customer_order
    SET status = 'CANCELLED'
    WHERE id = p_order_id
      AND status = 'PENDING';

    GET DIAGNOSTICS v_affected_rows = ROW_COUNT;
    RETURN v_affected_rows = 1;
END;
$$;

INSERT INTO customer_order (
    id, merchant_no, merchant_order_no, amount, status
)
VALUES (
    1, 'M1001', 'ORDER-001', 99.00, 'PENDING'
);

SELECT cancel_pending_order(1) AS cancelled;
SELECT id, status FROM customer_order;
SELECT old_status, new_status
FROM order_status_audit
WHERE order_id = 1;

预期结果

text
 cancelled
-----------
 t

 id |  status
----+-----------
  1 | CANCELLED

 old_status | new_status
------------+------------
 PENDING    | CANCELLED

结果说明三层职责已经生效:约束保证基础数据合法,函数完成原子状态迁移,触发器自动记录状态变化。应用收到 cancelled = true 后,再提交通知任务;通知失败不应回滚已经完成的数据库事务。

决策顺序

第一步,先尝试使用约束,因为它最明确、执行成本可预测,也能覆盖所有写入入口。第二步,若规则包含多条 SQL 且必须在一个事务中完成,再考虑函数。

第三步,只有调用方无法可靠遵守显式调用约定时,才使用触发器。第四步,凡是涉及网络、重试、人机交互或跨服务流程的逻辑,放回应用层,并用事务后事件或发件箱模式衔接数据库提交。

避免重复实现

同一条核心规则只保留一个权威实现位置。若数据库已有唯一约束,应用可以提前检查以改善提示,但仍应捕获数据库冲突,而不能把应用检查当作最终保证。

当函数或触发器包含重要规则时,应通过迁移脚本纳入版本控制,并为成功路径、拒绝路径和并发场景编写测试。否则逻辑虽然靠近数据,却可能脱离正常代码审查。

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

目录与阅读设置

阅读设置

17

记录一个想法

选择数据库逻辑的实现位置

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

登录哪都通