触发器设计概述
- 业务场景说明
- 财务总账入账前（before insert）进行严谨校验与自动补全：
- 校验：单行借贷规范（仅一边为正），会计期是否开启，避免重复入账（签名去重），并在提交时保证同一 trace_id 的分录借贷平衡。
- 自动补全：缺省补全 trace_id、posted_at；按汇率 fx_rate 折算 base_amount，并“锁定”所用汇率版本。
- 审计：生成稳定的 sha256 签名，作为幂等去重凭据，保障可追溯性与一致性。
- 触发条件分析
- BEFORE INSERT 行级触发器：
- 保证单行借贷合法性
- 检查会计期开启
- 自动补全 trace_id、posted_at
- 计算与锁定汇率（查表并 FOR SHARE 锁定版本），计算 base_amount
- 基于关键字段生成 sha256 签名
- DEFERRABLE 约束触发器（AFTER，INITIALLY DEFERRED）：
- 在事务提交时，保证同一 trace_id 下的分录合计借贷平衡
- 预期效果描述
- 插入 gl_entry 时，若字段缺失将被自动补全；若不符合会计期或汇率不可用、或借贷不合法、或重复入账，将立即阻断。
- 提交事务时，如同一 trace_id 的分录借贷不平衡，将阻断提交，确保总账一致性。
触发器代码
-- 要求：PostgreSQL 12+；需要扩展 pgcrypto 用于哈希与 UUID
CREATE EXTENSION IF NOT EXISTS pgcrypto;
-- 可选应用级配置项（可通过 ALTER DATABASE/ROLE SET 设置持久化）
-- app.base_currency：基础币种（默认 USD）
-- app.fx_table：汇率表名（默认 fx_rates）
-- app.period_table：会计期间表名（默认 accounting_periods）
-- 示例期望表结构（用于文档说明，不会被执行）
-- gl_entry(
-- id bigserial pk,
-- account_id bigint not null,
-- currency_code text not null,
-- debit_amount numeric not null default 0,
-- credit_amount numeric not null default 0,
-- base_amount numeric not null,
-- fx_rate numeric,
-- fx_rate_version integer,
-- base_currency_code text default current_setting('app.base_currency', true),
-- posted_at timestamptz,
-- period_id integer,
-- trace_id uuid,
-- description text,
-- source_system text,
-- signature text, -- 建议为 text 存放十六进制哈希
-- created_at timestamptz default now()
-- );
-- 辅助函数：获取开启的会计期间ID（动态SQL避免编译期对象依赖）
CREATE OR REPLACE FUNCTION fn_get_open_period_id(p_ts timestamptz)
RETURNS integer
LANGUAGE plpgsql
STABLE
SET search_path = pg_catalog, public
AS $$
DECLARE
v_tbl text := COALESCE(current_setting('app.period_table', true), 'accounting_periods');
v_sql text;
v_id integer;
BEGIN
IF to_regclass(v_tbl) IS NULL THEN
RAISE EXCEPTION '会计期间表 "%" 不存在，请通过 app.period_table 指定正确表名', v_tbl;
END IF;
v_sql := format(
'SELECT id FROM %I
WHERE start_at <= $1 AND $1 < end_at
AND is_open = true
ORDER BY id DESC
LIMIT 1
FOR SHARE',
v_tbl
);
EXECUTE v_sql INTO v_id USING p_ts;
IF v_id IS NULL THEN
RAISE EXCEPTION '未找到开启的会计期间覆盖时间点 %', p_ts;
END IF;
RETURN v_id;
END;
$$;
-- 辅助函数：查询并锁定汇率与版本
-- 期望 fx_rates 表具备字段：from_currency, to_currency, valid_from, valid_to, rate, version
CREATE OR REPLACE FUNCTION fn_get_fx(p_from text, p_to text, p_at timestamptz)
RETURNS TABLE(rate numeric, version integer)
LANGUAGE plpgsql
STABLE
SET search_path = pg_catalog, public
AS $$
DECLARE
v_tbl text := COALESCE(current_setting('app.fx_table', true), 'fx_rates');
v_sql text;
BEGIN
IF p_from = p_to THEN
rate := 1;
version := 0;
RETURN NEXT;
RETURN;
END IF;
IF to_regclass(v_tbl) IS NULL THEN
RAISE EXCEPTION '汇率表 "%" 不存在，请通过 app.fx_table 指定正确表名', v_tbl;
END IF;
v_sql := format($f$
SELECT r.rate, r.version
FROM %I r
WHERE r.from_currency = $1
AND r.to_currency = $2
AND r.valid_from <= $3
AND (r.valid_to IS NULL OR $3 < r.valid_to)
ORDER BY r.valid_from DESC, r.version DESC
LIMIT 1
FOR SHARE
$f$, v_tbl);
EXECUTE v_sql INTO rate, version USING p_from, p_to, p_at;
IF rate IS NULL THEN
RAISE EXCEPTION '未找到汇率：% -> % 在 %', p_from, p_to, p_at;
END IF;
RETURN NEXT;
END;
$$;
-- BEFORE INSERT 触发器函数：单行校验、自动补全、汇率折算、签名生成
CREATE OR REPLACE FUNCTION trg_bi_gl_entry()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
SET search_path = pg_catalog, public
AS $$
DECLARE
v_base_ccy text := COALESCE(current_setting('app.base_currency', true), 'USD');
v_rate numeric;
v_ver integer;
v_amt numeric;
BEGIN
-- 1) 借贷规范：仅一边为正，且不可同时为零或同时为正
IF COALESCE(NEW.debit_amount, 0) > 0 AND COALESCE(NEW.credit_amount, 0) > 0 THEN
RAISE EXCEPTION '借贷同时为正：debit=%, credit=%', NEW.debit_amount, NEW.credit_amount;
END IF;
IF COALESCE(NEW.debit_amount, 0) = 0 AND COALESCE(NEW.credit_amount, 0) = 0 THEN
RAISE EXCEPTION '借贷至少一边为正（另一边必须为0）';
END IF;
IF NEW.debit_amount < 0 OR NEW.credit_amount < 0 THEN
RAISE EXCEPTION '借贷金额不可为负';
END IF;
-- 2) 自动补全 posted_at / trace_id
IF NEW.posted_at IS NULL THEN
NEW.posted_at := clock_timestamp();
END IF;
IF NEW.trace_id IS NULL THEN
NEW.trace_id := gen_random_uuid();
END IF;
-- 3) 会计期开启校验（并回填 period_id，如存在该列）
-- 若表结构中不存在 period_id，可忽略赋值，但校验仍会执行
PERFORM 1;
BEGIN
NEW.period_id := fn_get_open_period_id(NEW.posted_at);
EXCEPTION
WHEN undefined_column THEN
-- period_id 列不存在，仅做校验不回填
PERFORM fn_get_open_period_id(NEW.posted_at);
END;
-- 4) 汇率折算与版本锁定
IF NEW.currency_code IS NULL THEN
RAISE EXCEPTION 'currency_code 不能为空';
END IF;
IF NEW.currency_code = v_base_ccy THEN
-- 基础币种：汇率视为1，版本可置0
NEW.fx_rate := 1;
IF NEW.fx_rate_version IS NULL THEN
NEW.fx_rate_version := 0;
END IF;
ELSE
-- 若外部已经传入 fx_rate 与版本，可复用并不重复查询；否则查询并锁定
IF NEW.fx_rate IS NULL OR NEW.fx_rate <= 0 OR NEW.fx_rate_version IS NULL THEN
SELECT rate, version INTO v_rate, v_ver
FROM fn_get_fx(NEW.currency_code, v_base_ccy, NEW.posted_at);
IF NEW.fx_rate IS NULL OR NEW.fx_rate <= 0 THEN
NEW.fx_rate := v_rate;
END IF;
IF NEW.fx_rate_version IS NULL THEN
NEW.fx_rate_version := v_ver;
END IF;
END IF;
IF NEW.fx_rate IS NULL OR NEW.fx_rate <= 0 THEN
RAISE EXCEPTION '无效汇率：% -> % at %', NEW.currency_code, v_base_ccy, NEW.posted_at;
END IF;
END IF;
-- 5) 计算 base_amount（以正额计量，方向由借/贷体现）
v_amt := CASE
WHEN COALESCE(NEW.debit_amount, 0) > 0 THEN NEW.debit_amount
ELSE NEW.credit_amount
END;
NEW.base_amount := v_amt * NEW.fx_rate;
-- 6) 生成幂等签名（sha256，十六进制），作为去重依据
-- 签名构成应涵盖：trace_id、账户、币种、借贷金额、base_amount、基础币种、汇率/版本、posted_at（秒级）
-- 注意：签名字段 signature 建议为 text
NEW.signature := encode(
digest(
concat_ws('|',
COALESCE(NEW.trace_id::text, ''),
COALESCE(NEW.account_id::text, ''),
COALESCE(NEW.currency_code, ''),
to_char(COALESCE(NEW.debit_amount, 0), 'FM9999999999990.999999'),
to_char(COALESCE(NEW.credit_amount, 0), 'FM9999999999990.999999'),
to_char(COALESCE(NEW.base_amount, 0), 'FM9999999999990.999999'),
v_base_ccy,
to_char(COALESCE(NEW.fx_rate, 0), 'FM9999999999990.999999'),
COALESCE(NEW.fx_rate_version::text, ''),
to_char(date_trunc('second', NEW.posted_at), 'YYYY-MM-DD"T"HH24:MI:SSOF')
)::bytea,
'sha256'
),
'hex'
);
RETURN NEW;
END;
$$;
-- BEFORE INSERT 触发器
DROP TRIGGER IF EXISTS bi_gl_entry ON gl_entry;
CREATE TRIGGER bi_gl_entry
BEFORE INSERT ON gl_entry
FOR EACH ROW
EXECUTE FUNCTION trg_bi_gl_entry();
-- 事务提交时的借贷平衡校验（按 trace_id 分组），使用约束触发器，初始化为延迟到提交时检查
CREATE OR REPLACE FUNCTION trg_balance_gl_entry()
RETURNS trigger
LANGUAGE plpgsql
VOLATILE
SET search_path = pg_catalog, public
AS $$
DECLARE
v_trace uuid;
v_debit numeric;
v_credit numeric;
BEGIN
v_trace := CASE
WHEN TG_OP IN ('INSERT', 'UPDATE') THEN NEW.trace_id
ELSE OLD.trace_id
END;
-- trace_id 为空则不校验（允许独立单条分录场景）
IF v_trace IS NULL THEN
RETURN NULL;
END IF;
SELECT COALESCE(SUM(debit_amount), 0), COALESCE(SUM(credit_amount), 0)
INTO v_debit, v_credit
FROM gl_entry
WHERE trace_id = v_trace;
IF v_debit <> v_credit THEN
RAISE EXCEPTION '分录不平衡（trace_id=%）：debit=%，credit=%', v_trace, v_debit, v_credit;
END IF;
RETURN NULL;
END;
$$;
DROP TRIGGER IF EXISTS cs_gl_entry_balance ON gl_entry;
CREATE CONSTRAINT TRIGGER cs_gl_entry_balance
AFTER INSERT OR UPDATE OR DELETE ON gl_entry
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION trg_balance_gl_entry();
-- 去重建议：对签名建立唯一索引（需确保 signature 为 text 且非 NULL）
-- 注意：CONCURRENTLY 不能在事务中执行，如在迁移脚本中请拆到单独事务
-- CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS ux_gl_entry_signature ON gl_entry(signature);
-- 性能建议：支撑触发器校验与查询的必要索引（如不存在）
-- 建议的非唯一索引（根据实际表结构调整）
-- CREATE INDEX IF NOT EXISTS ix_gl_entry_trace_id ON gl_entry(trace_id);
代码说明
-
关键语法解释
- 使用 CREATE EXTENSION pgcrypto 提供 digest 与 gen_random_uuid。
- 使用 SET search_path = pg_catalog, public 固定搜索路径，规避 search_path 污染风险。
- fn_get_open_period_id / fn_get_fx 使用动态 SQL，依赖 app.period_table / app.fx_table 配置或默认表名；通过 to_regclass 动态检测表是否存在，避免函数创建期失败。
- BEFORE INSERT 触发器：
- 校验借贷：仅允许一侧为正且非负；两侧同时为0或同时为正都会抛错。
- 自动补全：posted_at 缺省为当前时间；trace_id 缺省为 gen_random_uuid()。
- 会计期开启：调用 fn_get_open_period_id 进行校验，并尝试赋值 NEW.period_id（若该列存在）。
- 汇率获取与锁定：若非基础币种，优先使用传入的 fx_rate/fx_rate_version，否则从 fx 表选取有效期内最新记录，同时 FOR SHARE 以锁定使用版本。
- base_amount 计算：以正额计量（方向由借/贷体现），避免产生符号歧义。
- 签名：将关键字段按字符串拼接，digest 后以十六进制存储到 signature（text）。
- 约束触发器（DEFERRABLE INITIALLY DEFERRED）：在事务提交时，按 trace_id 汇总借贷金额，若不相等则阻断提交，保证成组分录平衡。
-
业务逻辑说明
- 单行规范保证：每行分录必须要么借要么贷，不得两者兼有或皆无。
- 会计期校验：确保入账时间点在开启会计期间内，防止跨期/关账期间入账。
- 汇率与版本：确保计算 base_amount 的汇率有明确版本来源，且在插入时“锁定”所用版本，便于审计追溯。
- 签名去重：签名覆盖 trace_id、账户、币种、金额、汇率、版本、posted_at 等关键信息，防止重复入账。
- 成组平衡校验：对同一 trace_id 的整组分录在事务提交时必须借贷相等，满足会计平衡。
-
注意事项提醒
- 请确保 gl_entry.signature 字段类型为 text；如为 bytea，请将签名赋值改为直接 digest(...)，或 encode(...)->text 改为 bytea。
- 请配置 app.base_currency（如 USD/CNY 等）并保证 gl_entry.currency_code 存在有效值。
- 请准备汇率表与会计期间表并通过 app.fx_table、app.period_table 指向正确对象，且列名与示例一致或相应调整动态 SQL。
- 唯一索引 ux_gl_entry_signature 未在上述脚本中强制创建为 CONCURRENTLY 以兼顾部署安全性，请在变更窗口单独执行。
性能建议
- 索引优化建议
- gl_entry(trace_id)：支撑提交时的分组汇总校验。
- gl_entry(signature) 唯一索引：强力去重，建议 NOT NULL 并保持短列（十六进制64字符）。
- 会计期间表：accounting_periods(start_at, end_at, is_open) 或基于有效期的覆盖索引。
- 汇率表：fx_rates(from_currency, to_currency, valid_from DESC) 覆盖有效期查询；可加部分索引包含 valid_to。
- 监控指标说明
- 插入失败率与异常类型分布：用于监控会计期关闭、汇率缺失、重复入账等异常。
- 事务提交时的触发器延迟：大批量同一 trace_id 的提交时，注意提交阶段的聚合开销。
- 索引命中率与膨胀：trace_id、signature 索引的大小与碎片，定期 REINDEX/维护。
- 常见问题预防
- 大批量批处理：建议批次内复用同一 trace_id，且在单事务内提交；避免跨事务导致组内不平衡。
- 汇率切换：确保 fx_rates 版本与有效期维护规范，避免边界时间点无匹配；必要时业务侧显式传入 fx_rate_version。
- 时区一致性：posted_at 建议使用 timestamptz，统一以 UTC 存储，避免时区导致的期间匹配偏差。
测试用例
-
基本功能测试场景
- 自动补全
- INSERT 仅给出 debit_amount/currency_code/account_id，缺省 posted_at/trace_id，期望自动补全且 period_id 正确。
- 借贷规范
- 同时赋正的 debit_amount 与 credit_amount，期望报错。
- 二者皆为0，期望报错。
- 会计期校验
- posted_at 落在关闭期间，期望报错。
- posted_at 落在开启期间，成功。
- 汇率与 base_amount
- 基础币种（等于 app.base_currency），期望 fx_rate=1、version=0、base_amount=原币金额。
- 外币且不传 fx_rate，存在有效期内汇率，期望自动填充 fx_rate/fx_rate_version 且 base_amount=金额*汇率。
- 外币且无有效汇率，期望报错。
- 签名去重
- 两次插入完全相同数据（含 trace_id），第二次因唯一索引（创建后）报重复键错误。
- 组平衡校验
- 同一 trace_id 下插入一借一贷金额相等的两条，事务提交成功。
- 只插入借方不插入贷方，提交时触发器报不平衡错误。
-
边界条件测试建议
- 极小/极大金额与多小数位，确认签名格式化无歧义，base_amount 精度与四舍五入符合财务规则。
- 在有效期边界（valid_from/valid_to）时刻插入，验证汇率选择正确。
- 高并发插入同一 trace_id 的事务，验证提交阶段平衡校验与索引支撑性能。
- 基础币种与外币相同/不同切换，验证 fx_rate 与 version 逻辑。
- signature 列空值与唯一索引行为（建议 signature 设为 NOT NULL 并默认由触发器生成）。
说明：以上代码与建议基于 PostgreSQL 12+ 并假定 gl_entry 具有文中提及字段类型。若实际表结构存在差异，请相应调整触发器函数中的列名与签名构成字段，并确保 app.fx_table、app.period_table 指向的对象及列名与查询一致。为避免运行时语义变化，建议在测试环境回归后再上线生产。
