database - 在 Postgresql 中创建函数，将数据插入已更新的表行

Question

我一直在尝试弄清楚如何创建一个触发器/函数来计算表行更新时的值。这是我正在创建的 DVD 租赁数据库。我有一些数据库经验，但我才刚刚开始使用触发器和函数。我想要发生的是，当员工插入电影归还日期时，它将采用租借日期并从归还日期中减去它，然后将天数差乘以每天租金 1.99 美元。我有计算那个的表达式。我想要一个触发器来运行表达式并将结果插入到该表中。

例如:

客户归还电影，员工在租金表中输入归还日期，更新此行时，触发器将运行计算欠款金额的表达式，并将结果插入租金表行中的 amount_owed 列。

这是我一直在尝试的一些事情，触发器执行了函数，但之后我得到了一个错误。

函数代码:

CREATE OR REPLACE FUNCTION update_cus_balance(integer)
  RETURNS void AS
$BODY$
BEGIN

UPDATE rent
SET amount_owed=subquery.balance

FROM (SELECT date_part('day'::text, rent.return_date - rent.rent_date) *         2.99::double precision AS balance

from rent) AS subquery
WHERE rent.rent_id=rent_id;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION update_cus_balance(integer)
  OWNER TO postgres;

触发代码:

CREATE TRIGGER update_balance
AFTER UPDATE
ON rent
FOR EACH ROW
EXECUTE PROCEDURE amount_owed();

当我尝试添加 return_date 并保存更改时，PGadmin III 只是说“发生错误”。

Answer 1

就像 Jakub 指出的那样，您需要一个 BEFORE UPDATE 触发器才能完成这项工作。这样的触发器有两个重要的特征:

1. 您有一个 OLD 和一个 NEW 变量，它们保存更新前行的值以及更新后的样子。您可以操作 NEW 变量，然后这些更改将保存到表中。
2. 如果某些更改不合适，您可以中止整个更新。

对于您的情况，触发函数看起来有点像这样:

CREATE OR REPLACE FUNCTION update_cus_balance(<strike>integer</strike>)
  RETURNS <strike>void</strike> <b>trigger</b> AS -- A trigger function should ALWAYS return <b>trigger</b>
$BODY$
BEGIN
  -- Calculate rent if the return date is set or changed
  IF NEW.return_date <> OLD.return_date THEN 
    IF NEW.return_date = NEW.rent_date THEN -- Same day returns
      NEW.amount_owed := 2.99;
    ELSE
      NEW.amount_owed := (NEW.return_date - NEW.rent_date) * 2.99;
    END IF;
  END IF;

  -- Trigger function should ALWAYS return NEW to succeed.
  -- Return NULL to make the update fail.
  RETURN NEW; 
END
$BODY$ LANGUAGE plpgsql;

触发器应该是:

CREATE TRIGGER update_balance
BEFORE UPDATE ON rent
FOR EACH ROW EXECUTE PROCEDURE update_cus_balance();

请注意，我假设 rent_date 和 return_date 是 date 类型(顾名思义)。如果它们是 timestamp，您可以创建两个变量和 EXTRACT() 租赁日期并将其归还。

触发器和函数在 PostgreSQL 中不是很复杂，但请阅读 excellent manual仔细。