This section presents a suite of six scripts, four of which make use of the code generation function being demonstrated on this site.
The Objective is to build a system which
a) saves every pre-modified version of rows from a table each time it is subject to modification, and
b) logs the instance of every modification to a specified table.
Each time the specified table is modified, two things will happen.
A record of the Table_Name, the Date, and the Activity (whether Insert, Update or Delete) will be recorded in the logging table LOG_MODS.
When an existing row is updated or deleted, the pre-modified row will be copied to the relevant Copy (or audit) table (a mirror image of the original specified table), with flags indicating the name of the person making the change, the date, and a flag to show whether the row was Updated or Deleted.
The system requires several new objects, which are created by the scripts below. Example names assume a specified table called 'MY_TABLE':
- A script to add the neccessary audit fields to the specified table - add_audit_fields.sql
- A copy (or audit) table called 'my_table_CPY' - build_cpy_table.sql
- An audit trigger to populate 'my_table_CPY' called 'my_table_AUD'- build_aud_trig.sql
- (A simple table create script will create the logging table LOG_MODS) - build_log_table.sql
- A log trigger to populate the LOG_MODS table called 'my_table_LOG' - build_log_trig.sql
- (A master build script to run all the above) - MASTER_AUDIT_BUILD.SQL
Download each script from the above, or the full suite in zip format here: Master_audit.zip