Solved

Audit Trail

Posted on 2001-06-29
12
1,575 Views
Last Modified: 2008-02-20
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
Javaq
0
Comment
Question by:javaq092999
  • 4
  • 3
  • 2
  • +3
12 Comments
 

Author Comment

by:javaq092999
ID: 6237338
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
0
 
LVL 2

Expert Comment

by:racher
ID: 6237595
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;
IF UPDATING THEN
   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;
   dp_uko_order_allocations_audit.insert_record  
            (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;
   dp_uko_order_allocations_audit.insert_record
         (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


0
 
LVL 1

Expert Comment

by:i020242
ID: 6237652
Hi,


just hooked to the notify list.

Thanks,
J.K
0
 
LVL 34

Accepted Solution

by:
Mark Geerlings earned 100 total points
ID: 6238349
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(:new.inventory_unit,:old.inventory_unit,null,nvl(:old.inventory_unit,'?')),
  decode(:new.purchasing_unit,:old.purchasing_unit,null,nvl(:old.purchasing_unit,'?')),
  decode(:new.pur_inv_multiplier,:old.pur_inv_multiplier,null,nvl(:old.pur_inv_multiplier,0)),
  decode(:new.obsolete_date,:old.obsolete_date,null,nvl(:old.obsolete_date,to_date('01/01/2099','DD/MM/YYYY')))...);

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

Expert Comment

by:ilver
ID: 6241428
Have you considered using the built in audit features ?
Check this: http://technet.oracle.com/doc/server.815/a67772/audit.htm#1156

0
 

Author Comment

by:javaq092999
ID: 6241873
markgeer,

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.

Regards
javaq

PS: ilver : Oracle Database or OS auditing does not provide you the old and new values hence it is of no help to me.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:javaq092999
ID: 6241877
Read "will have another table mentioning" as "will have another record mentioning " --javaq
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6245858
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.
0
 

Author Comment

by:javaq092999
ID: 6247293
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.

Thanks
--javaq
0
 
LVL 34

Expert Comment

by:Mark Geerlings
ID: 6255962
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.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6877692
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.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

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.
http://www.experts-exchange.com/questions/Q.20023290.html
http://www.experts-exchange.com/questions/Q.20121869.html
http://www.experts-exchange.com/questions/Q.20126064.html
http://www.experts-exchange.com/questions/Q.20139990.html
http://www.experts-exchange.com/questions/Q.20143257.html
http://www.experts-exchange.com/questions/Q.20148988.html
http://www.experts-exchange.com/questions/Q.20150711.html
http://www.experts-exchange.com/questions/Q.20158263.html
http://www.experts-exchange.com/questions/Q.20163902.html
http://www.experts-exchange.com/questions/Q.20163903.html
http://www.experts-exchange.com/questions/Q.20181528.html
http://www.experts-exchange.com/questions/Q.20245208.html
http://www.experts-exchange.com/questions/Q.20245590.html
http://www.experts-exchange.com/questions/Q.20251382.html
http://www.experts-exchange.com/questions/Q.20253111.html


To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20143170.html
http://www.experts-exchange.com/questions/Q.20143993.html

PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  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 http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643

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.
 
Moondancer
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.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6883209
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.

Thanks,
Moondancer - EE Moderator
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now