Insert, update, and delete Triggers in Oracle

Now that you have seen the syntax of triggers, you will view another example of how to code a trigger and then complete an exercise where you create three triggers of your own. First, however, look at the table below to see a summary of the combinations of action, timing, and scope that can be used when creating table triggers.There are potentially 12 different triggers that can be set up for any database table.

• How to use a trigger in Oracle
  When creating a trigger, consider how the trigger affects the data in the table. For example, if you wish to change the value of one column of data when inserting a row, your trigger should fire before the insert and should fire once per row. A trigger that is used to record who performs updates on a table can fire either before or after the update and should fire once per transaction. A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.
  The Syntax for creating a trigger is:
  

   CREATE [OR REPLACE ] TRIGGER trigger_name 
   {BEFORE | AFTER | INSTEAD OF } 
   {INSERT [OR] | UPDATE [OR] | DELETE} 
   [OF col_name] 
   ON table_name 
   [REFERENCING OLD AS o NEW AS n] 
   [FOR EACH ROW] 
   WHEN (condition)  
   BEGIN 
     --- sql statements  
   END; 
  

The most commonly used triggers are the first three listed in the table below.

Action	Timing	Scope	Syntax example
Update	Before	Once per row	CREATE TRIGGER PRE_UPDATE BEFORE UPDATE ON CUSTOMER FOR EACH ROW...
Insert	Before	Once per row	CREATE TRIGGER PRE_INSERT BEFORE INSERT ON CUSTOMER FOR EACH ROW...
Delete	Before	Once per row	CREATE TRIGGER PRE_DELETE BEFORE DELETE ON CUSTOMER FOR EACH ROW...
Update	After	Once per row	CREATE TRIGGER POST_UPDATE BEFORE UPDATE ON CUSTOMER FOR EACH ROW...
Insert	After	Once per row	CREATE TRIGGER POST_INSERT BEFORE INSERT ON CUSTOMER FOR EACH ROW...
Delete	After	Once per row	CREATE TRIGGER POST_DELETE BEFORE DELETE ON CUSTOMER FOR EACH ROW...
Update	Before	Once per transaction	CREATE TRIGGER PRE_UPDATE BEFORE UPDATE ON CUSTOMER...
Insert	Before	Once per transaction	CREATE TRIGGER PRE_INSERT BEFORE INSERT ON CUSTOMER...
Delete	Before	Once per transaction	CREATE TRIGGER PRE_DELETE BEFORE DELETE ON CUSTOMER...
Update	After	Once per transaction	CREATE TRIGGER POST_UPD_TRANS BEFORE UPDATE ON CUSTOMER...
Insert	After	Once per transaction	CREATE TRIGGER POST_INS_TRANS BEFORE INSERT ON CUSTOMER...
Delete	After	Once per transaction	CREATE TRIGGER POST_DEL_TRANS BEFORE DELETE ON CUSTOMER...

This trigger's purpose is to compare the current sale price of a product to the current cost of the product. If the sale price is lower than the cost, the trigger fails which in turn causes the update to fail. This prevents a user from setting a money-losing price for any product.

The steps for creating a `BEFORE UPDATE` row-level trigger in Oracle 11g R2. Here's an explanation and a slight refinement to ensure everything is accurate:
Step-by-Step Verification and Explanation:

1. Trigger Definition Start:

   • CREATE OR REPLACE TRIGGER PRE_UPD_PRODUCT
             

   • This is correct. This starts the creation of the trigger and ensures any existing trigger with the same name is replaced.
2. Trigger Timing and Table:

   • BEFORE UPDATE ON PRODUCT
             

   • Correct. Specifies that the trigger should fire before the UPDATE operation on the PRODUCT table.
3. Row-Level Trigger:

   • FOR EACH ROW
             

   • Correct. This ensures the trigger fires for each row being updated, rather than firing once for the entire UPDATE statement.
4. Declaring a User-Defined Exception:

   • PRICE_TOO_LOW EXCEPTION;
             

   • Correct. A user-defined exception is declared to handle cases where the price is lower than the cost.
5. Condition Testing:

   • IF :NEW.SALE_PRICE < :NEW.STORE_COST THEN
     RAISE PRICE_TOO_LOW;
     END IF;
             

   • This part is correct. The :NEW keyword is used to reference the new values being updated in the row. If the condition is met, the PRICE_TOO_LOW exception is raised.
6. Exception Section:

   • EXCEPTION
     WHEN PRICE_TOO_LOW THEN
     RAISE_APPLICATION_ERROR(-2000, 'Raise price');
             

   • This is correct. When the PRICE_TOO_LOW exception is raised, it is caught here, and the RAISE_APPLICATION_ERROR procedure is used to abort the update with a custom error message and code.
7. Completing the Trigger:

   • END;
     / 
             

   • Correct. The END; closes the trigger body, and the / executes the trigger creation in SQL*Plus or similar tools.

Complete Trigger Code: Below is the complete trigger code based on the steps provided:

CREATE OR REPLACE TRIGGER PRE_UPD_PRODUCT
BEFORE UPDATE ON PRODUCT
FOR EACH ROW
DECLARE
  PRICE_TOO_LOW EXCEPTION;
BEGIN
  IF :NEW.SALE_PRICE < :NEW.STORE_COST THEN
    RAISE PRICE_TOO_LOW;
  END IF;
EXCEPTION
  WHEN PRICE_TOO_LOW THEN
    RAISE_APPLICATION_ERROR(-2000, 'Raise price');
END;
/

Additional Notes:

1. Trigger Name: Ensure the name PRE_UPD_PRODUCT is meaningful and adheres to your naming conventions.
2. Validation: After creating the trigger, you can test it by attempting to update the PRODUCT table with a SALE_PRICE less than the STORE_COST.
3. Error Code Range: Oracle recommends using custom error codes in the range -20000 to -20999. The code -2000 you provided is slightly outside this range, but it should still work.

The steps outlined, combined with the complete code, are valid and should result in a correctly functioning trigger.

Click the Exercise link below to check your knowledge about triggers.
Create Three Triggers - Exercise
The next lesson shows you how to combine multiple triggers into a single trigger.