PostgreSQL 触发器错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4868060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL Trigger error
提问by Miko Kronn
I have:
我有：
CREATE OR REPLACE FUNCTION aktualizujIloscPodan() RETURNS TRIGGER AS
$BODY$
DECLARE
n integer;
sid integer;
BEGIN
sid=0;
IF (TG_OP='INSERT') THEN
sid = NEW."studentID";
ELSIF (TG_OP='DELETE') THEN
sid = OLD."studentID";
END IF;
n = COALESCE ((SELECT count("studentID") as c
FROM "Podania" WHERE "studentID"=sid
GROUP BY "studentID"), 0);
UPDATE "Studenci" SET "licznikpodan" = n WHERE "ID"=sid;
END;
$BODY$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS triggenPodan ON "Podania";
CREATE TRIGGER triggenPodan AFTER INSERT OR DELETE
ON "Podania"
EXECUTE PROCEDURE aktualizujIloscPodan();
When I try to execute:
当我尝试执行时：
DELETE FROM "Podania"
I get
ERROR: record "old" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
CONTEXT: PL/pgSQL function "aktualizujiloscpodan" line 11 at assignment
********** B??d **********
ERROR: record "old" is not assigned yet
Stan SQL:55000
Szczegó?y:The tuple structure of a not-yet-assigned record is indeterminate.
Kontekst:PL/pgSQL function "aktualizujiloscpodan" line 11 at assignment
It seems like it doesn't know what is OLDor NEW. How can I fix that?
它似乎不知道什么是OLD或NEW。我该如何解决？
回答by Ichibann
You need to use FOR EACH ROW
你需要使用 FOR EACH ROW
CREATE TRIGGER triggerPodan AFTER INSERT OR DELETE
ON "Podania" FOR EACH ROW
EXECUTE PROCEDURE aktualizujIloscPodan();
回答by Maxim Sloyko
For the deletetrigger only OLDrecord is defined and NEWis undefined. So, in the code, check if the trigger is running as DELETEor INSERT(variable TG_OP) and access the appropriate record.
对于delete触发器，只有OLD记录已定义NEW且未定义。因此，在代码中，检查触发器是否作为DELETE或INSERT（变量TG_OP）运行并访问适当的记录。
Besides, you can go without counting here at all, like this:
此外，您可以完全不计算这里，如下所示：
CREATE OR REPLACE FUNCTION aktualizujIloscPodan() RETURNS TRIGGER AS
$BODY$
DECLARE
n integer;
BEGIN
IF TG_OP = 'INSERT' then
UPDATE "Studenci" SET "ilosc_podan" = "ilosc_podan" + 1 WHERE "ID"=NEW."studentID";
ELSIF TG_OP = 'DELETE' then
UPDATE "Studenci" SET "ilosc_podan" = "ilosc_podan" - 1 WHERE "ID"=OLD."studentID";
END IF;
END;
$BODY$
LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS triggenPodan ON "Podania";
CREATE TRIGGER triggenPodan AFTER INSERT OR DELETE
ON "Podania" FOR EACH ROW
EXECUTE PROCEDURE aktualizujIloscPodan();
