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.
Well in 10g the auditing is much better so that can get you the sql executed (but it wont give you a before and after image of the whole record). The other possibility is using logminer to say at midnight (if your system isnt busy) to collate all the dml executed during the day which if I recall correctly can give some info from v$session.
Alternatively stick with your present method but enhance you trigger to query v$session to extract machine and os user i.e.
WHERE audsid = sys_context('USERENV', 'SESSIONID');
If your app uses connection pooling so you cant see the end user then you would have to alter the app to write to a context which the trigger can then extract using SYS_CONTEXT.
beilstwh, I'm not fixed on writing my own Logging module, but I'm inclined to mainly becuase many logic entities in the application are spread over more than one table, and I somehow need to group those together for our audit reports.