Re: AW: auditing tables

From: Thomas B. Cox <tbcox23_at_yahoo.com>
Date: Tue, 29 Jan 2002 12:27:19 -0800
Message-ID: <F001.003FF686.20020129121050@fatcity.com>

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

Jared Still <jkstill_at_cybcon.com> wrote:
> I don't think you need two rows for updates. The old values
> will be in the audit table, the new ones are in the production
> table.
> At least that's the way I've always done it.
> Is there some other reason for saving both in the audit table?

> 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 ??

Thomas B. Cox "Saepe in errore sed numquam in dubito" tbcox23@yahoo.com http://www.geocities.com/tbcox23/

"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

Do You Yahoo!?
Great stuff seeking new owners in Yahoo! Auctions! http://auctions.yahoo.com

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

Received on Tue Jan 29 2002 - 14:27:19 CST