Here is a working example:
drop table test_intervals;
CREATE TABLE test_intervals (
start_time NUMBER NOT NULL,
end_time NUMBER NOT NULL,primary key (start_time,end_time)
);
create or replace package test_mut as
type start_time_tab_type is table of number index by binary_integer;
start_time_tab start_time_tab_type;
end_time_tab start_time_tab_type;
end;
/
create or replace trigger testupd before update or insert on test_intervals
for each row
declare
m_cnt number := 0;
begin
test_mut.start_time_tab(test_mut.start_time_tab.count + 1) :=
:new.start_time;
test_mut.end_time_tab (test_mut.end_time_tab.count + 1) :=
:new.end_time;
end;
/
create or replace trigger testupd1 after update or insert on test_intervals
declare
m_cnt number := 0;
begin
for i in 1..test_mut.start_time_tab.count loop
dbms_output.put_line(i);
select count(*) into m_cnt
from test_intervals
where (test_mut.start_time_tab(i) between start_time and end_time
or
start_time between test_mut.start_time_tab(i) and
test_mut.end_time_tab(i))
and not(start_time = test_mut.start_time_tab(i) and end_time =
test_mut.end_time_tab(i));
if m_cnt <> 0
then
test_mut.start_time_tab.delete;
test_mut.end_time_tab.delete;
raise_application_error (-20001,' overlap error ');
end if;
end loop;
test_mut.start_time_tab.delete;
test_mut.end_time_tab.delete;
end;
/
Try different inserts/updates.
Regards,
Waleed
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
