Trigger examples

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

---

The syntax for a dropping a Trigger is:

DROP TRIGGER trigger_name;

For example:

If you had a trigger called orders_before_insert, you could drop it with the following command:

DROP TRIGGER orders_before_insert;

Disable a Trigger

---

The syntax for a disabling a Trigger is:

ALTER TRIGGER trigger_name DISABLE;

For example:

If you had a trigger called orders_before_insert, you could disable it with the following command:

ALTER TRIGGER orders_before_insert DISABLE;

Disable all Triggers on a table

---

The syntax for a disabling all Triggers on a table is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

For example:

If you had a table called orders and you wanted to disable all triggers on this table, you could execute the following command:

ALTER TABLE orders DISABLE ALL TRIGGERS;

Enable a Trigger

---

The syntax for a enabling a Trigger is:

ALTER TRIGGER trigger_name ENABLE;

For example:

If you had a trigger called orders_before_insert, you could enable it with the following command:

ALTER TRIGGER orders_before_insert ENABLE;

Enable all Triggers on a table

---

The syntax for a enabling all Triggers on a table is:

ALTER TABLE table_name ENABLE ALL TRIGGERS;

For example:

If you had a table called orders and you wanted to enable all triggers on this table, you could execute the following command:

ALTER TABLE orders ENABLE ALL TRIGGERS;