RE: Complex Integrity Checking

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 05 Jun 2002 11:18:07 -0800
Message-ID: <F001.004756DE.20020605111807@fatcity.com>

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: Waleed.Khedr_at_FMR.COM

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: ListGuru_at_fatcity.com (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).

Received on Wed Jun 05 2002 - 14:18:07 CDT