>Hi
>
>I am using UPDATE SQL and INSERT SQL to maintain my tables. Is there any way to log changes of the fields i.e. before and after info, in these tables to a log file/table without doing the normal read and compare before the SQL statements ? If a read/seek has be done first, then I am effectively going to have to perform 2 read i/o's ie. one for the read/compare and one for the SQL statement. I feel it is quite inefficient and hope that there could be a better and more efficient way of doing this.
>
>Any ideas/suggestions would be much appreciated.
>
>Thanks in advance

Destine,

First I copy the fields to an array (using scatter())when I know the user wants to edit, before any editing has taken place. Then I use a UDF function called checkforchanges that uses getfldstate() to determine if there are any changes to fields. I then copy the *current* field values to an array (using scatter()) and also compare the array of fields before the changes to the current fields array (because if a user changes a field then changes it back, it will show in getfldstate() as changed, and I don't want to record this in my log as a change). If a change has really been made then I call the UDF audittrail function, which iterates through both the before and after arrays, and compares each array element. For each element that has been changed I save the fieldname to a variable and I convert the data to character type and add it to 2 vars which I call beforechange and afterchange. I continue adding changed fieldnames, and before and after data, using a seperator char between the fields, until i have tested all fields in the arrays. I then save the datetime(), tablename, primarykey value, fieldnames(memo), beforechange(memo), afterchange(memo), and anyother data I need to an audittrail table. You could probably avoid creating the first array (beforechange) by using the oldval() function, but I haven't gotten around to that yet.

hope it helps

tc