Creating 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

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;

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;

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;