In this article, I discuss several levels of tracking but only as they relate to logging mandates, starting with the very simple and ending with a granular level of tracking. In each case, I briefly discuss techniques you can use to accomplish the logging. This article isn't intended as a blueprint for implementing logging, but rather as a sketch of the general approach.

Compliance mandates

But first, what are the regulatory mandates themselves that govern audit logging? Following is an excerpt from a recent white paper by Steven Blackwell, a data security and protection expert in the FileMaker developer community:

"As a result of Sarbanes-Oxley, or of the HIPAA regulations covering various personally identifiable medical and healthcare information, or of Gramm-Leach-Bliley Act requirements for financial institutions, or of Payment Card Industry requirements for credit card processing, or of Buckley Act requirements for management of student data, many organizations have adopted extensive security policies governing specific steps that must be taken to assure confidentiality, integrity, and availability of digital assets. Additionally many companies or organizations not covered by various regulatory frameworks have also adopted, as a matter of Best Practices, similar requirements. In the not-for-profit sector this often includes the business leagues of regulated industries (501c6), educational, literary or scientific professional organizations (501c3), and foundations (501c9) supported by various businesses or industries."

You can download the complete white paper from: http://worldsync.com/fmDataGuard/RealTime.pdf

So, assuming your database fits into the wide range of information systems covered by government regulations, or you see the independent merit of tracking changes for one of the benefits discussed in Steven's quote, let's take a look at some various levels of change tracking and the methodology behind each.

Simple tracking

Perhaps the simplest form of tracking, and one every table in every database should have, is accomplished by adding four fields (table 1).

By adding these four fields, you can determine a number of things about activities that take place in your databases, such as limited usage analysis. The process of adding them (if you don't have them already) is simple. In fact, if you have FileMaker Pro Advanced, you can create them in one table, then copy and paste them into other tables.

Although simple to implement, this approach is limited in its ability to provide real activity tracking. First of all, it only tracks the last change, and only captures the date, time, and user name. This limited information falls well short of meeting any regulatory requirements and provides little in the way of backup or recovery.