PostgreSQL：插入主表记录后自动同步关联表数据

问题场景 #

插入accounts表生成自增account_id后，需要自动将该ID插入currency_golden_credit和currency_iron_credit两个关联表，使用默认的balance=0值。

原SQL错误原因 #

你尝试的SQL存在语法问题：在同一个CTE（WITH子句）后连续执行两个INSERT语句不符合SQL规范，CTE的作用域仅覆盖紧随其后的单个SQL语句，无法同时为两个INSERT提供数据。

解决方案 #

方案1：单次手动执行的SQL写法 #

通过一次插入accounts后，分别为两个关联表执行插入操作，利用CTE暂存生成的account_id：

WITH inserted_account AS (
  INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959')
  RETURNING account_id
)
INSERT INTO currency_golden_credit (ref_id) SELECT account_id FROM inserted_account;

-- 单独执行第二个插入，从accounts表获取已生成的account_id
INSERT INTO currency_iron_credit (ref_id) 
SELECT account_id FROM accounts WHERE unique_id = 'c78f139a-7d71-429b-b55c-0d8fad481959';

方案2：自动触发的触发器（推荐） #

如果需要每次插入accounts都自动生成关联表记录，使用触发器是更优的方案：

1. 创建触发器函数：

CREATE OR REPLACE FUNCTION insert_account_currency_records()
RETURNS TRIGGER AS $$
BEGIN
  -- 向两个关联表插入新生成的account_id
  INSERT INTO currency_golden_credit (ref_id) VALUES (NEW.account_id);
  INSERT INTO currency_iron_credit (ref_id) VALUES (NEW.account_id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到accounts表的插入事件：

CREATE TRIGGER trigger_after_insert_account
AFTER INSERT ON accounts
FOR EACH ROW
EXECUTE FUNCTION insert_account_currency_records();

之后只需执行基础的插入语句，关联表的记录会自动生成：

INSERT INTO accounts (unique_id) VALUES ('c78f139a-7d71-429b-b55c-0d8fad481959');

内容的提问来源于stack exchange，提问作者user22150393