RelationalDBDesign RelationalDBDesign


Creating Triggers  «Prev 

Create a pre-update trigger in Oracle

  1. You will create a trigger that fires before updating each row of the PRODUCT table. Type in the following text to start the trigger definition:
    CREATE OR REPLACE TRIGGER PRE_UPD_PRODUCT
  2. The next line defines the timing of the trigger. Type this line and press Enter:
    BEFORE UPDATE ON PRODUCT
  3. The next line tells the database to fire the trigger for every row. Type this line and press Enter:
    FOR EACH ROW
  4. I have filled in the next two lines for you, which are required in the trigger syntax. The trigger's primary goal is to cause an update to fail if the price is lower than the cost of the product. Typically, you raise an exception when you want a trigger to fail. Because there is no pre-defined exception (like NO DATA FOUND, for example) for the condition we are testing, we must define our own exception. You will use this exception if the sale price is lower than the cost of the product. To declare the user-defined exception, type this line and press Enter: PRICE_TOO_LOW EXCEPTION;
  5. I have filled in the next line for you, just to save some steps. This sets the stage for testing the condition that will either raise or not raise the PRICE_TOO_LOW exception.
    Type in the following line to test for a sale price that is lower than the cost of the product. Then press Enter.
    IF :NEW.SALE_PRICE < :NEW.STORE_COST THEN
  6. The next line raises the user-defined exception. Type this line and press Enter:
    RAISE PRICE_TOO_LOW;
  7. I filled in the END IF; line, which closes the IF statement. Since there are no more actions to be taken by the trigger, it is time to define the exception that was declared near the beginning of the trigger command. To start the exception section, type this line and press Enter:
    EXCEPTION
  8. This is the point where you create the user-defined exception. The first line identifies the exception. Lines below this one define the actions taken when this exception is raised. Type the following line and press Enter.
    WHEN PRICE_TOO_LOW THEN
  9. This exception must make the trigger fail, which aborts the update. This is done by invoking the RAISE_APPLICATION_ERROR procedure. The two parameters needed for this procedure are the error number (-2000) and the error message ('Raise price'). Type:
    RAISE_APPLICATION_ERROR(-2000,'Raise price');
    
  10. I have added the two closing END; lines. This completes the definition of the trigger. Create the trigger by executing the whole PL/SQL command. To do this, type a forward slash and press Enter:
    /
  11. Congratulations! You have created a trigger.