This article will try and explain a little bit about the new Database Events that are part of the new features of VFP 7, and how to use these for audit trails, database security etc. Database Events are one of the many new additions to VFP 7; these are like triggers which automatically execute specific routines when something is done with a member of the DBC (such as a Table or a View) or the DBC itself. By default the DBC Events are turned off. There are two ways you can turn on the DBC Events for a given DBC. One is programmatically using something like the following command:

DBSetProp("MyDatabase","Database","DBCEvents",.T.)

The other is using the Set Events On check box in the new Database Properties dialog, to access this right click in the Database Designer and choose Properties.

Note: Once you turn on the DBC Events the DBC will no longer be backward-compatible with previous versions of VFP, but once you turn them off the DBC will become backward-compatible again. The Version property of a DBC returned by DBGetProp(), normally 10, is 11 if DBC Events are turned on.

Just turning on the DBC Events doesn’t mean anything will happen; you have to specify what you want to happen, and this article will explain how to do that, also I will give an example of restricting access to the Database and Tables within the DBC, also the example will show how to keep a check of modifications made.

How much overhead do DBC Events add? Testing the difference in time to open a table with and without DBC_BeforeOpenTable and DBC_BeforeCloseTable events (but nothing in those events) showed a difference of only 2-3 percent, which isn’t a significant amount; obviously, putting extensive code into events will have an impact on performance.

DBC Event Descriptions

DBC Events for DatabasesParametersDBC_OpenDatacDatabaseName, lExclusive, lNoUpdate, lValidateDetails This event is called after the DBC is opened. After it fires, if another DBC is current, DBC_Deactivate fires for that database (since it won’t be the current database anymore). DBC_Activate for the newly open database then fires (since this database becomes the current one). Only after DBC_Activate does DBC() return the name of the Database. If DBC_OpenData returns False, the Error “file access is denied” is triggered and the database can’t be opened. (Note: If you have VFP you can stop this code from firing by opening the DBC as a DBF with the USE database.dbc, this then opens the DBC as a table, you can then change the stored procedure code and recompile the database with COMPILE DATABASE) DBC_CloseDatacDatabaseName, lAllDetails Called before a DBC is closed; return False to prevent the DBC from being closed. DBC Deactivate also fires (after DBC_CloseData), since the database is no longer the current database.DBC_ActivatecDatabaseName DetailsCalled when the DBC is made current via such means as Set Database, after Open Database completes, clicking on an open Database Designer window, and so on. If another DBC is current, DBC_Deactive fires for that database first (if that database has events enabled). DBC_DeactivecDatabaseName DetailsCalled when the DBC is made non-current; return False to prevent this from happening (this also prevents the DBC from being Closed). DBC_BeforeAppendProccFileName, nCodePage, lOverwriteDetails Called before the Append Procedures command starts; return False to prevent the procedures from being updated. DBC_AfterAppendProccFileName, nCodePage, lOverwrite DetailsCalled after the Append Procedures command has completed. DBC_BeforeCopyProccFileName, nCodePage, lAdditiveDetailsCalled before the Copy Procedures operation starts; return False to prevent procedures from being copied. DBC_AfterCopyProccFileName, nCodePage, lAdditive DetailsCalled after the Copy Procedures operation has completed. DBC_BeforeModifyProcDetailsCalled before stored procedures are modified; return False to prevent the editor window from appearing. DBC_AfterModifyProclChangedDetailsCalled when the window for stored procedures is closed.DBC_BeforeDBGetPropcName, cType, cProperty Details

Called before DBGetProp() executes. Returning False prevents the property’s value from being read, in which case DBGetProp() returns NULL.DBC_AfterDBGetPropcName, cType, cPropertyDetailsCalled after DBGetProp() completes but before the value is actually returned.

DBC_PackDataDetailsCalled before Pack Database executes; returning False from this event prevents the database from being packed, but also tiggers a “file access denied” error, so be prepared to trap for this.

DBC Events for TablesThere’s a DBC event associated with everything you can do structurally with a table. One issue is The Remove Table and Drop Table commands, which do the same thing, have a different set of events. If you want to trap the removal of a table, then be sure to handle both set of events. Another issue is that if you open a table with a different alias, that alias is passed for the cTableName parameter in all table events rather than the real name of the table. Workaround for this is to use:

CursorGetProp("SourceName")

to determine the real name of the table.

DBC Events for TablesParametersDBC_BeforeAddTablecTableName, cLongTableNameDetailsCalled before a free table is added to the DBC; return False to prevent the table from being added. DBC_AfterAddTablecTableName, cLongTableNameDetailsCalled after a free table is added to the DBC. DBC_BeforeCreateTablecTableName, cLongTableNameDetailsCalled before a table is created; return False to prevent table creation. DBC_AfterCreateTablecTableName, cLongTableNameDetailsCalled after a table is created. DBC_BeforeDropTablecTableName, lRecycleDetailsCalled before a table is removed from the DBC using Drop Table; return False to prevent a table from being removed. DBC_AfterDropTablecTableName, lRecycleDetailsCalled after a table has been removed. DBC_BeforeRemoveTablecTableName, lDelete, lRecycleDetailsCalled before a table is removed from the DBC using the Remove Table command or visually in the Database Designer; return False to prevent the table from being removed. DBC_AfterRemoveTablecTableName, lDelete, lRecycleDetailsCalled after a table has been removed. DBC_BeforeModifyTablecTableNameDetailsCalled before a table structure is modified; return False to prevent modification. DBC_AfterModifyTablecTableName, lChangedDetailsCalled after a table structure has been modified. You can’t tell what changes are made unless you save the structural information somewhere in the DBC_BeforeModifyTable (or use metadata) and then compare the current structure with the saved information in the DBC_AfterModifyTable. DBC_BeforeRenameTablecPreviousName, cNewNameDetailsCalled before a table is renamed. DBC_AfterRenameTablecPreviousName, cNewNameDetailsCalled after a table has been renamed. DBC_BeforeOpenTablecTableNameDetailsCalled before a table is opened; returning False prevents the table from being opened and triggers a “file access denied” error, which you should be prepared to trap. DBC_AfterOpenTablecTableNameDetailsCalled after a table is opened. DBC_BeforeCloseTablecTableNameDetailsCalled before a table is closed; return False to prevent the table from being closed (no error is triggered). DBC_AfterCloseTablecTableNameDetailsCalled after a table is closed.

DBC Events for Views and Relations will be covered in Part 2, when I have had time to play with these. So back to the main topic and using some of the events described above. Note that these events return .T. for successful and .F. to fail, so if you returned .F. in every event you would not be able to do anything with the database or any table contained within the database.

Example

This Example contains a simple database with a few tables, these tables are:

Audit – Keeps a log with what is going ondbcUsers – Users allowed to access the Database, and which access levelTestTable – table you can use to modify and do whatever with.

The example will only allow access level 9 to Modify Tables and Stored Procedures.Users Available:

NoteIf you open a main file in the Database like Audit without first opening the Database, you might get a C5 error, why this happens I don’t really know, but if you open the files in the following order you won’t have any problems:

OPENDATABASE dbcEvents
USE Audit

This is only a small part of what the Events can do for you, with some work you can write tools to keep track of table structure changes, and then e-mail these changes to the rest of your team, so that they know what has been added or deleted. This In the next article I will show how to use Event handlers and triggers to give you a proper audit trail, so when users add/delete or change records these get logged to an audit table.

If you want to know more about these great new features, please feel free to ask.

Download codeYou can download Simons sample here. The download is a zipfile. Its size is 29.567 bytes.

ABOUT THE AUTHOR: SIMON ARNOLD

Simon has worked with FoxPro for over 16 years. He currently works for a company based in Harrogate, North Yorkshire (UK), which
specialises in FoxPro, Web and Unix development.He has a weblog at http://weblogs.foxite.com/simonarnold.