PostgreSQL Trigger error

Question

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 OLD or NEW. How can I fix that?

Ichibann · Accepted Answer · 2011-02-01 23:30:58Z

21

You need to use FOR EACH ROW

CREATE TRIGGER triggerPodan AFTER INSERT OR DELETE
ON "Podania" FOR EACH ROW
EXECUTE PROCEDURE aktualizujIloscPodan();

answered Feb 1, 2011 at 23:30

Ichibann

4,4719 gold badges35 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Maxim Sloyko · Accepted Answer · 2018-12-19 22:01:07Z

13

For the delete trigger only OLD record is defined and NEW is undefined. So, in the code, check if the trigger is running as DELETE or INSERT (variable TG_OP) and access the appropriate record.

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();

edited Dec 19, 2018 at 22:01

answered Feb 1, 2011 at 21:49

Maxim Sloyko

16k9 gold badges46 silver badges50 bronze badges

4 Comments

Miko Kronn Over a year ago

OK, maybe I don't need to count... Modified my code and got another error. Can you help? Two things: It does not allow to return NULL (works without return) and it is ELSIF not ELIF

Maxim Sloyko Over a year ago

@Miko sorry, I didn't check it. Edited.

Maxim Sloyko Over a year ago

@Miko after triggers don't need return value.

Miko Kronn Over a year ago

Oh, I see. This is about this return... But I still get the message that old is not definied? Why?

Collectives™ on Stack Overflow

PostgreSQL Trigger error

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related