Auditing Data Change History

·

4 min read

Play this article

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.

  1. 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.

  2. 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.

Did you find this article valuable?

Support Doug Gault by becoming a sponsor. Any amount is appreciated!