I have many tables for which I want to create the audit trail so that at any time I should be able to tell that for the given primary key record what values were before. Means keeping the history. So far I have thought it this way (which may not be a very elegant)...
1. Create the new tables using the structure of the existing tables. *One for each* with AUDIT_ word at begining for name. Add new columns in the AUDIT_* tables like user, date, operation (update/delete).
2. Before a updation happens in the main table record, that record as it is gets inserted into the corresponding AUDIT_ table along with timestamp etc. This way I will be having the UPDATED record in my main table and the old record in the AUDIT_ table. The drawback is that I will be storing the column fields that are not actually changed by the Update statement. Other is performance but that is not the issue with my application at all.
3. I am thinking of using the Trigger to achive the above.
I am not having much of experience in Oracle/Triggers etc. And would like to know your inputs on the plan menstioned above. Please point out modifications to the above mentioned statements and provode some example illustrating the whole audit trail.