Oracle Database Triggers

What's a trigger? In the Oracle world, a trigger is an important system-level component that allows you to associate a "block of code" (usually PL/SQL or Java) with a specific system event, namely DML (SQL insert, update and delete) statements.

Oracle defines triggers as "procedures that are stored in the database and implicitly run, or fired, when something happens.What is the "something" that happens to make a trigger fire? There are 12 envents that can cause triggers to fire: Before/After during Insert/Update/Delete on a Row/Table.

Triggers are a special PL/SQL construct similar to procedures. However, a procedure is executed explicitly from another block via a procedure call, while a trigger is executed implicitly whenever the triggering event happens. The triggering event is either a INSERT, DELETE, or UPDATE command. The timing can be either BEFORE or AFTER. The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.

Trigger Type

• BEFORE INSERT
• AFTER INSERT
• BEFORE UPDATE
• AFTER UPDATE
• BEFORE DELETE
• AFTER DELETE

Syntax

CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT OR UPDATE OR DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   -- variable declarations

BEGIN
   -- trigger code

EXCEPTION
   WHEN ...
   -- exception handling

END;

Examples

1.
CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT OR UPDATE OR DELETE
   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;

2.
CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE OR UPDATE OR DELETE
   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;

Drop a Triggers

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

DROP TRIGGER orders_before_insert;

Disable/Enable Triggers

If you had a trigger called orders_before_insert, you could Disable/Enable it with the following commands.

ALTER TRIGGER orders_before_insert DISABLE;

ALTER TRIGGER orders_before_insert ENABLE;

If you had a table called Orders and you wanted to Disable/Enable all triggers on this table, you could execute the following commands.

ALTER TABLE orders DISABLE ALL TRIGGERS;

ALTER TABLE orders ENABLE ALL TRIGGERS;