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;