trigger registros postgres para disparadores before auditoria actualizar postgresql triggers

registros - trigger postgresql auditoria



Error de Trigger de PostgreSQL (2)

Debe usar FOR EACH ROW

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

Yo tengo:

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

Cuando intento ejecutar:

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

Parece que no sabe qué es OLD o NEW . ¿Cómo puedo arreglar eso?


Para el activador de delete solo se define el registro OLD y NEW está definido NEW . Por lo tanto, en el código, verifique si el desencadenador se está ejecutando como DELETE o INSERT (variable TG_OP ) y acceda al registro apropiado.

Además, puedes ir sin contar aquí, así:

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