Triggers : Trigger « Trigger « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Trigger
3. Trigger

A trigger is a procedure that is run automatically by the database when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a table.

Triggers are useful for doing things like advanced auditing of changes made to column values in a table.

When a Trigger Runs

1. A trigger can fire before or after the SQL statement runs.
2. A trigger can may be run once for every row affected. Such a trigger is known as a row-level trigger.
3. A trigger can may be run for all the rows. Such trigger is known as a statement-level trigger.
4. A row-level trigger has access to the old and new column values when the trigger fires as a result of an UPDATE statement on that column.
5. The firing of a trigger may also be limited using a trigger condition.

Different events may fire a trigger, but these events are always divided into three groups:

1. DML triggers,
2. INSTEAD OF triggers, and
3. system event triggers.

DML triggers are the triggers on INSERT/UPDATE/DELETE operations in any table.

SQL> CREATE TABLE employee_history (
  2     name VARCHAR2(100),
  3     description VARCHAR2(255),
  4     occurred_on DATE);

Table created.

SQL>
SQL>
SQL> CREATE TABLE employee_compensation (
  2     company VARCHAR2(100),
  3     name VARCHAR2(100),
  4     compensation NUMBER,
  5     layoffs NUMBER);

Table created.

SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE employee_audit (
  2     name IN VARCHAR2,
  3     description IN VARCHAR2,
  4     occurred_on IN DATE
  5     )
  6  IS
  7     PRAGMA AUTONOMOUS_TRANSACTION;
  8  BEGIN
  9     INSERT INTO employee_history VALUES (
 10        employee_audit.name,
 11        employee_audit.description,
 12        employee_audit.occurred_on
 13        );
 14     COMMIT;
 15  END;
 16  /

Procedure created.

SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE TRIGGER bef_ins_ceo_comp
  2  BEFORE INSERT ON employee_compensation FOR EACH ROW
  3  DECLARE
  4     ok BOOLEAN := TRUE;
  5  BEGIN
  6     IF ok
  7     THEN
  8        employee_audit (
  9           :new.name, 'BEFORE INSERT', SYSDATE);
 10     END IF;
 11  END;
 12  /

Trigger created.

SQL>
SQL>
SQL>
SQL> SELECT name,
  2         description,
  3         TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
  4    FROM employee_history;

no rows selected

SQL>
SQL> BEGIN
  2     INSERT INTO employee_compensation VALUES ('M', 'J', 9100000, 2700);
  3
  4     INSERT INTO employee_compensation VALUES ('A', 'H', 33200000, 3300);
  5
  6     INSERT INTO employee_compensation VALUES ('E', 'G', 10700000, 20100);
  7
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT name,
  2         description,
  3         TO_CHAR (occurred_on, 'MM/DD/YYYY HH:MI:SS') occurred_on
  4    FROM employee_history;

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------
J
BEFORE INSERT
07/24/2008 08:03:08

H
BEFORE INSERT
07/24/2008 08:03:08

NAME
--------------------------------------------------------------------------------
DESCRIPTION
--------------------------------------------------------------------------------
OCCURRED_ON
-------------------

G
BEFORE INSERT
07/24/2008 08:03:08


SQL>
SQL>
SQL> DROP TABLE employee_compensation;

Table dropped.

SQL>
SQL> DROP TABLE employee_history;

Table dropped.