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.
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
