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.


Cheers, Kris

Can only give you what I do which is..

If it suits I use cascade on the relation which takes care of it.

If it doesn't suit, I use an on_after_delete method in my model to clean up the orphaned record.

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?

Customer Table
address_id (foreign key to address-->id)
on update cascade

Addresses Table
id
country
city

nothing -- you need to set ON DELETE CASCADE instead

ON UPDATE CASCADE means if you change the value of a PK, the related FKs will also be changed

no, if you want them set to null then you have to use ON DELETE SET NULL