I have a 3 tables that look like this:
(source: InsomniacGeek.com)
On the foreign keys I have set cascade deletes. Right now, when I delete a record in the Folder table, only the related record in the FolderItem is deleted.
This is expected and correct.
What I would to accomplish is when I delete a record in the Folder table, the corresponding records in the FolderItem and the Item table should be deleted.
How do I solve this? By adding a trigger that deletes all instances of Item with the FolderID in question? Or is there any better solution?