When cleaning out old data from a set of database tables, is it safer to have the logic in the application or in a database trigger?
I am upgrading an application that I wrote a while back (and in a hurried fashion) and one of the things I want to clean up first is ensuring that data which is no longer being used is propmtly deleted. Currently this is being done in my application itself via a number of SQL calls to various tables. Some of these queries take a bit of time to run and as the application is PHP driven, I don't want users having to wait too long.
I was therefore thinking about creating a database trigger on one table that would merrily clean up all the other tables and then allow the single row delete to occur from my master table.
I am considering these pros and cons at the moment:
- Database trigger
- (+) Invisible to the application
- (+) Simplifies application logic - new objects can be simplified
(-) Adds complexity to overall maintenance - have to maintain code and triggers during table changes etc.
Application Logic
- (+) Keeps all maintenance tasks in one place
- (-) Longer page loads on certain tasks
- (-) More chance of a request timing out - orphaning data
If you have any further insight, considerations I haven't thought of here, experience with one vs the other or can point me towards some reading on the matter I would love to hear it.
Edit: I originally thought that my pages would run quicker, but if I am creating a Before Delete trigger, will the page loads be the same - as in the application sends a tiny query through but it won't be actioned until the entire set of statements in the trigger complete?