Mike, Claudine, James, Mark, Emmitt, Karen Thanks for all the responses. There are some good options for us to work on that we never considered. As usual some of this material is just one step past good understanding on our part, but that is part of the "fun" of learning how to make it work. What a group!
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 -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of MikeB Sent: Tuesday, July 29, 2008 4:10 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Transaction logs Just another tidbit created out of necessity/convenience... ----- Original Message ----- From: "Claudine Robbins" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, July 29, 2008 4:08 PM Subject: [RBASE-L] - RE: Transaction logs Mike, That is so "génial!" as we say in French. I wasn't going to say anything because all the ideas shared today on audit data are so much more sophisticated that what I do. However, with your example, my simple --unload and append data to a csv monthly-trail-data-file every time a record is either entered or changed-- turns into a readily searchable table should the need arise. Thank you! Claudine -----Original Message----- From: [email protected] [mailto:[EMAIL PROTECTED] On Behalf Of MikeB Sent: Tuesday, July 29, 2008 2:25 PM To: RBASE-L Mailing List Subject: [RBASE-L] - RE: Transaction logs If you output to a text file for your trans log and keep the data in CSV format, you can use ODBC and the Microsoft Text Driver to process the file from RBase as if it were a temp table. Example: *(watch for wrap) *(StartEst.Rmd MByerley 120805) *() *() *() *(Start Server Tables) SET MESSAGES OFF SET ERROR MESSAGES OFF IF (CVAL('DATABASE')) <> 'Est' OR (CVAL('DATABASE')) IS NULL THEN CONNECT est ENDIF --SET VAR vconnect = + --('SCONNECT '';driver=R:BASE 7.5 Database Driver (*.rb1);dbq=' + .vloc +'''') SET VAR vconnect = + ('SDISCCONNECT '';Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=f:\Rb76\Est\;Extensions=asc,csv,tab,txt''') SET VAR vconnect = + ('SCONNECT '';Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=f:\Rb76\Est\;Extensions=asc,csv,tab,txt''') &vconnect Sattach 'Vendlst.Csv' RETURN ----- Original Message ----- From: "Mark Lindner" <[EMAIL PROTECTED]> To: "RBASE-L Mailing List" <[email protected]> Sent: Tuesday, July 29, 2008 2:59 PM Subject: [RBASE-L] - RE: Transaction logs 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
