Thread: Foreign key reference causes invalid DELETE trigger calls

I need to log table "foo" real deletes.

"foo" has foreign key relation established but no data exists.

Postgres 8.1 calls "foo" delete trigger when record is deleted from master
table "klient". Why ?

How to modify the following code so that record is inserted into serveri
table only when records are really deleted from foo table ?
Is it possible to add some check into trigger code?

CREATE TABLE serverti ( notice char(50));

CREATE FUNCTION setlastchange() RETURNS "trigger" AS $$
BEGIN
INSERT INTO serverti values ('changed');
RETURN NULL;
END$$  LANGUAGE plpgsql;

CREATE table klient ( kood integer primary key );

CREATE TABLE  foo (
  klient char(12) NOT NULL,
  toode char(20) NOT NULL,
  CONSTRAINT foo_pkey PRIMARY KEY (klient, toode),
  CONSTRAINT foo_klient_fkey FOREIGN KEY (klient)
      REFERENCES klient (kood) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
) ;

CREATE TRIGGER foo_trig
  BEFORE INSERT OR UPDATE OR DELETE ON foo
  FOR EACH STATEMENT
  EXECUTE PROCEDURE setlastchange();

insert into klient values (1);
-- Next line causes execution of foo_trig. Why ?
delete from klient where kood=1;

Andrus.

"Andrus" <eetasoft@online.ee> writes:
> Postgres 8.1 calls "foo" delete trigger when record is deleted from master
> table "klient". Why ?

Because you have an ON DELETE CASCADE.  That leads to a DELETE ... WHERE ...
on the slave table; whether any records actually get deleted depends on
what the DELETE finds.  This is a general hazard of using STATEMENT
triggers: you have no info about whether the statement actually did
anything.  (It's rather silly to imagine that a BEFORE STATEMENT trigger
would have any way to know that anyway.  We currently don't tell an
AFTER STATEMENT trigger anything either; though that may change
someday.)

            regards, tom lane

> Because you have an ON DELETE CASCADE.  That leads to a DELETE ... WHERE
> ...
> on the slave table; whether any records actually get deleted depends on
> what the DELETE finds.  This is a general hazard of using STATEMENT
> triggers: you have no info about whether the statement actually did
> anything.

Tom, thank you.

I try to implement table level replication in my application.
Last table change time is written to control table by trigger.
Client finds the tables which are changed after last login and re-loads the
whole tables. Unfortunately, ON DELETE CASCADE tables are replicated always
in this case
which slows down my application startup.

Should I use foo_trig as row level trigger to detect only real row deletion
?

> (It's rather silly to imagine that a BEFORE STATEMENT trigger
> would have any way to know that anyway.  We currently don't tell an
> AFTER STATEMENT trigger anything either; though that may change
> someday.)

I tried following code:

insert into klient values (1);
update klient set kood=2 WHERE kood=3;

In this case, foo_trig trigger is NOT executed.

So BEFORE UPDATE ROW trigger is NOT executed when there are no rows to
update,
but BEFORE DELETE ROW trigger IS executed when there are no rows to delete!

Why is this behaviour inconsistent ?

Andrus.

"Andrus" <eetasoft@online.ee> writes:
> I tried following code:

> insert into klient values (1);
> update klient set kood=2 WHERE kood=3;

> In this case, foo_trig trigger is NOT executed.

Well, yes, because that UPDATE didn't change any rows of the master
table.

            regards, tom lane