Richard Huxton <dev@xxxxxxxxxxxx> wrote: > Michaël Lemaire wrote: > > Richard Huxton <dev@xxxxxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
