When handling delete operations in an application, are there any recommendations/standards regarding performing the delete in associated tables using a trigger vs processing it in your application code?
For example, if I have an address table and the user deletes the address, is it better to clean up the associated tables that refer to the address (through a foreign key) using a trigger or in the application code itself?
The application that I'm working with uses MySQL 5.5 if it makes any difference.
Interesting, I had not heard about cascading updates/deletes before. If I understand them correctly, I have a question: If I set an ON UPDATE CASCADE relationship and the entity that the foreign key refers to is deleted, what happens? Does the value for the foreign key automatically get set to NULL?
For example, if I have the following and an address is deleted, will the customer-->address_id for any rows that refer to that address automatically be set to NULL?
address_id (foreign key to address-->id)
on update cascade