Re: protected ON DELETE CASCADE

ok *click* i get it i get it

m


Oliver Elphick wrote:
>
> Murray Hobbs wrote:
>   >
>   >i neglected to show it properly
>   >
>   >have tables A, B, C, D PLUS a few others
>   >
>   >A <- B
>   >
>   >     F
>   >     |
>   >     v
>   >A <- C <- D
>   >     ^
>   >     |
>   >     E
>   >
>   >i want to delete from C and cascade any delete to E or F but not if
>   >there are records in D
>   >
>   >what i have done is to have ON DELETE CASCADE on C's primary
>   >
>   >but force deletes to C through a function that will delete from C only
>   >if there is no records in D
>   >
>   >but i would like to believe there is a better way - a way that does not
>   >require that i do all my deletes through a function
>
> B REFERENCES A ON DELETE CASCADE
> C REFERENCES A ON DELETE CASCADE
> D REFERENCES C ON DELETE NO ACTION
> E REFERENCES C ON DELETE CASCADE
> F REFERENCES C ON DELETE CASCADE
>
> So if there is a deletion in A it cascades to B and C
>
> The deletion in C cascades to E and F, but fails if there is a
> reference in D.  The failure causes a rollback of the transaction and
> thus the deletions in A, B, C, E and F do not happen after all.
>
> --
> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
> Isle of Wight                              http://www.lfix.co.uk/oliver
> PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>                  ========================================
>      "For God hath not appointed us to wrath, but to obtain
>       salvation by our Lord Jesus Christ, Who died for us,
>       that, whether we wake or sleep, we should live
>       together with him."
>                          I Thessalonians 5:9,10