sql - 记录 "old"尚未分配

Question

我已经创建了这个触发器:

CREATE TRIGGER trig_update 
AFTER INSERT
ON "delivery" FOR EACH ROW
EXECUTE PROCEDURE premium1();

有了这个功能:

CREATE OR REPLACE FUNCTION premium1() 
RETURNS trigger AS $$
BEGIN
IF (TG_OP='INSERT') THEN
IF(select sum(amount) from delivery where cli_num=OLD.cli_num group by cli_num) > 500.00 THEN
    UPDATE client SET Type ='Gold' WHERE cli_num=OLD.cli_num;
END IF;
END IF;
RETURN NULL;
END;
$$LANGUAGE plpgsql;

在表传递中插入新值时出现以下错误:

ERROR: record "old" is not assigned yet Stan SQL:55000

有什么帮助吗？

Answer 1

在 INSERT 触发器中 OLD 不可访问/可见，因为该记录还不在数据库中，因此没有什么可以与之比较。您必须将其更改为 NEW。

来自fine manual:

For a row-level trigger, the input data also includes the NEW row for INSERT and UPDATE triggers, and/or the OLD row for UPDATE and DELETE triggers.