Oracle PL/SQL - Row-level triggers

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

Introduction

To react action in each row, you need to use row-level triggers.

The following trigger implements a rule: A manager may not receive a commission that exceeds his or her salary.

This rule is about the data in each row, so it should be implemented as row-level trigger.

In row-level triggers is that you can use :OLD and :NEW prefixes on each column of the table to reference the original and modified values.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w ww . j  a va  2s . 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> create or replace trigger emp_biu
  2    before insert or update
  3    on emp
  4    referencing new as new old as old
  5    for each row
  6  begin
  7        if  :new.job = 'MANAGER'
  8        and nvl(:new.sal,0)<nvl(:new.comm,0) then
  9             raise_application_error (-20999,'Managers should
 10                 not have commissions higher then salary!');
 11        end if;
 12  end;
 13  /
SQL>

Related Topic