Triggers in Oracle PL/SQL ?

Oracle/PLSQL Topics: Creating Triggers

Insert Triggers:

BEFORE INSERT Trigger

AFTER INSERT Trigger

Update Triggers:

BEFORE UPDATE Trigger

AFTER UPDATE Trigger

Delete Triggers:

BEFORE DELETE Trigger

AFTER DELETE Trigger

Drop Triggers:

Drop a Trigger

Disable/Enable Triggers:

Disable a Trigger

Disable all Triggers on a table

Enable a Trigger

Enable all Triggers on a table


Oracle/PLSQL: BEFORE INSERT Trigger

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

The syntax for an BEFORE INSERT Trigger 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;

trigger_name is the name of the trigger to create.

Restrictions:

* You can not create a BEFORE trigger on a view.
* You can update the :NEW values.
* You can not update the :OLD values.


For example:

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 create a 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;