Logging DML

Hi Everyone,

We are looking for a good mechanism that will log DML changes in the database.
We work with Oracle 9i and will soon upgrade to 10g.

There are 2 main requirements for this mechanism:

• All data changes made by users must be logged. Our application is based on a single Oracle user/schema and its users are distinguished by their IDs. We need to know who made a specific change and when.
• We need to be able to get flexible log reports, from both log and regular tables’ data.

Currently, we create a log table for each table that can be changed, and on any UPDATE or DELETE, a trigger copies the entire updated or deleted record to the log table. INSERTs don't need to be logged.

The main disadvantages of this mechanism are:

• An entity (user, order, transaction etc.) is not always stored in a single table, rather it is being stored in 2 or more one/many-to-one/many tables. It’s very difficult to create a report of all changes made to a specific entity in chronological order.
• It is very difficult to create a query that returns all changes made by a specific user in a specific date range.
• Storing the ID of a user performing a deletion cannot be done by the log trigger like we do in UPDATE (:NEW.USER_ID).
• When a base table is heavily updated, the log mechanism significantly slows the update operation.
• The entire record is copied to the log table, no matter how many columns have been updated.

Before we start redesigning our own mechanism, we wanted to check solutions that already exist in the market. We would appreciate any input on this issue.

There are definately alot of opinions on auditing. I think it would be good to know a couple things first.

How often will you need to dig through the audit logs?
Is space or performance an issue?
How easy does digging through the audit log need to be?

My preference is trigger based auditing.

It can be as simple as 1 audit table with

source table
when_changed
who_changed
data_before_change

If you don't have to audit often, this is a fairly simple way to just store what you need.

If you audit often and space is not an issue, copy the data into a structure that looks exactly the same (table_source and table_source_aud for example), with the addition of a Changed_by and a Change_date_time column. Then you can just outer join the two tables together w/ nvl(changed_by,'Current') to distinguish current rows from previously modified rows.

Ken