Getting Started › Procedures and Triggers › Example: Calling a Procedure › Using a Trigger

Using a Trigger

Given the business process described by the preceding ITEMKILL procedure program, you want to call the procedure every time a row is deleted from the ITEMS_FOR_SALE table. The trigger would look like this:

 CREATE TRIGGER cancel_orders
   BEFORE DELETE ON sales.items_for_sale
   REFERENCING OLD ROW AS deleted_item
   FOR EACH ROW
   WHEN deleted_item.date_available <= CURRENT_DATE
   CALL item_order_killer(deleted_item.item_id,deleted_item.vendor_id, 5)

After the CREATE TRIGGER statement (shown previously) is executed, every DELETE executed against the "items_for_sale" table generates a call (or calls, one per deleted row) to the coded procedure, unless the "date_available" has not been reached, meaning that no orders yet exist.