BeforeOpenTable, AfterOpenTable, BeforeCloseTable, AfterCloseTable

Despite their names, these Database Events fire when either a table or view is opened or closed; there are no specific events just for views.

Usage

PROCEDURE DBC_BeforeOpenTable( cTableOrViewName )

PROCEDURE DBC_AfterOpenTable( cTableOrViewName )

PROCEDURE DBC_BeforeCloseTable( cTableOrViewName )

PROCEDURE DBC_AfterCloseTable( cTableOrViewName )

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 . This is also true with these events when they're fired for a view; all other view events receive the correct name, however. This bug will 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 or view, although this can be used only when the table or view is open (such as in AfterOpenTable or BeforeCloseTable).

The USE command supports a wide variety of clauses, such as EXCLUSIVE, SHARED, and ALIAS. However, unlike many other database events, BeforeOpenTable and AfterOpenTable don't receive any parameters representing the values of these clauses. We're not sure this is a bug so much as a practical issue: These events would have to receive over a dozen parameters to handle all the clauses, which would be fairly cumbersome. Also, some functions such as ISEXCLUSIVE(), ISREADONLY(), and ORDER(), can be used in AfterOpenTable to determine some of the properties of the opened table or view.

Similar to other before-and-after pairs of events, you can prevent a table or view from being opened or closed by returning .F. in the Before events, while the After events simply receive notification that something happened. Returning .F. from BeforeOpenTable triggers a “file access denied” error, which you should be prepared to trap in your error handler.

As you may suspect, opening a view doesn’t just fire the open events for the view; it also fires them for the underlying tables as well, although interestingly, the table events fire even if the tables are already open. This probably gives us an indication of what VFP is doing behind the scenes, such as opening instances of the tables in internal data sessions, when a view is opened.

One use of the BeforeOpenTable event is to provide a rudimentary form of security. For example, you can check the current user’s name against a table of security rights and return .F., which prevents the table from opening, if the user doesn’t have rights to the table or view. The AfterOpenTable and AfterCloseTable events could be used to log table access by recording the date, time, and name of each user who opened and closed a table or view. Another use for AfterOpenTable is to automatically create indexes for views when they’re opened.

Example

* This goes in the stored procedures of a database.

PROCEDURE DBC_BeforeOpenTable(cTableName)
* Ensure the user has rights to open the table or view.
LOCAL lnSelect, lcUser, llReturn
lnSelect = SELECT()
lcUser = GetUserName()
SELECT RIGHTS ;
    FROM SECURITY ;
    WHERE (USER = lcUser OR USER = "ALL") AND ;
        OBJECT = UPPER(cTableName) ;
    INTO CURSOR TEMP
IF _TALLY = 0 OR RIGHTS = 0
    MESSAGEBOX("You are not authorized to access " + cTableName)
    llReturn = .F.
ELSE
    llReturn = .T.
ENDIF
IF USED('TEMP')
    USE IN TEMP
ENDIF
SELECT (lnSelect)
RETURN llReturn

PROCEDURE DBC_AfterOpenTable(cTableName)
* Index the OrdView view
IF UPPER(cTableName) = "ORDVIEW"
    lnBuffer = CursorGetProp('Buffering')
    IF lnBuffer > 3
        cursorsetprop('Buffering', 3)
    ENDIF
    INDEX ON Order_ID TAG Order_ID
    IF lnBuffer > 3
        CursorSetProp('Buffering', lnBuffer)
    ENDIF
ENDIF

* End of stored procedures.
* Create a security table, create a view, then try to open
* tables and views.

CD (_SAMPLES + "\DATA")
CREATE TABLE SECURITY FREE (USER C(20), OBJECT C(128), RIGHTS I)
INSERT INTO SECURITY VALUES ("ALL", "ORDERS", 3)
INSERT INTO SECURITY VALUES ("ALL", "ORDVIEW", 3)
OPEN DATABASE TestData
DBSETPROP(DBC(), "Database", "DBCEvents", .T.) && turn on events
CREATE SQL VIEW OrdView AS SELECT * FROM Orders
USE Customer    && displays unauthorized dialog, followed by
                && "file access denied" error
USE Orders      && opens successfully
USE OrdView     && opens successfully
? TAG(1)        && displays "ORDER_ID"

See Also

Close Databases, Close Tables, Create SQL View, Database Events, IsExclusive(), IsReadOnly(), Order(), Use