1

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

OM The Eternity
  • 15,694
  • 44
  • 120
  • 182
  • 2
    You shouldn't usually be creating new tables dynamically. The table structure is supposed to be static. What are you trying to do? – Mark Byers Mar 26 '10 at 10:41
  • 1
    are you creating tables instead of adding rows ??? – Jon Black Mar 26 '10 at 10:45
  • I am preparing script for Audit Trial, in this case i am ecountering this situation that , what if new table is created? In this case I will not be available with required triggers for the table to track the data change.. hence finding a way if I can create a trigger as find new table created... Please help me in this concern.. – OM The Eternity Mar 26 '10 at 10:46
  • 1
    Please see OPs other question relating to this: http://stackoverflow.com/questions/2522053/how-to-fetch-the-data-from-binary-log-file-and-insert-in-our-desired-table-in-my/2522097#2522097 – Andy Shellam Mar 26 '10 at 11:19

1 Answers1

3

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.

Community
  • 1
  • 1
Andy Shellam
  • 15,403
  • 1
  • 27
  • 41