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- 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: Thomas B. Cox 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bala, Prakash 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).
