Creating Triggers

You create triggers using the Table Designer or the CREATE TRIGGER command. For each table, you can create one trigger for each of the three events: INSERT, UPDATE, and DELETE. A table can have a maximum of three triggers at any one time. A trigger must return a true (.T.) or false (.F.) value.

To create a trigger

• In the Table tab of the Table Designer, enter the trigger expression or the name of a stored procedure containing the trigger expression in the Insert trigger, Update trigger, or Delete trigger box.

  -or-

• Use the CREATE TRIGGER command.

For example, perhaps each time Tasmanian Traders sells an item, they want to compare the remaining Units_in_stock against the Reorder_level and be notified if they need to reorder that item. You can create an Update trigger on the products table to accomplish this. Every time a product is sold, the Update trigger will fire and the Units_in_stock field will be updated to reflect the remaining items in stock.

To create the trigger, you can specify updProductsTrigger( ) as your Update trigger for the products table. You can add a field to products, named reorder_amount, which stores the amount you want to order each time you reorder the item, and create a reorder table with the fields: product_id and reorder_amount. You can then add this code to your stored procedure:

PROCEDURE updProductsTrigger
   IF (units_in_stock+units_on_order) <= reorder_level
   INSERT INTO Reorder VALUES(Products.product_id, ;
    Products.reorder_amount)
   ENDIF   
ENDPROC

You can create similar triggers for an insert or delete event by using the FOR INSERT or FOR DELETE clause, respectively, instead of the FOR UPDATE clause. If you attempt to create a trigger that already exists for a particular event and table while SET SAFETY is on, Visual FoxPro asks you if you want to overwrite the existing trigger.

See Also

Trigger Usage | Removing or Deleting Triggers | Working with Tables | Modifying Triggers | Modifying the Table Structure