Cascading deletion

[shok411]

Hello

I'm trying to perform a cascading deletion in MS Access but can't figure out the best way to solve my problem.

I'll give you a more simplified example of it:
Tables:

MEMBER(member_id, firstname, surname, email)
MEMBER_VISITED_AREA(member_id, area_id)
AREA(area_id, area)
MEMBER_SPOKEN_LANGUAGE(language_id, member_id)
LANGUAGES(language_id, language)

so let's say a member has has visited 15 areas, of these, they are the only member to have visited 5 of them, they can speak 1 language nobody else can.

Now if I delete the member then the deletion should delete all records referring to the member, but also if an AREA no longer has an association in the MEMBER_VISTED_AREA this should also be deleted (in this case 5 areas should be removed!), the same goes for the LANGUAGES which no longer have an association in the language table (in this case 1).

I realise this will require more than one SQL statement (methinks) but what would be the best way to perform such an action.

Thanks for taking the time to read this long post, I appreciate any suggstions

 

[r937]

interesting question

what i would do is run a "cleanup" query once in a while -- i.e. not as a trigger or similar part of the delete cascade

try forum701 to see if those folks know how to launch a query periodically


rudy

http://r937.com/

 

[swampBoogie]

Access supports cascading foreign keys, so why not use that?

 

[r937]

swampboogie, cascading foreign keys will not delete a "childless parent"

MEMBER ( member_id ... )
MEMBER_VISITED_AREA (member_id, area_id)
AREA ( area_id ... )

delete a row in MEMBER, and the cascade will delete any related rows in MEMBER_VISITED_AREA

but shok411 wanted to delete "childless" rows in AREA after the last related MEMBER_VISITED_AREA was deleted

at least, that's how i understood the problem


rudy