Database Events

Database Events, added in VFP 7, are a cool enhancement to an already powerful data engine. Like a trigger, which automatically executes procedural code when a record is inserted, updated, or deleted, a database event automatically executes a specific routine when something is done to a member of the DBC (such as a table or view) or to the DBC itself. These events always fire, whether you do something visually (for example, selecting a view in the Database Designer and pressing the Delete key) or programmatically (executing the DROP VIEW command), in a runtime or development environment, through VFP code or through the OLE DB provider. Events are available for almost any action you can take on any object in a DBC, such as opening or closing a table or view, creating a connection, dropping a relation, validating the DBC, and so forth.

By default, Database Events are turned off. There are two ways you can turn them on for a given DBC. You can do it programmatically for the current database by issuing something like:

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

You can also do it visually using the Set Events On check box in the Database Properties dialog (choose Properties from the Database menu or right-click in the Database Designer and choose Properties from the shortcut MENU TO display this dialog).

The Version property of a DBC returned by DBGetProp(), normally 10, is 11 if Database Events are turned on.

A DBC (and therefore anything in the database) with events turned on cannot be accessed by previous versions of VFP or through the VFP ODBC driver. Of course, you can always turn events back off again, but only from within VFP 7. If you want to maintain compatibility with older applications but use Database Events in your new versions, have your new applications explicitly turn Database Events on when they start. Just be aware that while the new applications are running, the older versions won’t be able to access the database.

Turning Database Events on doesn’t mean anything will happen; you have to specify what events you want to handle by creating routines with the names VFP expects for these events. The procedure names all begin with “DBC_”, followed by the name of the event (for example, DBC_BeforeOpenTable is the name of the procedure VFP executes when a table is opened). You can use the ALANGUAGE() function to fill an array with the names of Database Events.

Database event code can be placed in the stored procedures of a DBC or in a separate PRG file. There are two ways you can specify a separate PRG file. Programmatically, use something like the following (where lcPRGName contains the name of an existing PRG):

DBSETPROP("MyDatabase", "Database", "DBCEventFileName", "MyProcs.PRG")

The other way is to use the Events File check box and associated Open File button in the Database Properties dialog. Doing it visually doesn’t automatically create the PRG; you must select an existing file, even if it’s empty.

Although you can type the database event names and parameter lists yourself, VFP can generate “template” code for you. Bring up the Database Properties dialog and select the events you want to handle in the Events list box (any events that already have code will appear in bold) and then click the Edit Code button. An edit window for either the stored procedures of the DBC or the separate PRG file, depending on which you’re using, appears with template code for each of the events you selected. All you have to do is fill in the code to execute for each event.

If you want to work on existing code for a single event or generate the template for it, double-clicking on the event will do the trick. Hold down the Ctrl and Shift keys while clicking the Edit Code button to generate template code with a ? statement to display the event name and parameters passed (although we would have preferred a DEBUGOUT statement instead).

Most actions have both “Before” and “After” events; in the case of actions that have only a single event, that event is implicitly a Before event. While the After events just receive notification that an action took place, the Before events can actually prevent the action from occurring by returning False. For example, if DBC_BeforeOpenTable returns .F. unconditionally, you won’t be able to open any tables or views. Obviously, you want to be careful how you handle these events or you can make your database unusable!

Database Events are available for the DBC itself, as well as for tables, views, relations, and connections.

The stored procedures of a database are like a PRG file in that if there are two or more procedures with the same name, the last one is the one executed when the procedure is called. While this can be handy for testing—no need to comment out a procedure to insert test code when you can just create another procedure below the "real" one, using the same name, containing the test code—it can cause you to pull your hair out trying to figure out why the code you've written isn't executed and some other code is run instead.

Database Events for Databases

Almost everything you can do with a database has a DBC event associated with it; the only one missing is a CompileDatabase event.

Database Events for Tables

There’s a database event associated with everything you can do structurally with a table. One thing is a little goofy: 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, you have to be sure to handle both sets of events. (The trick to this, of course, is to designate one stored procedure you create to hold the logic for both events, and have each of the events call that stored procedure. Calling code in an event is poor OOP.) Another issue is that if you open a table with a different alias, that alias (rather than the name of the table) is passed for the table name parameter. The workaround is to use CURSORGETPROP(“SourceName”) to determine the real name of the table.

Database Events for Views

As with tables, anything you can do with a view has a database event associated with it. Some actions with views, such as creating or opening, cause the source tables to be opened, so the BeforeOpenTable and AfterOpenTable events fire for those tables as well, which both makes sense and is desirable. Interestingly, these events fire even if the tables are already open, giving us a glimpse of some internal processes involved in opening the view (such as opening a second copy of the tables).

As with tables, if a view is opened with a different alias, that alias (rather than the name of the view) is passed for the table name parameter in the Open and Close events.

Database Events for Relations

There are only two types of events for relations: adding and removing. When a relation is modified, it’s first removed and then re-added, so both the drop and add events fire.

Database Events for Connections

There are four types of events for connections: adding, removing, renaming, and modifying.

What Have You Done For Me Lately?

So, what kinds of things can you use Database Events for? Lots of things. See “DBF, FPT, CDX, DBC—Hike!” for some ideas we’ve come up with.

See Also

ALanguage(), Activate Database Event, AfterAddRelation, AfterAddTable, AfterAppendProc, AfterCloseTable, AfterCopyProc, AfterCreateConnection, AfterCreateOffline, AfterCreateTable, AfterCreateView, AfterDBGetProp, AfterDBSetProp, AfterDeleteConnection, AfterDropOffline, AfterDropRelation, AfterDropTable, AfterDropView, AfterModifyConnection, AfterModifyProc, AfterModifyTable, AfterModifyView, AfterOpenTable, AfterRemoveTable, AfterRenameConnection, AfterRenameTable, AfterRenameView, AfterValidateData, BeforeAddRelation, BeforeAddTable, BeforeAppendProc, BeforeCloseTable, BeforeCopyProc, BeforeCreateConnection, BeforeCreateOffline, BeforeCreateTable, BeforeCreateView, BeforeDBGetProp, BeforeDBSetProp, BeforeDeleteConnection, BeforeDropOffline, BeforeDropRelation, BeforeDropTable, BeforeDropView, BeforeModifyConnection, BeforeModifyProc, BeforeModifyTable, BeforeModifyView, BeforeOpenTable, BeforeRemoveTable, BeforeRenameConnection, BeforeRenameTable, BeforeRenameView, BeforeValidateData, CloseData, CursorGetProp(), DBGetProp(), DBSetProp(), Deactivate Database Event, ModifyData, OpenData, PackData