We do it a couple of ways depending on the type of data changed.
For simple ones we use a trigger and have it write to an audit table the row
of data plus a few extra fields, date changed, type change, ie edit/add,
delete, who did it.
You could just as easily output append to a text file, so the database
doesnt grow too fast.
For more complex tables we compare old and new values by column, something
like this
if newvalue <> .oldvalue then
set var changeinfo = (.oldvalue & '-->>' & .newvalue)
endif
if newvalue2 <> .oldvalue2 then
set var changeinfo = (.changeinfo & .oldvalue2 & '-->>' &
.newvalue2)
endif
[ going thru the list of columns involved ]
then at the end build a string saying
changeinfo = (.#date & .#time & .userid, & .changeinfo)
and store that in a transaction log or history file.
this would create a line looking like this '7/25/08 11:45am [userid]
Johhn Doe -->> John Doe, 234 Main St -->> 190 South St' etc, so you
could see the before and after info and who did it. You can format it to
store in a table or output it to a text file using
Out logfilename.txt append
write .changinfo
out screen
This was written before dirty flags and sys_column_new etc so it could be
more elegant now, but it works.
Mark Lindner
Lindner & Associates PC
254 Second Ave
Needham MA 02494
781 247 1100 Fax 781 247 1143
-----Original Message-----
From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of Tom
Frederick
Sent: Tuesday, July 29, 2008 1:47 PM
To: RBASE-L Mailing List
Subject: [RBASE-L] - Transaction logs
Is there a preferred method of tracking changes users make to database info?
We currently track who was the last user, computer used, date/time to make
changes to rows in a table. While this works great, it does nothing to track
who changed individual fields within the row or what was changed. It would
be very common for three users open a form for Table INFO, each change 1 of
20 different fields, and all we would know was who was the last person at
INFO. We are looking at several options (i.e., load row into variables,
delete unchanged rows, convert data to text, and append to a TRANS table
with the user ID info). Is this a case of paranoia on our part? Yep, but we
can also see a bunch of coming performance problems if we do nothing. Is
there a sample in R:Base or a basic procedure we should follow? This feels
like a potentially large project but needed.
Tom Frederick
Elm City Center
1314 W Walnut
Jacksonville, IL 62650
Off - 217-245-9504
Fax - 217-245-2350
Email - [EMAIL PROTECTED]
Web - www.elmcity.org
