Oracle Before Trigger

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

This statement specifies that Oracle will fire this trigger BEFORE the INSERT/UPDATE or DELETE operation is executed.

Syntax

  1. CREATE [ OR REPLACE ] TRIGGER trigger_name  
  2. BEFORE INSERT or UPDATE or DELETE  
  3.  ON table_name  
  4.   [ FOR EACH ROW ]  
  5. DECLARE  
  6.    -- variable declarations  
  7. BEGIN  
  8.    -- trigger code  
  9. EXCEPTION  
  10.    WHEN ...  
  11.    -- exception handling  
  12. END;  

Parameters

OR REPLACE: It is an optional parameter. It is used to re-create the trigger if it already exists. It facilitates you to change the trigger definition without using a DROP TRIGGER statement.

trigger_name: It specifies the name of the trigger that you want to create.

BEFORE INSERT or UPDATE or DELETE: It specifies that the trigger will be fired before the INSERT or UPDATE or DELETE operation is executed.

table_name: It specifies the name of the table on which trigger operation is being performed.

Limitations

  • BEFORE trigger cannot be created on a view.
  • You cannot update the OLD values.
  • You can only update the NEW values.

Oracle BEFORE Trigger Example

Consider, you have a "suppliers" table with the following parameters.

  1. CREATE TABLE  "SUPPLIERS"   
  2.    (    "SUPPLIER_ID" NUMBER,   
  3.     "SUPPLIER_NAME" VARCHAR2(4000),   
  4.     "SUPPLIER_ADDRESS" VARCHAR2(4000)  
  5.    )  
  6. /  

You can use the following CREATE TRIGGER query to create a BEFORE INSERT or UPDATE or DELETE Trigger:

  1. CREATE OR REPLACE TRIGGER  "SUPPLIERS_T1"   
  2. BEFORE  
  3. insert or update or delete on "SUPPLIERS"  
  4. for each row  
  5. begin  
  6. when the person performs insert/update/delete operations into the table.  
  7. end;  
  8. /  
  9. ALTER TRIGGER  "SUPPLIERS_T1" ENABLE  
  10. /  

Here the trigger name is "SUPPLIERS_T1" and it is fired BEFORE the insert or update or delete operation is executed on the table "suppliers".

Oracle Before Trigger