Oracle PL/SQL - Statement-level triggers

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

Introduction

Use statement-level triggers to check business rules that are not row dependent.

For example, there is a rule stating that nobody can delete or create new emp over a weekend.

This rule concerns the behavior of the whole EMPLOYEE table.

You could implement it as a statement-level trigger.

By default, triggers are statement-level so you don't need to specify the trigger type.

Demo

SQL>
SQL> drop table emp;

Table dropped.--   w w  w  . j ava  2 s  .co  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> create or replace trigger emp_bid
  2    before insert or delete                                                  
  3    on emp
  4    referencing new as new old as old
  5  begin
  6        if to_char(sysdate,'Dy') in ('Sat','Sun') then
  7             raise_application_error
  8              (-20999,'No create/delete emp on weekend!');
  9        end if;
 10  end;
 11  /
SQL>

Related Topic