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

Delete triggers order in delete cascade (pg 8.3.7).

From
Michaël Lemaire
Date:
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).

This happens with 'after' and 'before' triggers.
I really need the order to be C then B then A.

Is there a reason for the triggers to fire in this order ? Has anyone
an idea to reverse it ?

Thanks in advance.

Michaël Lemaire

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

From
Richard Huxton
Date:
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?

--
   Richard Huxton
   Archonet Ltd

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

From
Michaël Lemaire
Date:
Richard Huxton <dev@archonet.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.

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

From
Richard Huxton
Date:
Michaël Lemaire wrote:
> Richard Huxton <dev@archonet.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.

--
   Richard Huxton
   Archonet Ltd

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

From
Michaël Lemaire
Date:
Richard Huxton <dev@archonet.com> wrote:
> Michaël Lemaire wrote:
> > Richard Huxton <dev@archonet.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.

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

From
Jasen Betts
Date:
On 2009-07-02, Michaël Lemaire <adminsys@rodacom.fr> wrote:
> Richard Huxton <dev@archonet.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.

they come out backwards, live with it.

when you select from the command table do

order by timestamp_column ascending sequence_column descending

and they'll magically come out in the "right" order.