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

Either that, or have something like the following, in a single memo field:

"Changed by user Freeman
Field1: 0 --> 1
Field2: abc --> def
..."

We do something like this at BSO; but note that while this is readable for humans, it is not convenient for a computer search. So, you may want to keep each field changed in a separate record, as I think you are proposing.

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

I would say, try to make it generic - i.e., a generic solution for every table. Duplicating the table structure would require a separate audit table for each of your data tables; not very practical, IMNSHO.
>
>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.

2 GB limit: our main problem is that we don't want to include a year of audit data in the daily backups. So, once a month we copy older audit data to a history (or archival) file. Once the history file approaches 2 GB, we will have to split the data horizontally, i.e., create a new history file for the more recent data.

You will have to check the size of the file regularly, and do something similar.