I hinted at the possibility of doing an audit-trail, in my article on triggers - now, as a reaction to questions in the Universal Thread, I want to present a sample solution.
What should be saved
First, consider what you want to save when a user changes a record. You will probably want to include most of the following options:
Where to save it
You could use a text-file, but you will probably want to use a table (DBF), since this will make it easier to do systematic searches.
How to accomplish it
First of all, you can intercept any change saved, with a trigger. If you are not familiar with triggers, I suggest you read my introductory article on triggers, Universal Thread Magazine, June 2002.
The trigger function receives a parameter to indicate whether the change done was an Insert, an Update, or a Delete. (Of course, you can also create three separate trigger functions for the three cases.) I believe that the trigger function is best saved in the database stored procedures.
Now, for each field in the table, save the old and new values to the audit-table. Old values (i.e., before the change was made) are obtained with an expression like OldVal("MyField").
Requirements
In order to use a trigger function, each table which you want to audit has to be part of a database.
While I strongly recommend to use buffering most of the time, this is not a requirement for triggers to work. There is frequent confusion on this topic, due to a somewhat confusing help topic for the function OldVal(). I tested trigger functions for an audit-trail (and other trigger functions, where changes in one table update another table), and it works quite well without buffering.
Some of the functions I used in this article require VFP 6 or later, but it is quite possible to adapt the audit-trail function to versions 3 or 5, as well.
Sample table structure
Create a table with these commands:
set blocksize to 0 && Optional; reduces memo size
create table MyAudit free; (TableName C(20), UserName C(20), TrigType C(1),; WhenChang T, Changes M)
Note: I avoided using field names of more than 10 characters, since I decided to keep the audit table free.
The fields used are:
The sample audit-trail function
* Basic audit-trail function * (Sample, for the article) FUNCTION MyAudit(tcTriggerType) local lnField, lcFieldName, lcTable, lcUserName, lcChanges if type("gcUserName") = "C" && if public variable for the user has been declared lcUserName = gcUserName else lcUserName = "???" endif lcTable = juststem(dbf()) lcChanges = "" do case case tcTriggerType = "D" * Nothing is required here, unless you want to * save OldVal(...) for each field * (in case the user made changes, and then deleted the record) case tcTriggerType = "I" for lnField = 1 to fcount() lcFieldName = field(lnField) lcChanges = lcChanges + lcFieldName + ": "; + transform(eval(lcFieldName)); + chr(13) + chr(10) next case tcTriggerType = "U" for lnField = 1 to fcount() lcFieldName = field(lnField) lcChanges = lcChanges + lcFieldName + ": "; + transform(oldval(lcFieldName)) + " -> "; + transform(eval(lcFieldName)); + chr(13) + chr(10) next endcase insert into MyAudit; (TableName, UserName, TrigType, WhenChang, Changes); values; (lcTable, lcUserName, tcTriggerType, datetime(), lcChanges)
How to use the sample function
Open any table for which you want to save audit-trail information, with MODIFY STRUCTURE. Go to the "Table" tab.
Insert code for the three triggers. For the insert trigger, type MyAudit("I"). For the update trigger, type MyAudit("U"). For the delete trigger, type MyAudit("D").
If any of the triggers already has an expression, don't overwrite it. (The expressions may have been inserted by the Referential Integrity Builder, or by a programmer.) Instead, add the functions to the end of the expression, combined with AND.
For instance, if the expression for the insert trigger is __ri_insert_mytable(), change it to __ri_insert_mytable() and MyAudit("I").
Normally, it is convenient to put the audit function at the end, so that no audit information is saved if other triggers fail (return .F.).
You have to repeat this for every table you want to keep track of.
Possible Enhancements
One possible enhancement is to change to a format that makes it easier to be read automatically by a program. This may be important if you want to use the audit-trail not only for documentation purposes, or to fix responsibilities, but you also want to be able to have rollback and roll-forward capabilities (that is, undo changes, based on the audit-trail and new data; or recover data after the last backup, also from the audit-trail).
Another thing you may want to change is to use a more sophisticated type conversion, instead of the standard transform() function.
Finally, note that I didn't make every effort to make the function as perfect as possible, nor did I test it under a variety of circumstances. The intention was simply to have a working sample, to introduce you to audit-trail programming.
Additional considerations
For a multi-user system, it is important to keep the time on the different users' machines more or less synchronized. I did this by checking the time on the server on startup of the main program, and exiting (with an error message) if there was a discrepancy of more than 10 minutes. One way to check the time on the server is to create a file on the server, and check its date-time. The file will have the date-time of the server. (I only tested this on a Windows NT 4 file server.)
Another thing to consider is that the audit-trail table can grow rather quickly. You may want to save the older parts of the audit-trail to a history file, to reduce the size of the backup (in case you include the audit-trail in the backup), or in case it approaches the 2 GB limit.
Also note that saving data to the audit-trail can make every change to your data slower. This should be no problem when the user does changes interactively, but when a procedure or trigger updates hundreds or thousands of records automatically, things can get noticeably slower. You may therefore want to disable auditing under certain circumstances.
Conclusion
As you have seen, it is relatively easy to add an audit-trail to your tables.
The sample audit-trail in this article is a demo only; a proof of concept. The idea was to give you a hint of what can be done. You may want to use the ideas in this article as a starting-point, to create an audit-trail for your specific needs.
Source Code
