What is a trigger and what is its purpose give an

This preview shows page 7 - 10 out of 26 pages.

17. What is a trigger, and what is its purpose? Give an example.
21
Chapter 8 Advanced SQL Triggers are also excellent for enforcing data constraints that cannot be directly enforced by the data model. For example, suppose that you must enforce the following business rule: If the quantity on hand of a product falls below the minimum quantity, the P_REORDER attribute must the automatically set to 1. To enforce this business rule, you can create the following TRG_PRODUCT_REORDER trigger: CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER BEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN ON PRODUCT FOR EACH ROW BEGIN IF :NEW.P_ONHAND <= :NEW.PROD_MIN THEN NEW.P_REORDER := 1; ELSE :NEW.P_REORDER := 0; END IF; END; 18. What is a stored procedure, and why is it particularly useful? Give an example.
For example, the following PRC_LINE_ADD stored procedure will add a new invoice line to the LINE table and it will automatically retrieve the correct price from the PRODUCT table. 22
Chapter 8 Advanced SQL

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture