Audit tables

[David Warnock <da...@sundayta.com>]

Hi,

I am looking at OJB and have a couple of queries relating to our use of 
audit tables. For some tables we need to log every change.

Below is a summary of how we do this. Can anyone see any problems 
implementing OJB on top of this kind of structure?

Thanks

Dave

For each table where we need an audit we do the following

1. Have the following standard columns

   Id                         T_Id                    NOT NULL,
   RowVersion                 T_Version               NOT NULL,
   RowTimestamp               T_Timestamp             NOT NULL,
   RowUserId                  T_Id,
   RowAuditAction             T_AuditAction           NOT NULL,

Id is an Integer primary key.

Version is an integer. Triggers check that in all saves it has not been 
changed by another user (ie you must update RowVersion by adding 1, the 
trigger checks new.RowVersion = old.RowVersion+1).

RowAuditAction is 0 = Insert, 1 = Update, 2 = Delete

2. We have a table with the same structure but
a) called TABLENAMEAudit
b) with a primary key of Id + RowVersion
c) no foreign keys and no unique indexes (in most cases we don't have 
any indexes on the audit table)

3. We use beforeUpdate and beforeDelete triggers to insert the old row 
from the main table into the audit table.



-- 
David Warnock, Sundayta Ltd. http://www.sundayta.com
iDocSys for Document Management. VisibleResults for Fundraising.
Development and Hosting of Web Applications and Sites.