create or replace trigger tgr_conflict 
before insert or update
on resource_schedules for each row
 l_conflict boolean;
begin  

 -- Call procedure to mark rows that conflict with the one
 -- we are updating. Return l_conflict so we know if we
 -- have conflicts.
 check_conflicts(:new.resource_sched_id, l_conflict);

 -- if we have conflicts, set the flag for the current record
 if l_conflict
 then
   :new.conflict := 'Y';
 end if;

end;

create or replace procedure check_conflicts(
  pi_sched_id in number,
  po_conflict out boolean) 
is
begin

 po_conflict := false;

 -- find conflicting rows and update them
 for r1 in (select rowid
             from resource_schedules a, resource_schedules b
             where a.resource_sched_id = pi_sched_id
               and a.resource_sched_id <> b.resource_sched_id
               and a.resource_id = b.resource_id
               and ( (a.scd_start between b.scd_start and b.scd_end)
                     or (a.scd_end between b.scd_start and b.scd_end) )
 loop

   update resource_schedules
   set conflict = 'Y'
   where rowid = r1.rowid;

   po_conflict := true;

 end loop;

end check_conflicts;