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

Hilmar -
Yes, your first suggestion is what I have been doing and it has the problems you mentioned. I am currently experimenting with the other 2.