Oracle/PLSQL Topics: Triggers

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;

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;

BEFORE UPDATE Trigger

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

The syntax for an BEFORE UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    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), 
 updated_date date, 
 updated_by varchar2(10) 
); 


We could then create a BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update
BEFORE UPDATE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE on the table
    SELECT user INTO v_username
    FROM dual;

    -- Update updated_date field to current system date
    :new.updated_date := sysdate;

    -- Update updated_by field to the username of the person performing the UPDATE
    :new.updated_by := v_username;

END;

AFTER UPDATE Trigger

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

The syntax for an AFTER UPDATE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
    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 UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE into table
    SELECT user INTO v_username
    FROM dual;

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

END;

BEFORE DELETE Trigger

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

The syntax for an BEFORE DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
    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) 
); 

We could then create a BEFORE DELETE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on 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,
       delete_date,
       deleted_by )
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

AFTER DELETE Trigger

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

The syntax for an AFTER DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    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 DELETE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_delete
AFTER DELETE
    ON orders
    FOR EACH ROW

DECLARE
    v_username varchar2(10);

BEGIN

    -- Find username of person performing the DELETE on 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,
       delete_date,
       deleted_by)
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );

END;

Drop a Trigger

DROP TRIGGER trigger_name;