| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: AW: auditing tables
We had implemeted the following design in my previous project (a Mortgage Origination System) to accomplish this.
We had marketing tables to keep track of mortgage products, rates etc. These tables were query intensive. Inserts/Updates/Logical deletes were less.
The primary key was made up with an sequence# and a revision number. A row with the revision number of 0 is the latest one. Another column (expiry_date) indicates whether the row is logically deleted or not.
So whenver an update was done, a 'before row update' trigger used to capture the old values, get the max revision number + 1 for that sequence number, and reinsert the row into the same table. So auditing was easy.
Prakash
-----Original Message-----
Sent: Tuesday, January 29, 2002 3:11 PM
To: Multiple recipients of list ORACLE-L
Now you're getting into the realm of Temporal or Time- Databases.
Suppose you want to know what change Fred made on Tuesday. With your design, the audit row only shows what the old value was, not what the new value is. To find that, you have to find either the current production row, OR the next-most-recent change for that row in the audit table.
Finding the next-most-recent row in an audit table is not a lot of fun, and can be a bit of a performance pig.
And suppose the next change is a deletion. A typical way to track that is to record only the PK value of the deleted row. If you do that, then you've lost the 'new' value that Fred put in.
So, if you regularly report on old-and-new values from the audit table, it makes sense to store them both for the same change. My most recent design looked something like this, with one row in AUD_TAB for each row changed, and one row in AUD_COL for each column changed in that row (inserts and updates only):
AUD_TAB
change_id (pk)
table_name
change_type
pk_value
AUD_COL
change_id (fk) (pk)
column_name (pk)
old_value
new_value
Cheers.
-Tom
> On Tuesday 29 January 2002 03:00, Rachel Carmichael wrote:
> > Update -- add two rows to the auditing table -- first with old
> > values and type = O
> > second with all the new values and type =N
> > --- "Foelz.Frank" <Foelz.Frank_at_Scheidt-Bachmann.de> wrote:
> > > What I need is exactly what Oracle doesn't support. Logging "who"
> > > changed "what" in a special area of our database.
> > >
> > > I think triggering the events will be much more specific and more
> > > easy to change.
> > >
> > > In case all our applications use the same database and user, I am
> > > trying to check out
> > > what application is changing monitored tables (i.e.
> > > c:\app\userapp\app.exe is changing table1).
> > > What do you think of that ??
"The whole aim of practical politics is to keep the populace alarmed (and hence clamorous to be led to safety) by menacing it with an endless series of hobgoblins, all of them imaginary." --H.L. Mencken
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas B. Cox INET: tbcox23_at_yahoo.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash INET: prakash.bala_at_cingular.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Wed Jan 30 2002 - 10:57:22 CST
 |
 |