CODEWITHSUNDEEP
postgresqltriggersdatabase-trigger
Eugen Konkov
Eugen Konkov

Reputation: 25153

Why referenced data is not available while it is still referenced?

I have next relations (I left only related fields):

CREATE TABLE "operations" (
  "id" serial NOT NULL,
  "opdate" timestamp DEFAULT current_timestamp NOT NULL,
);

CREATE TABLE "prows" (
  "id" serial NOT NULL,
  "operation_id" integer NOT NULL,
);

ALTER TABLE "prows" ADD CONSTRAINT "prows_fk_operation_id" FOREIGN KEY ("operation_id")
  REFERENCES "operations" ("id") ON DELETE CASCADE ON UPDATE RESTRICT;

CREATE TRIGGER "prow_bd_changesaldo" before delete
  ON "prows" FOR EACH row EXECUTE PROCEDURE make_prow()
;

CREATE FUNCTION "make_prow" ()
 RETURNS TRIGGER
 LANGUAGE plpgsql
 AS $$
    DECLARE _count INT;
     BEGIN

    SELECT count(*) /* OP.OpDate */
      FROM Operations OP
      WHERE OP.ID = OLD.Operation_ID
      INTO _count;

    RAISE NOTICE 'COUNT: %', _count;

    ...

    IF TG_OP = 'DELETE' THEN RETURN OLD; ELSE RETURN NULL; END IF;/**/
    END;/**/
$$

I got that count is zero:

delete from operations ;
NOTICE:  COUNT: 0
CONTEXT:  PL/pgSQL function make_prow() line 1 at RAISE
SQL statement "DELETE FROM ONLY "public"."prows" WHERE $1 OPERATOR(pg_catalog.=) "operation_id""
...

So related row still exists while referenced row is gone. It seems very inconsistent to me and breaks integrity.

Is there any ways to get Operations.OpDate value from make_prow trigger?

Upvotes: 0

Views: 100

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246918

It is not surprising that the DELETE cascades to prows only after the rows in operations have been deleted.

To solve the problem, you can change ON DELETE CASCADE to ON DELETE NO ACTION and define a BEFORE trigger on operations that deletes the dependent rows in prows before the row in operations is deleted. That way the trigger on prows will be called before the row in operations is gone.

Upvotes: 1

Related Questions