Oracle/PLSQL: AFTER INSERT Trigger

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.

The syntax for an AFTER INSERT Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER 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 an AFTER trigger on a view.
• You can not 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)
);

We could then create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN
    -- Find username of person performing the INSERT into the table
    SELECT user INTO v_username
    FROM dual;

    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       username )
    VALUES
     ( :new.order_id,
       :new.quantity,
       :new.cost_per_item,
       :new.total_cost,
       v_username );

END;