The tack we take is to have a separate table that tracks changes. It does,
of necessity, contain the same fields as the original record (including the
ID of the records being modified). It also contains fields that specify
whether the record was deleted or updated, when, and by whom. There is also
a text field containing the names of the fields that were changed. We
capture the "before" data, since the original record has the "after" data.
(At least I think we do, this part of the system is rather murky.)
If there is only one field that you need to track, that simplifies things a
lot. You won't need the list of changed fields, etc.
This is probably the only way to do it, even though it is expensive. Our
transaction volume is very low, and our database is relatively small.
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341