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