This statement specifies that Oracle will fire this trigger BEFORE the INSERT/UPDATE or DELETE operation is executed.
- CREATE [ OR REPLACE ] TRIGGER trigger_name
- BEFORE INSERT or UPDATE or DELETE
- ON table_name
- [ FOR EACH ROW ]
- — variable declarations
- — trigger code
- WHEN …
- — exception handling
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.
- 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.
- CREATE TABLE “SUPPLIERS”
- ( “SUPPLIER_ID” NUMBER,
- “SUPPLIER_NAME” VARCHAR2(4000),
- “SUPPLIER_ADDRESS” VARCHAR2(4000)
You can use the following CREATE TRIGGER query to create a BEFORE INSERT or UPDATE or DELETE Trigger:
- CREATE OR REPLACE TRIGGER “SUPPLIERS_T1”
- insert or update or delete on “SUPPLIERS”
- for each row
- when the person performs insert/update/delete operations into the table.
- ALTER TRIGGER “SUPPLIERS_T1” ENABLE
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”.