Re: Delete triggers order in delete cascade (pg 8.3.7).

Richard Huxton <dev(at)archonet(dot)com> wrote:

> Michaël Lemaire wrote:
> > Hi all.
> >
> > I've come across a problem with delete cascade.
> >
> > I have three tables A, B and C.
> > Table B has a foreign key on A with delete cascade.
> > Table C has a foreign key on B with delete cascade.
> > So, we have this reference chain: C->B->A
> > All three tables have an 'on delete' trigger.
> >
> > My problem is, when I delete a row from A, the delete triggers are
> > fired in the order A then B then C, which is the opposite of what I
> > expected (the row from B should be deleted before the A one, or the
> > reference constraint would break).
>
> The "on delete cascade" are (sort of) implemented with system
> triggers. So deleting a row from A triggers a delete on B where
> fkey=X and so on.
>
> > This happens with 'after' and 'before' triggers.
> > I really need the order to be C then B then A.
>
> Why? What are you trying to do?

The delete triggers add 'command' rows in another table to notify
another server of data changes (kind of a replication system but with
data convertion).

This other server's database doesn't have delete cascades (I can't
change this for compatibility with other scripts). So delete commands
must be issued in an order that don't break foreign keys.