Re: protected ON DELETE CASCADE

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