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?
Syntax. CREATE TRIGGER trigger_name [BEFORE|AFTER|INSTEAD OF] event_name ON table_name [ -- Trigger logic goes here.... ]; Here, event_name could be INSERT, DELETE, UPDATE, and TRUNCATE database operation on the mentioned table table_name. You can optionally specify FOR EACH ROW after table name.
INSTEAD OF triggers do not support WHEN conditions. Typically, row-level BEFORE triggers are used for checking or modifying the data that will be inserted or updated. For example, a BEFORE trigger might be used to insert the current time into a timestamp column, or to check that two elements of the row are consistent.
TG_OP. Data type text ; a string of INSERT , UPDATE , DELETE , or TRUNCATE telling for which operation the trigger was fired. TG_RELID. Data type oid ; the object ID of the table that caused the trigger invocation. TG_RELNAME.
You need to use FOR EACH ROW
CREATE TRIGGER triggerPodan AFTER INSERT OR DELETE
ON "Podania" FOR EACH ROW
EXECUTE PROCEDURE aktualizujIloscPodan();
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();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With