Triggers

A trigger is a stored subprogram associated with a table, view, or event. The trigger can be invoked once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be invoked before or after the event.

The trigger in example is invoked whenever salaries in the employees table are updated. For each update, the trigger writes a record to the emp_audit table.

Example:

Creating a Trigger

SQL> CREATE TABLE emp_audit (
2    emp_audit_id  NUMBER(6),
3    up_date       DATE,
4    new_sal       NUMBER(8,2),
5    old_sal       NUMBER(8,2)
6  );

Table created.

SQL> CREATE OR REPLACE TRIGGER audit_sal
2    AFTER UPDATE OF salary
3      ON employees
4        FOR EACH ROW
5  BEGIN
6    INSERT INTO emp_audit
7      VALUES(:old.employee_id,SYSDATE,:new.salary,
            :old.salary);
8  END;
9  /

Trigger created.

SQL> update employees set salary=5000
2  where salary < 3000;

24 rows updated.


SQL> select * from emp_audit;
EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   116 25-JUL-09       5000       2900
   117 25-JUL-09       5000       2800
   118 25-JUL-09       5000       2600
   119 25-JUL-09       5000       2500
   126 25-JUL-09       5000       2700
   127 25-JUL-09       5000       2400
   128 25-JUL-09       5000       2200
   130 25-JUL-09       5000       2800
   131 25-JUL-09       5000       2500
   132 25-JUL-09       5000       2100
   134 25-JUL-09       5000       2900

EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   135 25-JUL-09       5000       2400
   136 25-JUL-09       5000       2200
   139 25-JUL-09       5000       2700
   140 25-JUL-09       5000       2500
   143 25-JUL-09       5000       2600
   144 25-JUL-09       5000       2500
   182 25-JUL-09       5000       2500
   183 25-JUL-09       5000       2800
   190 25-JUL-09       5000       2900
   191 25-JUL-09       5000       2500
   195 25-JUL-09       5000       2800

EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   198 25-JUL-09       5000       2600
   199 25-JUL-09       5000       2600

24 rows selected.