Not every table or every column is going to need to a record of changes, but when you build the audit system, it easier to start with everything and pare down from there as things aren’t needed or the performance/storage costs get too high.
- Every table will have a change table behind it, in the same database.
- The name of the change table will be orginal_chg
- The first column of the change table will be chg_id (bigserial) and that will be the primary key
- The second column will be called chg_activity and it will be an enum (‘CREATE’,‘UPDATE_BEFORE’,‘UPDATE_AFTER’,‘DELETE’)
- The third column will be called chg_activity_date and it will default to the date the row was created.
- The fourth column will be called chg_user and will default to the current_user
- The rest of the columns will be in the exact same order and of the exact same datatype as they were in the original table.
To build the chg_tables,
- Take the script the builds the original tables and copy it to a new script.
- Add _chg to the end of the table names
- Remove the unique constraints.
- Remove the foreign key constraints (the foreign keys will be correct when inserted and will stay correct until a row in the original db is deleted).
- Remove the default values (we want what is inserted or updated not the default) .
- The old primary key “id” must not be null
- Add the new columns specified above to the front of the tables
- Add an index to the following columns at a minimum
To build the triggers,
We don’t want to have to write these out by hand, rather what we want is a script that connects to the DB and gets a list of columns in a table then builds the triggers before writing them to a .sql file.
This is especially import if we add more tables/columns or we decide that we only want to log changes, because in the first case all you have to do is to rerun the script, and in the second the if statements get really long and it would be easy for a human to mess up.