up vote 1 down vote favorite
1
share [fb]

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?

link|improve this question
100% accept rate
feedback

2 Answers

up vote 2 down vote accepted

You need to use FOR EACH ROW

CREATE TRIGGER triggerPodan AFTER INSERT OR DELETE
ON "Podania" FOR EACH ROW
EXECUTE PROCEDURE aktualizujIloscPodan();
link|improve this answer
Oh man, that is so obvious.... Thanks:) – Miko Kronn Feb 1 at 23:36
feedback
up vote 2 down vote

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"
EXECUTE PROCEDURE aktualizujIloscPodan();
link|improve this answer
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 – Miko Kronn Feb 1 at 22:02
@Miko sorry, I didn't check it. Edited. – Maxim Sloyko Feb 1 at 22:13
@Miko after triggers don't need return value. – Maxim Sloyko Feb 1 at 22:14
Oh, I see. This is about this return... But I still get the message that old is not definied? Why? – Miko Kronn Feb 1 at 22:31
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.