Re: AW: auditing tables

Tom,

Well, I didn't include a design with my post.

Audit tables always get their own PK in my book, along with a datestamp recording the time of the change and column indicating who made the change ( if that's available ).

The old value(s) is/are always in the audit table, the new value is always in the production table.

If a row is deleted, the whole thing goes in the audit table.

My purpose for these has been for occasional auditing. I use code ( Perl, or course ) to generate the proper DDL and triggers for all tables I wish to audit, placing them in their own tablespaces.

Your implementation of a separate table for column changes is intriguing, I'll compare it with what I normally do next time someone thinks they want their stuff audited.

If someone wants to query this stuff on a regular basis, it needs to be designed and constructed appropriately.

At that time I take Snodgrass and Kimball off the bookshelf. :)

Jared

On Tuesday 29 January 2002 12:10, Thomas B. Cox wrote:
> Now you're getting into the realm of Temporal or Time-
> Oriented 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: Jared Still
  INET: jkstill_at_cybcon.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).