Almost in all stages of my application, the database inserts / updates / deletions are done by the application code (in my case, PHP).
However, in some situations, I am using MySQL triggers to do some calculations and changes to the tables.
Is this a recommended approach? Or is there any best practice rule that only the application should do all the work and not the database?
Thanks for any recommendation.
The database is typically the hardest thing to scale. As a result, at very high scale, one popular strategy is to use only the most basic database features to ensure maximal database performance -- which means no triggers, no foreign keys, no joins (accomplished via denormalization), etc. However, this is only something you have to worry about if you're in, say, the top 1% of the web.
However, simplicity is always a high priority as well. And triggers are often the simplest solution. If you're not going to be anywhere near the top 1% of the web in the near future (and let's be honest -- few, if any of us here are) go ahead and use triggers.
Designing an application for facebook-levels of scale requires far more work and maintenance overhead than writing an application the simple way. And if you don't reach facebook-level traffic, all that work was wasted.