SQL> desc interval
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 S                                                  DATE
 E                                                  DATE

drop table interval;
create table interval (
s	date,
e	date
);

--PACKAGE
create or replace package interval_package
as

type t_start is table of date index by binary_integer;
type t_end is table of date index by binary_integer;

v_s	t_start;
v_e	t_end;
v_count binary_integer :=0;

end interval_package;
/

--ROW LEVEL TRIGGER
create or replace trigger RInterval
before insert or update on interval
for each row
begin
/* just record values, do not perform any updates in order
   to avoid ORA-4091 */
  interval_package.v_count := interval_package.v_count + 1;
  interval_package.v_s(interval_package.v_count) := :new.s;
  interval_package.v_e(interval_package.v_count) := :new.e;
  --dbms_output.put_line('first count: '||interval_package.v_count);
end RInterval;
/

--STATEMENT LEVEL TRIGGER
create or replace trigger SInterval
after insert or update on interval
declare
  v_start	date;
  v_end		date;
  v_start_curr  date;
  v_end_curr    date;
  v_matches	binary_integer;
begin
/* loop through each date range being inserted or updated
   and verify there is no overlap */
for v_i in 1..interval_package.v_count loop
  --dbms_output.put_line('count: '||interval_package.v_count);
  v_start := interval_package.v_s( v_i );
  v_end := interval_package.v_e( v_i );
  --dbms_output.put_line('s '||v_start);
  --dbms_output.put_line('e '||v_end);

  --look for overlap
  select count(*)
  into v_matches
  from interval
  where v_start < e
  and v_end > s;

  --if overlap, raise error
  --dbms_output.put_line('matches: '||v_matches);
  if v_matches > 1 then
  	raise_application_error(-20000,'date overlap '||v_start||' '||v_end);
  end if;
 
end loop;

interval_package.v_count := 0;
interval_package.v_s.DELETE;
interval_package.v_e.DELETE;

end SInterval;
/

--should work as table is empty
insert into interval
values('01-JAN-2002','01-MAR-2002');

--should work, does not overlap
insert into interval
values('03-MAR-2002','26-MAR-2002');

--end date overlaps
insert into interval
values('03-FEB-2002','14-MAR-2002');

--start date overlaps
insert into interval
values('01-DEC-1999','01-JAN-2002');

--start date and end dates overlap
insert into interval
values('05-JAN-2002','01-FEB-2002');
