Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Complex Integrity Checking

RE: Complex Integrity Checking

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Wed, 05 Jun 2002 08:43:37 -0800
Message-ID: <F001.004752DF.20020605084337@fatcity.com> 





Iulian, this is what you want, NO? (except this works for date fields not
number fields as you've put in


your latest posts)...  This is done using two triggers.



SQL> insert into interval


values('01-JAN-2002','01-MAR-2002');


  2


1 row created.



SQL> insert into interval


values('03-MAR-2002','26-MAR-2002');


  2


1 row created.



SQL> insert into interval


values('03-FEB-2002','14-MAR-2002');


  2  insert into interval


            *


ERROR at line 1:

ORA-20000: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'






SQL> insert into interval


values('01-DEC-1999','01-JAN-2002');


  2  insert into interval


            *


ERROR at line 1:

ORA-20000: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'






SQL> insert into interval


values('05-JAN-2002','01-FEB-2002');


  2  insert into interval


            *


ERROR at line 1:

ORA-20000: date overlap 03-FEB-02 14-MAR-02
ORA-06512: at "RHUNTLEY.SINTERVAL", line 23
ORA-04088: error during execution of trigger 'RHUNTLEY.SINTERVAL'   





-----Original Message-----


Sent: Wednesday, June 05, 2002 10:39 AM


To: Multiple recipients of list ORACLE-L




-----Original Message-----


Sent: Wednesday, June 05, 2002 4:53 PM


To: Multiple recipients of list ORACLE-L






This email has been tested for viruses by F-Secure Antivirus
administered by IT Network Department.





 two questions: How many records do you insert into that table before a
commit ?



Is the whole issue simply mutating table error when running some business
logic in an insert/update trigger for the intervals table?



Regards,



Waleed



I'm sorry bu I can't answer to your questions because I don't see the point.




Here's a test table:


CREATE TABLE intervals (

	start_time NUMBER NOT NULL,
	end_time NUMBER NOT NULL




)



Here are some statemens:



INSERT INTO intervals


(START_TIME,END_TIME)




VALUES 



(3,5)



/


INSERT INTO intervals


(START_TIME,END_TIME)




VALUES 



(2,3)



/


INSERT INTO intervals


(START_TIME,END_TIME)




VALUES 



(7,8)





What I want is that the integrity rule (no overlapped intervals) be
operational even if i insert a new record or more or update one or more.
Think of it the same way an unique key works.
This is a simplified table for example purpose. In fact my application is a
resource scheduler, so I want a resource not to be assigned for more than 1
client at the same time.


Here the start_time and end_time are of number type just for testing, but of
course it'll be of date type.



I'm starting to think that what I want, can be done in a simple, clean
manner but using complex workarounds, isn't it?
Thanks!



iulian



-----Original Message-----


To: Multiple recipients of list ORACLE-L
Sent: 6/5/02 4:33 AM



First of all I want to thank you all for your answers.
Let's take'em one by one:







Attn: Mercadante, Thomas F [NDATFM_at_labor.state.ny.us]
- I cannont use "instead of" trigger because of this error:



ORA-25002: cannot create INSTEAD OF triggers on tables
Cause: Only BEFORE or AFTER triggers can be created on a table.
Action: Change the trigger type to BEFORE or AFTER.



I have an Oracle database version 9.0.1.1.1








Attn: Stephane Faroult [sfaroult_at_oriole.com]
- for insert your approach works (although I have to change a bit the
select


in exists condirion) but what about the update statements. 
- moreover i think this will not keep my integrity rule consistent, if
someone try to simply use typical insert&update statements.








Attn: Khedr, Waleed [Waleed.Khedr_at_FMR.COM]
- Can you give me an example for your unique function based index, I
mean


how can you assign an unique number for various intervals. 
- anyway if this can be done I assume that would be a very nice, clean
solution








Attn: Richard Huntley [rhuntley_at_mindleaders.com], Gogala, Mladen
[MGogala_at_oxhp.com]


- this really doesn't suit my needs, create 2 tables instead of one








Attn: DENNIS WILLIAMS [DWILLIAMS_at_LIFETOUCH.COM], Aponte, Tony
[AponteT_at_hsn.net]


- I did make a function: 



FUNCTION check_for_overlapped_intervals (


    p_start_time IN NUMBER, 


    p_end_time IN NUMBER)


RETURN NUMBER



IS


    n NUMBER;


BEGIN




and use it in the trigger:



CREATE OR REPLACE TRIGGER bi_interval


BEFORE INSERT  OR UPDATE



ON intervals


REFERENCING NEW AS NEW OLD AS OLD



FOR EACH ROW



BEGIN



  IF check_for_overlapped_intervals(:new.start_time, :new.end_time) <> 0
THEN



    raise_application_error(-20100, 'Overlapped intervals');
  END IF;



END;


but still got the same mutating table error. Am I wrong someplace.








Thanks again. I try to test all of your solution and above are my
answers.


Can you still help me.


I simplify my problem using a table INTERVALS with 2 columns START_TIME,
END_TIME of NUMBER type.



CREATE TABLE intervals (

	start_time NUMBER NOT NULL,
	end_time NUMBER NOT NULL




)



Please try to insert some data and implement an integrity system like I
wanted


Regards



iulian








The information contained in this communication is confidential and 
may be legally privileged. It is intended solely for the use of the 
individual or entity to whom it is addressed and others authorised to 
receive it. If you are not the intended recipient you are hereby 
notified that any disclosure, copying, distribution or taking action in 
reliance of the contents of this information is strictly prohibited and 
may be unlawful. Orange Romania SA is neither liable for the proper,
complete transmission of the information contained in this communication
nor any delay in its receipt.






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Iulian.ILIES_at_orange.ro

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).
-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Iulian.ILIES_at_orange.ro

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).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Richard Huntley
  INET: rhuntley_at_mindleaders.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 - 11:43:37 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US