Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1598
  • Last Modified:

Audit Trail

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.

Many Thanks
  • 4
  • 3
  • 2
  • +3
1 Solution
javaq092999Author Commented:
Please note that I am looking for GENERIC solution preferably. For example a trigger that can work on all the tables I have for making the record copying etc. --javaq
I have used two methods in the past. The first is the one you mentioned where you have an audit of the entire record.
As you say the disadvantage is that you audit all the columns that have not changed, the positive side is that it is very easy to reconstruct the record at a cetain point in the past.

The second method I have used just stores the changed columns in the audit table. The advantage is obvious, the disadvantage is that the database trigger is more comples.
Here is an example, the primary key in uko_order_allocations is (fm_order_id, fund_code)

The two tables :

CREATE TABLE uko_order_allocations
 (fm_order_id                 NUMBER(8,0) NOT NULL
 ,fund_code                 VARCHAR2(10) NOT NULL
 ,shares_volume           NUMBER(20,0) NOT NULL
 ,shares_allocated            NUMBER(20,0) NOT NULL
 ,shares_outstanding          NUMBER(20,0) NOT NULL
 ,shares_natural_outstanding     NUMBER(20,4)
 ,status                 VARCHAR2(1) NOT NULL

CREATE TABLE uko_order_allocations_audit
 (change_type       VARCHAR2(1) NOT NULL
 ,changed_by       VARCHAR2(30) NOT NULL
 ,timestamp       DATE NOT NULL
 ,fm_order_id       NUMBER(8,0) NOT NULL
 ,fund_code       VARCHAR2(10) NOT NULL
 ,column_name       VARCHAR2(32)
 ,old_value       VARCHAR2(2000)
 ,new_value       VARCHAR2(2000)

I then have an after row trigger on uko_order_allocations
that does the following for each column in the table, the example shows two columns being checked. Note that the check is more complex if the column can have NULL values.
dp_uko_order_allocations_audit.insert_record (r_record_audit, FALSE) is just a packaged procedure that takes a ROWTYPE as a parameter and inserts it into the table, the FALSE is to say do not commit the insert (the database trigger itself will do the commit)

Inside the database after row trigger
r_record_audit.changed_by     := USER;
r_record_audit.timestamp     := SYSDATE;
   r_record_audit.change_type     := 'U';
   r_record_audit.fm_order_id     := :new.fm_order_id;
   r_record_audit.fund_code     := :new.fund_code;
 IF :new.shares_outstanding != :old.shares_outstanding THEN
   r_record_audit.column_name     := 'SHARES_OUTSTANDING';
   r_record_audit.old_value     := :old.shares_outstanding;
   r_record_audit.new_value     := :new.shares_outstanding;
            (r_record_audit, FALSE);
 END IF ;  

 IF (:new.shares_natural_outstanding != :old.shares_natural_outstanding) OR
    (:new.shares_natural_outstanding IS NULL AND :old.shares_natural_outstanding IS NOT NULL) OR
       (:new.shares_natural_outstanding IS NOT NULL AND :old.shares_natural_outstanding IS NULL) THEN
      r_record_audit.column_name     := 'SHARES_NATURAL_OUTSTANDING';
      r_record_audit.old_value          := :old.shares_natural_outstanding;
      r_record_audit.new_value          := :new.shares_natural_outstanding;
         (r_record_audit, FALSE);
 END IF ;  
If a delete occurs I take a copy of every column

This is not an easy solution, but it works and uses less storage space.
It's proved very useful when a user claims not to have changed anything, but I can prove that he has!!

Hope it helps or gives you some more ideas


just hooked to the notify list.

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Mark GeerlingsDatabase AdministratorCommented:
I usually use a "sparse" approach to audit data that is in between the two options already suggested.  This avoids the problem of massive amounts of data that can accumulate if you capture the entire record each time a change is made.  This also makes working with the audit records easy, since only the old values appear in each audit record(the new value is always available from the next audit record or the current record).

To use this approach, create an audit table with the same columns as the base table plus columns to record the timestamp and user_id (if they are not already part of the base table).  Most columns in the audit table should be nullable - the exceptions are the primary key column(s), timestamp and user_id.

Then create a row-level after-update trigger on the base table to populate the audit table.  This trigger should fill the primary key column(s), timestamp and user_id for each record, but only populate the old value(s) for the column(s) that actually change.  Here is an excerpt from the trigger that does this for our item_master table:

insert into item_master_history
 (part_nbr, date_modified, modified_by,
  inventory_unit, purchasing_unit, pur_inv_multiplier,
  obsolete_date, ...)
values(:new.part_nbr, :new.date_modified, :new.modified_by,

Decode is used so that if the value of a column is changed, the old value is saved, otherwise a null is inserted for that column.

Note that for columns in the base table where nulls are allowed, we have chosen to hard-code some defaults ("?" for varchar2 fields, 0 (zero) for numeric fields and January 1, 2099 for date fields) when the value is changed to a non-null value.  Otherwise the "old" value of null would not be noticeable in our audit records, since a null in the audit record means the column value was not changed.
Have you considered using the built in audit features ?
Check this:

javaq092999Author Commented:

Your scheme is good but seems like have some problem. Please put some light on it.

Assume there is a VARCHAR2 field which is right now null. I modify this to a string "?" and due to the trigger you have proposed a "?" will be stored in my audit table as the old value is null. So far fine. But notice that I delibrately changed my varchar2 value to "?". Now if I again change the value from "?" to "Hi Markgeer" then the adit table will have another table mentioning the old value as "?". Now the OBVIOUS problem is that how the developer will check if this "?" in the audit table means the old value OR the old value was null (based on your assumption). Same situation I can put for the NUMBER data type (you are proposing null->0). How do you handle such situations.


PS: ilver : Oracle Database or OS auditing does not provide you the old and new values hence it is of no help to me.
javaq092999Author Commented:
Read "will have another table mentioning" as "will have another record mentioning " --javaq
Mark GeerlingsDatabase AdministratorCommented:
You are correct - the difficulty with this approach to auditing is how to handle null values.  (Maybe it is better to not allow nulls at all, as some database experts propose.)  In our system, a value of "?" is not considered to be a valid value in varchar2 fields, so we can safely use that to indicate that the previous value was null.  May be a different character (or character string) is better in your system to indicate a null.  I do like this "sparse" approach to storing audit data though, since it greatly reduces the volume of data to store compared to other options, and with the "decode" command it is quite easy to implement.
javaq092999Author Commented:
Hi Markgeer,

The "sparse" approach is correct in specific rather generic. The other major issue I can think of is the actual auditing. This is essentially not a problem but a uneasy solution from developer point of view. Also from performance point of view. Why? Read on. Imagine there is a record and there there has been THREE updation on it. Means there are three records in the audit table. Now if I go to the third audit record and want to see the values (changed as well as not changed, means create the record view at that time). I certainly see the changed values VERY easily but the old values not. No problem as this is the way the sparse approach works. Now to get the old values I need to make table lookup for the record which is just before this third record ordered by the timestamp. The some of the values in that record might be null (NOT CHANGED) and hence I need to go back to the previous one and so on till the latest record values which is there in my main table. This process might become a developer, maintenance and performance issue. The scheme is very fine for knowing the values that have changed w.r.t. current record. Please put your comments.

Mark GeerlingsDatabase AdministratorCommented:
For our system, this sparse approach works very well.  We use it for both purchase order lines and item master records.  Our audit screens display the current record at the top of the screen and the audit records in a scrollable area below that, each with only the old value(s) only for the column(s) changed at that time plus the timestamp and user ID.  It is not a development or performance problem in our system.
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why.  This helps all involved, as well as future persons who may access this item in the future to seek help.

To view your open questions, please click the following link(s) and keep them all current with updates.

To view your locked questions, please click the following link(s) and evaluate the proposed answer.

------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link

Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made.  Expert input is always appreciated to determine the fair outcome.
Thank you everyone.
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
Thank you for returning and finalizing this question.

Please take an extra moment when you grade questions less than an "A" to comment as to why.  This helps not only the experts who have helped you, but also others in the future who may access this question to get help.  If you've found alternative solutions to achieve your goal and add it here, it further adds value to this item which has not moved to our PAQ (Previously Asked Question database).  Anytime that you need our help, for example, if the grade was chosen in error, let us know by posting a zero point question in the Community Support with the URL to the question involved, we're happy to help.

Moondancer - EE Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now