如何在 PostgreSQL 中编写触发器来将字段与另一个表中的字段进行比较

Question

这是我第一次尝试触发器。我已经了解触发器背后的概念和用法，但是我不太确定如何编写该过程。

基本上，我有一个表，我们将其称为“Money”，其中有一个名为“Max_transaction_amount”的字段，以及另一个名为“Currency_ID”的字段。然后，我有一个名为“Transactions”的表。基本上，我想要实现的是一种机制，每当新交易添加到“交易”表中时，都会进行检查以查看与某种货币相关的所有交易的“计数”不超过“名为“Money”的表中的“Max_transaction_amount”，相对于“Currency_ID”。

对于有数据库经验的用户来说，这似乎是一个非常简单的问题，但是我仍在学习，并且想编写这样一个过程，以便有一个良好的开端。

不幸的是，我无法提供任何代码，因为我对如何执行此操作没有丝毫线索。然而，这就是我“认为”可能起作用的：

• 触发器需要在更新“交易”表时执行
• 该过程通过检查其“Currency_ID”来检查正在添加的记录中的“Max_transaction_amount”。
• 执行 select 语句来查找符合该特定“Currency_ID”的行数
• 将检查“Money”表中的“Max_transaction_amount”字段，以查看
• “Transactions”表中 该特定货币的最大金额是多少，将与该特定货币相关的所有记录的计数与“max_transaction_amount”进行比较，
• 如果它小于或等于最大金额，则保存更改。

任何帮助将不胜感激！

---

我尝试编写以下过程...虽然语法被接受，但如果我尝试超出数量，则不会发生任何情况。有人能引导我走向正确的方向吗？

BEGIN

IF ((SELECT COUNT(Currency_ID) FROM "Transactions" WHERE "Currency_ID" = NEW.Currency_ID) > (SELECT "max_transaction_amount" FROM "Money" WHERE "Currency_ID" = NEW.Currency_ID)) THEN
RAISE EXCEPTION 'Error';

END IF;
END;

Answer 1

不确定我是否理解 max_transaction_amount 的存储方式，但对我来说，听起来您可以通过资金表中的余额列和检查约束来实现此目的。每次将行插入到事务表中时，余额都会更新。

CREATE TABLE money 
(
   currency_id              integer not null,
   max_transaction_amount   numeric(18,2) not null,
   current_balance          numeric(18,2) not null default 0,
   primary key (currency_id),
   check (current_balance <= max_transaction_amount)
);

CREATE TABLE transactions 
(
  transaction_id       integer not null,
  transaction_amount   numeric(18,2) not null,
  currency_id          integer not null,
  primary key (transaction_id),
  foreign key (currency_id) references money (currency_id)
);

现在您只需确保每次插入事务时都会更新 current_balance 。这似乎是规则有意义的罕见情况之一（但也可以在触发器中完成 - 特别是如果您需要进行更多检查）：

CREATE RULE transactions_insert AS 
  ON INSERT TO transactions
    DO UPDATE money 
          SET current_balance = current_balance + new.transaction_amount
       WHERE currency_id = new.currency_id;

现在更新 current_balance 列时，检查约束将确保不超过 max_transaction_amount。

如果您需要覆盖表的 UPDATE，您需要创建另一个规则：

CREATE RULE transactions_update AS 
  ON UPDATE TO transactions
    DO UPDATE money 
          SET current_balance = current_balance - old.transaction_amount + new.transaction_amount
       WHERE currency_id = new.currency_id;

当然这两个规则也可以使用单个触发器函数来完成。