MySQL history or transaction log table updated by triggers

Question

Hi all, my question is about a database history or transaction log table which is currently updated by mysql procedure. The procedure is called by mysql trigger every time when we keep a history of an appropriate table in during insert, update or delete actions. As far as we have lots of tables for each of them we need to create a separate trigger e.g. for "accounts table" we need to create "accounts_insert, accounts_update and accounts_delete" triggers.

The problem is every time when we alter "accounts table" we have to modify appropriate triggers as well.

Is there any way to avoid that manual work? Would it be better to implement it in application layer/code?

Answer 1

There are no 'global' triggers if that's what you're thinking about.

Application side logging is one possible solution. You'll want to do this within transactions whenever possible.

Other possible approaches:

• Create a script that will update your triggers for you. Can be fairly easy, if your triggers are generally similar to each other. Using information_schema database can be helpful here.
• Parse general query log (careful, enabling this log can have large negative impact on server's performance)