Oracle PL/SQL - Controlling when a trigger fires

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Function Procedure Package
  4. Triggers

Introduction

You may set triggers to execute either before or after the database event to which they are tied.

BEFORE EVENT triggers are for preventing the event from actually happening.

AFTER EVENT triggers are for the cleaning up jobs.

An example of an AFTER EVENT trigger is shown in the following code.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- from  w  ww  . j  av  a  2  s  . c o  m

Elapsed: 00:00:00.02
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL> insert into emp values(7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL>
SQL> alter table emp add note_tx varchar2(2000);

Table altered.

SQL>
SQL> create or replace trigger emp_aiu
  2     after insert or update of comm, sal
  3     on emp
  4     referencing new as new old as old
  5     for each row
  6  begin
  7     update emp
  8     set note_tx = note_tx||chr(10)||'Update of '||:new.empNo
  9     where empNo = :new.mgr;
 10  end;
 11  /

SQL>

The trigger is fired after INSERT or UPDATE if the columns COMM or SAL are modified.

Therefore, you can be sure that the change already occurred.

In AFTER EVENT row-level triggers you can use :NEW and :OLD variables, but you can't change the value of the NEW variable.

The column you're changing should be excluded from the list of columns that cause the trigger to fire to avoid an infinite loop.

Related Topic