postgresql - 使用 Trigger PostgreSQL 计算每一行的剩余余额并插入到表上的下一个 id 行

Question

我有 3 个表 invoice_items、topup 和 topup_usage。invoice_items那里有我2 column用于触发器amount和account_id。

表invoice_items：

id             account_id                   type     amount
200  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       4
201  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       7
202  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       2

表topup_usage：

id    id_topup    balance

表和充值数据的初始值topup：

id              account_id              amount_ds    status
322 ac96c2bc-d4e3-4d75-9814-0186be8e0163    4        active
323 ac96c2bc-d4e3-4d75-9814-0186be8e0163    7        active
324 ac96c2bc-d4e3-4d75-9814-0186be8e0163    2        active

有这种情况，当在表 invoice_item 上插入新数据时，我从列中获取 2 个数据account_id并amount在account_idforaccount_id和amountfor 的位置插入表充值amount_ds。然后我在我的系统上执行 2 个事务，当状态为“使用”时从表中触发，invoice_items如下所示：

id             account_id                   type     amount
200  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       4
201  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       7
202  ac96c2bc-d4e3-4d75-9814-0186be8e0163   topup       2
203  ac96c2bc-d4e3-4d75-9814-0186be8e0163   usage       2
204  ac96c2bc-d4e3-4d75-9814-0186be8e0163   usage       3

插入关于topup_usage我尝试这种情况的数据：

id   topup_id  amount
399    322        2
400    322        3

我尝试使 topup_id 322 总计最大为 4，如表 topup ( amount_ds) 上，但总共有 5 个，当 topup_id 上的总使用量超过 4 时，例如 5，它会生成到下一个 topup_id ，如下所示：

 id      topup_id    amount
 399       322        2
 400       322        2
 401       323        1

然后为 topup_id 323 计算最大值 7，但如前所述，topup_id 323 最多可使用 6 个数量。然后如果插入7，像这样

id      topup_id    amount
399       322         2
400       322         2
401       323         1
402       323         6
403       324         1

我使用这样的触发器：

create or replace function topup_usage() returns trigger
    language plpgsql
as $$
BEGIN
  IF new.type = 'USAGE' THEN
    IF(SELECT notes  = 'true' FROM accounts WHERE accounts.id = new.account_id) THEN

    IF ((SELECT amount_ds FROM topup WHERE topup.account_id = new.account_id AND status = 'active' ORDER BY id ASC LIMIT 1) - new.amount < 0 AND (SELECT COUNT(id) > 1 FROM topup WHERE topup.account_id=new.account_id)) THEN
        INSERT INTO topup_usage (id_topup, balance)
        VALUES((SELECT id FROM topup WHERE topup.account_id = new.account_id and status = 'active' limit 1), (SELECT amount_ds FROM topup WHERE topup.account_id=new.account_id AND status='active' ORDER BY id ASC LIMIT 1) - (SELECT COALESCE(sum(balance), 0) FROM topup_usage WHERE id_topup=(SELECT id FROM topup WHERE topup.account_id=new.account_id ORDER BY id DESC LIMIT 1)) );

         INSERT INTO topup_usage (id_topup, balance)
          VALUES((SELECT id FROM topup WHERE topup.account_id = new.account_id and status = 'active' ORDER BY id ASC limit 1) + 1, new.amount - (SELECT balance FROM topup_usage WHERE id_topup=(SELECT id FROM topup WHERE topup.account_id=new.account_id ORDER BY id ASC LIMIT 1)));

      ELSE
        INSERT INTO topup_usage (id_topup, balance)
        VALUES((SELECT id FROM topup WHERE topup.account_id = new.account_id and status = 'active' limit 1), new.amount);
      END IF;


      IF ((SELECT amount_ds FROM topup WHERE id =(SELECT id FROM topup WHERE status = 'active' ORDER BY id ASC limit 1)) - (SELECT SUM(balance) FROM topup_usage WHERE id_topup=(SELECT id FROM topup WHERE status = 'active' ORDER BY id ASC limit 1))) <= 0 THEN
          UPDATE topup SET status = 'finish' WHERE id = (SELECT id FROM topup WHERE status = 'active' ORDER BY id ASC limit 1);
      END IF;

    END IF;
  END IF;
  return null;
END;
$$;

这个结果发生在我身上：

id   topup_id  amount
399    322        2
400    322        3

不是这样的：

 id      topup_id    amount
 399       322         2
 400       322         2
 401       323         1
 402       323         6
 403       324         1

有什么建议吗？

标签： postgresql