Audit Table Strategies

We need to audit all updates to numerous tables. We will have a companion audit table for each audited data table. (We could probably make a single audit table work, but don't want to.) The audit tables will be managed by triggers on the data tables.

Table A (PK_COL, DATA_COL_1, DATA_COL_2, LAST_UPD_USR, LAST_UPD_TS)

I am considering the following two formats:

Table A_AUD (UPD_USR, UPD_TS, AUDIT_FUNC[I,U,D] not null, PK_COL_AUD not null,

            DATA_COL_1_OLD, DATA_COL_1_NEW, DATA_COL_2_OLD, DATA_COL_2_NEW,
            PK(UPD_USR, UPD_TS))

or

Table A_AUD (UPD_USR, UPD_TS, AUDIT_FUNC[I,U,D] not null, PK_COL_AUD not null,

            DATA_COL_NAME, DATA_COL_OLD varchar2, DATA_COL_NEW varchar2,
            PK(UPD_USR, UPD_TS, DATA_COL_NAME))

The first has a primary key of timestamp and user (timestamp alone would probably work), operation == update/insert/delete, the primary key of the record being audited, and the previous and new value for each column in the audited record. Obviously, new values are null on delete and old values are null on insert. This for each of many tables, twice duplicating each data column.

The second approach has one old-value column and one new-value column in the audit table, with the column name added to the key. So, there is a record for each column updated in the record being audited, with no audit record for columns that are unchanged. This includes null-in and null-out, so null columns aren't audited on insert or delete. In this case, all data is converted to text, and the type of the data columns is varchar2(longest column). In our case, the longest columns is only about 250 for one table and 100 or less for all others.

I am interested in preferences and trade-offs between these two strategies and suggestions for improvements and alternatives. Also ideas for storage strategy; we will probably just create a separate tablespace for all audit tables.

The data tables are relatively small and relatively static in size; some are also static in content. In addition we will have log tables that store continuous streams of data; these will not be audited and will also require separate tablespaces. The logs will require an archive/purge strategy; the audits will probably be kept on-line.

I could not find any written material on audit strategies, but such pointers would also be welcome.

-- 
Best regards,
Michael Krantz
mikek_at_ethos.net