>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.

There's a commercial product, FoxAudit that addresses this: http://www.takenote.com/DesktopDefault.aspx?tabid=28