s4g346.md

CREATE TRIGGER, DELETE TRIGGER

These two commands let you add and remove triggers for tables in a database.

Usage

CREATE TRIGGER ON TableName
     FOR INSERT | UPDATE | DELETE AS lTrigger
DELETE TRIGGER ON TableName
     FOR INSERT | UPDATE | DELETE

A trigger is a piece of code that runs under certain conditions. Visual FoxPro supports three kinds of triggers: Insert, Update and Delete. The Insert trigger for a table fires when you append a record and when you RECALL a deleted record. The Update trigger fires when you modify a record, and the DELETE TRIGGER fires when you delete a record. Buffering postpones triggers until you do something that attempts to commit the change.

Although lTrigger can be a simple expression, usually it's a call to a routine (in fact, most often a stored procedure) that returns either .T. or .F. If a trigger returns .F., the current error handler (yours or FoxPro's) is called.

When a trigger returns .F. in a Browse, it bypasses any custom error handler, and FoxPro's default "Trigger failed" message appears. This is generally a concern only in the development environment, however, since your error handler is correctly called when the trigger returns .F. in a grid.

Triggers cannot change data in the table they belong to. They can (and often do) change data in other tables in the same database.

The RI builder uses triggers to enforce referential integrity. It generates code that is saved with the database's stored procedures and sets the tables' triggers to appropriate calls to that code. (These routines are one example of trigger code that changes other tables.)

The Delete trigger doesn't fire when you ZAP a table. Although we think using ZAP on a table is a terrible mistake, except on your own test data, this is an absolute violation of the integrity of the database. It leaves a huge landmine in the path of any user who knows enough to be dangerous.

In VFP 7, if the database has Database Events turned on, CREATE TRIGGER and DELETE TRIGGER fire the BeforeModifyTable and AfterModifyTable events.

Example

CREATE TRIGGER ON MyTable FOR INSERT AS MyTrigger

* And here's the trigger code
PROCEDURE MyTrigger

WAIT WINDOW "New record added. Total is now " + ;
            LTRIM(STR(RECCOUNT()))
RETURN .T.

See Also

AfterModifyTable, BeforeModifyTable, Create Database, Create Table, Modify Database, _TriggerLevel, Zap