Quite some time ago I was asked by a customer how we could track the changes to a row over time. They had a couple of very important tables for which they wanted to keep track of who changed what.
The interesting thing was that they were not interested in all the columns, only specific, highly important and impactful ones.
At the time, Flashback Data Archive was not an option for them (for reasons I don't remember now) so it was decided that we'd go with a trigger approach to capture the changes for the fields they were interested in.
And true to form, instead of taking the time to write a trigger for each table they wanted to track, I wrote a package that does all the things they would need so that they wouldn't have to think about it as hard next time.
Features
The package has multiple methods including the ability to manage the base objects, including the table into which the audit trail is inserted.
Methods include:
CREATE_AUDIT_TABLE- generates (and optionally executes) the DDL to create the central logging table.DROP_AUDIT_TABLE- generates (and optionally executes) the DDL to drop the central logging table.ADD_TABLE_AUDIT_TRIG- generates (and optionally executes) the DDL that will CREATE the trigger used to audit a table.REMOVE_TABLE_AUDIT_TRIG- generates (and optionally executes) the DDL that will DROP the trigger used to audit a table.ENABLE_AUDIT_FOR_TABLE- generates (and optionally executes) the DDL that will ENABLE the auditing trigger for a specified table.DISABLE_AUDIT_FOR_TABLE- generates (and optionally executes) the DDL that will DISABLE the auditing trigger for a specified table.ENABLE_ALL_AUDIT_TIRGGERS- generates (and optionally executes) the DDL that will ENABLE ALL auditing triggers.DISABLE_ALL_AUDIT_TRIGGERS- generates (and optionally executes) the DDL that will DISABLE ALL auditing triggers.REMOVE_AUDIT_RECS_FOR_TABLE- generates (and optionally executes) the DDL that will remove audit records from the central audit table for a specific table being audited.- You may also specify a date before which all audit records are to be deleted.
REMOVE_ALL_AUDIT_RECS- generates (and optionally executes) the DDL that will remove audit records from the central audit table for all table being audited.- You may also specify a date before which all audit records are to be deleted.
TABLE_IS_AUDITED- checks to see if the supplied table name has an audit trigger that conforms to the naming convention used by the package and returns a boolean.CAPTURE_AUDIT- procedure that is called from the audit triggers to capture and store the changes.
Assumptions
All objects manipulated by this package exist in the current parsing schema.
All triggers that Audit tables will be of the form BUID_<<TABLE_NAME>>_AUD
ANY Triggers that have the above name signature will be affected by this package
Limitations
Currently, the data types that can be audited are limited to
NUMBER
FLOAT
BINARY_FLOAT
BINARY_DOUBLE
VARCHAR2
CHAR
DATE
TIMESTAMP
TIMESTAMP W TIMEZONE
TIMESTAMP WITH LOCAL TIMEZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
CLOB
DataTypes EXCLUDED from audit are:
ROWID
UROWID
BLOB
BFILE
NVARCHAR2
NCHAR
LONG
LONG_RAW
RAW
NOTE: Any varchar2 column greater than 4000 in length will be stored in the audit table's CLOB column rather than in the varchar2 column.
Caveat Emptor
I've made this package available here on GitHub for anyone to use. However, I will say this about using it.
This is probably not the most efficient use of database resources. For a small number of tables and a targeted number of columns, it will likely work just fine, but on a larger scale, you may want to rethink your approach.
For proof of this, you simply need to read Connor McDonald's blog post, A fresh look at auditing row changes.
The current state of the package was aimed at the very specific need of my then-client. There are many things that I would like to do to it to make it more generic and more functional. These changes are outlined in the ISSUES section of the GitHub repository.
As always, your comments and ideas are welcome.