I have been recently giving some thought to the issue of keeping a record of the data edits made by a user so we can find out who changed what and when. In its simplest form I have stored procedures launched by triggers that collect the username, date, tablename, fieldsname, before and after values and saves these into a memo field in a history table. Search capabilities are somewhat limited but it works.

In another case I save this data into a structured table with fields for each of the above data elements.
RECORDID,USER,DATE,CHANGEDTABLE,CHANGEDFIELD,OLDVALUE,NEWVALUE

If OLDVALUE and NEWVALUE are character fields, they need to be as wide as the widest field in the database which wastes a lot of space. And what if the changed field is a MEMO field?

So perhaps the OLDVALUE and NEWVALUE should themselves be memo fields?

Another way might be to duplicate the original table structure and save the whole edited record but this seems like overkill and greatly expands the database structure.

So I'm wondering if there are other methods that I haven't thought of and if there is a generally accepted method to accomplish this task. Also any critique on the methods I've outlined. One problem I see is what to do when the history file reaches the 2 gig limit.

Thanks for any insights.