Is there any way to automatically create a trigger on creation of new table in MySQL?

Question

Is there any way to automatically create a trigger on creation of new table in MySQL?

Answer 1

As I've pointed out in your other question, I think a process and security review is in order here. It's an audited database, so nobody (especially third-party service providers) should be creating tables in your database without your knowledge.

The issue you've got is, as well as the new table being created, you will also need to have another table created to store the audited/changed records, which will have an identical structure as the original table with possibly a time/date and user column. If a third-party provider is creating this table, they won't know to create the auditing table, therefore even if you could generate your triggers dynamically, they wouldn't work.

It's impossible to create a single table that will hold all changes record for all other tables in your database because the structure between tables inevitably differs.

Therefore: make all change requests (e.g. providers wants to create TableX, they submit a change request (including the SQL script) explaining the reason for the change) to yourself and/or your team.

You execute the SQL on a test copy of your database, and use the same structure to create another table to hold the modified records.

You then create and test the necessary triggers, generate a new SQL script to create the two tables and your triggers and execute that on your live database. You give your provider permissions to use the new table and away they go.

Everyone's happy. Yes, it may take a little while longer, and yes you'll have more work to do, but that's a hell of a lot less work than is required to try and parse query logs to re-create records that have already been changed/deleted, or parse the binary log and keep up-to-date with every change, and modify your code when the format of the log file changes etc etc.