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 <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> 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" > [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
