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.

Event	Fired When
Activate	The database becomes the current one.
AfterAppendProc	After procedures are appended.
AfterCopyProc	After procedures are copied.
AfterDBGetProp	After DBGETPROP() is called.
AfterDBSetProp	After DBSETPROP() is called.
AfterModifyProc	After stored procedures are modified.
AfterValidateData	After VALIDATE DATABASE is called.
BeforeAppendProc	Before stored procedures are appended.
BeforeCopyProc	Before stored procedures are copied.
BeforeDBGetProp	Before DBGETPROP() is called.
BeforeDBSetProp	Before DBSETPROP() is called.
BeforeModifyProc	Before stored procedures are modified.
BeforeValidateData	Before VALIDATE DATABASE is called.
CloseData	The database is closed.
Deactivate	The database is no longer the current one.
ModifyData	The Database Designer is displayed.
OpenData	The database is opened.
PackData	The database is packed.

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.

Event	Fired When
AfterAddTable	After a free table is added to the database.
AfterCloseTable	After a table is closed.
AfterCreateTable	After a table is created.
AfterDropTable	After a table is removed with the DROP TABLE command.
AfterModifyTable	After a table's structure is modified.
AfterOpenTable	After a table is opened.
AfterRemoveTable	After a table is removed with the REMOVE TABLE command or interactively.
AfterRenameTable	After a table's name (but not DBF name) has been changed.
BeforeAddTable	Before a free table is added to the database.
BeforeCloseTable	Before a table is closed.
BeforeCreateTable	Before a table is created.
BeforeDropTable	Before a table is removed with the DROP TABLE command.
BeforeModifyTable	Before a table's structure is modified.
BeforeOpenTable	Before a table is opened.
BeforeRemoveTable	Before a table is removed with the REMOVE TABLE command or interactively.
BeforeRenameTable	Before a table's name (but not DBF name) is changed.

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.

Event	Fired When
AfterCloseTable	After a view is closed.
AfterCreateOffline	After a view is taken offline.
AfterCreateView	After a view is created.
AfterDropOffline	After a view is taken back online.
AfterDropView	After a view is deleted (whether DROP VIEW or DELETE VIEW is used).
AfterModifyView	After a view is modified.
AfterOpenTable	After a view is opened.
AfterRenameView	After a view is renamed.
BeforeCloseTable	Before a view is closed.
BeforeCreateOffline	Before a view is taken offline.
BeforeCreateView	Before a view is created.
BeforeDropOffline	Before a view is taken back online.
BeforeDropView	Before a view is deleted (whether DROP VIEW or DELETE VIEW is used).
BeforeModifyView	Before a view is modified.
BeforeOpenTable	Before a view is opened.
BeforeRenameView	Before a view is renamed.

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.

Event	Fired When
AfterAddRelation	After a relation is created.
AfterDropRelation	After a relation is removed.
BeforeAddRelation	Before a relation is created.
BeforeDropRelation	Before a relation is removed.

Database Events for Connections

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

Event	Fired When
AfterCreateConnection	After a connection is created.
AfterDeleteConnection	After a connection is removed.
AfterModifyConnection	After a connection is modified.
AfterRenameConnection	After a connection is renamed.
BeforeCreateConnection	Before a connection is created.
BeforeDeleteConnection	Before a connection is removed.
BeforeModifyConnection	Before a connection is modified.
BeforeRenameConnection	Before a connection is renamed.

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.