记录“老”未分配尚未
问题描述:
我创造了这个触发:记录“老”未分配尚未
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
对此有何帮助?
答
CREATE OR REPLACE FUNCTION premium1()
RETURNS trigger AS $$
BEGIN
IF (TG_OP='INSERT') THEN
IF(select sum(amount) from delivery where cli_num=NEW.cli_num group by cli_num) > 500.00 THEN
UPDATE client SET Type ='Gold' WHERE cli_num=NEW.cli_num;
END IF;
END IF;
RETURN NULL;
END;
$$LANGUAGE plpgsql;
答
在AFTER
触发器OLD
不可访问/可见,因为该记录还没有在数据库中,所以没有什么可以比较它。您必须将其更改为NEW
。
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.
+0
感谢您的答复,卡米尔 – Cvg
非常感谢,现在的工作! – Cvg