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

Richard Huxton <dev(at)archonet(dot)com> wrote:
> Michaël Lemaire wrote:
> > 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.
>
> You could replace the "on delete cascade" with your own triggers.
> They should fire in the order you want.
>
> You will want a BEFORE DELETE trigger, but you will want it to fire
> after any other before triggers so will need to start its name with
> "z" or some such.

Yes, I think I'll write my own cascade then. I was just hoping a
prettier solution existed.

Thank you for your answers.