For regulatory and fraud requirements, we need to record every change to most of the tables in the DB. The way we did this very successfully with a previous project is to have a copy of each table, identical except that:
- 4 additional columns: DateChanged, UserId, Action (Create, Update, delete), and IP
- The Id pk no longer is unique.
- all other constraints (FK, unique indexes etc) removed.
- the audit tables are in a separate audit schema
At a previous job, the Oracle DBAs wrote a script to automatically generate all this, it did the following:
- Created a new schema called audit if not existing
- Iterate over each table t in the normal schema:
- created a new table in the audit schema with the same table name except prefixed it with a_, e.g. a_t
- Added all the same columns as the original table, plus the 4 additional columns (DateChanged, UserId, IP Address and Action)
- generated and added triggers to the original table (if not already there) to:
- If updating, write a new row to the corresponding a_ table, with action of "Update" containing the pre-updated (old) values in all the columns (the main table will have the new values)
- If deleting, Add a row which is a copy of the main tables row, but action = "Delete".
- If inserting, add the row to the audit schema
NOTE:
- All tables have a ID PK.
- A few tables need to be excluded for performance or because not needed (e.g. the balance table which is updated by a trigger)
The beauty of this system is you can query what changed and when, or who made changes to what, and see the record before and after the change. changes are at the DB row level, not the individual column level.
Has anyone come across something like this for MYSQL 5.6? We don't have a DBA on our team who could write something like this from scratch, but we know enough to modify something similar.
Best Answer
I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.