BeforeModifyTable, AfterModifyTable

Hacker’s Guide to Visual FoxPro
An irreverent look at how Visual FoxPro really works. Tells you the inside scoop on every command, function, property, event and method of Visual FoxPro.

BeforeModifyTable, AfterModifyTable

These Database Events fire when a table’s structure is modified in some way: in the Table Designer, using the ALTER TABLE command, when indexes are created or removed, and when triggers are created or removed.

Usage

PROCEDURE DBC_BeforeModifyTable( cTableName )

PROCEDURE DBC_AfterModifyTable( cTableName, lChanged )

Parameter	Value	Meaning
cTableName	Character	The name of the table being modified.
lChanged	Logical	Indicates whether the table was changed.

Most table-related events, these included, share a common bug: If you open a table with an alias (that is, you specify the ALIAS clause in the USE command), that alias (rather than the long table name) is passed to all database events . That'll cause havoc with any code that needs the actual name rather than the current alias, such as calls to DBGetProp(). The workaround is to use CursorGetProp('SourceName') to determine the real name of the table.

What we originally thought was a second bug is simply a consequence of the first. If you rename a table that's open, all subsequent database events (until the table is closed, that is) receive the former name of the table rather than the new name. The reason for this behavior is that the table's alias is still its original name.

As with other Before events, you can prevent a table from being modified (in the case of the MODIFY STRUCTURE command, the Table Designer won’t appear) by returning .F. in the BeforeModifyTable event. The AfterModifyTable event simply receives notification that the table may have been modified (the lChanged parameter is .T. if so).

When you delete a tag using DELETE TAG, the lChanged parameter receives .F. rather than the expected .T.

As discussed in the “Database Events” topic, there’s no way to tell what changes a user made, so if that’s important, you’ll have to save the current table structure (everything, including all table, field, index, and trigger information) in the BeforeModifyTable event, or rely on metadata that saves that information somewhere, and then compare the saved information against the table structure in the AfterModifyTable event.

Example

* This goes in the stored procedures of a database.

PROCEDURE DBC_BeforeModifyTable(cTableName)
WAIT WINDOW PROGRAM() + CHR(13) + ;
    'cTableName: ' + cTableName

PROCEDURE DBC_AfterModifyTable(cTableName, lChanged)
WAIT WINDOW PROGRAM() + CHR(13) + ;
    'cTableName: ' + cTableName + CHR(13) + ;
    'lChanged: ' + TRANSFORM(lChanged)

* End of stored procedures.
* Change some stuff about the Customer table.

OPEN DATABASE TestData
DBSETPROP(DBC(), 'Database', 'DBCEvents', .T.) && Turn on events
USE Customer EXCLUSIVE
ALTER TABLE Customer ADD COLUMN NewField C(1)
MODIFY STRUCTURE    && Remove the NewField field
INDEX ON Company TAG MyNewTag
DELETE TAG MyNewTag
CREATE TRIGGER ON Customer FOR INSERT AS .T.
DELETE TRIGGER ON Customer FOR INSERT