Learn how to create a BEFORE INSERT Trigger in Oracle with syntax and examples.
A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax to create a BEFORE INSERT Trigger in Oracle/PLSQL is:
CREATE [ OR REPLACE ] TRIGGER trigger_name BEFORE INSERT ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
OR REPLACE is optional. If specified, it allows you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement.
trigger_name is the name of the trigger to create.
BEFORE INSERT indicates that the trigger will fire before the INSERT operation is executed.
table_name is the name of the table that the trigger is created on.
Let's look at an example of how to create an BEFORE INSERT trigger using the CREATE TRIGGER statement.
If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2), create_date date, created_by varchar2(10) );
We could then use the CREATE TRIGGER statement to create an BEFORE INSERT trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_insert BEFORE INSERT ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing INSERT into table SELECT user INTO v_username FROM dual; -- Update create_date field to current system date :new.create_date := sysdate; -- Update created_by field to the username of the person performing the INSERT :new.created_by := v_username; END;