Oracle PL/SQL - PL SQL Function Procedure Package DML triggers

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

Introduction

You can place triggers on INSERT/UPDATE/DELETE operations in any table.

You can define an event or group of events with timing of BEFORE or AFTER the event with which you want to fire the trigger.

Demo

SQL>
SQL> drop table emp;

Table dropped.-- w w w.jav  a  2  s  .  co m
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> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL> insert into emp values(7788, 'SCOTT', 'ANALYST', 7566,to_date('13-JUL-87','dd-mm-rr') - 85, 3000, null, 20);
SQL> insert into emp values(7902, 'FORD', 'ANALYST', 7566, to_date('3-12-1981','dd-mm-yyyy'), 3000, null, 20);
SQL> insert into emp values(7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980','dd-mm-yyyy'), 800, null, 20);
SQL> insert into emp values(7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-2-1981','dd-mm-yyyy'), 1600, 300, 30);
SQL> insert into emp values(7521, 'WARD', 'SALESMAN', 7698, to_date('22-2-1981','dd-mm-yyyy'), 1250, 500, 30);
SQL> insert into emp values(7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-9-1981','dd-mm-yyyy'), 1250, 1400, 30);
SQL> insert into emp values(7844, 'TURNER', 'SALESMAN', 7698, to_date('8-9-1981','dd-mm-yyyy'), 1500, 0, 30);
SQL> insert into emp values(7876, 'ADAMS', 'CLERK', 7788, to_date('13-JUL-87', 'dd-mm-rr') - 51, 1100, null, 20);
SQL> insert into emp values(7900, 'JAMES', 'CLERK', 7698, to_date('3-12-1981','dd-mm-yyyy'), 950, null, 30);
SQL> insert into emp values(7934, 'MILLER', 'CLERK', 7782,to_date('23-1-1982','dd-mm-yyyy'), 1300, null, 10);
SQL>
SQL> create or replace trigger emp_biu
  2  BEFORE INSERT OR UPDATE
  3  of sal, comm
  4  on emp
  5  for each row
  6  declare
  7       v_error_tx VARCHAR2(2000);
  8  begin
  9        if :new.comm + :new.sal > 10000
 10        then
 11             v_error_tx:=:old.ename||' cannot have that much!';
 12             raise_application_error(-20999,v_error_tx);
 13        end if;
 14  end;
 15  /

Related Topic