Oracle-L: RE: Complex Integrity Checking

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 05 Jun 2002 05:53:22 -0800
Message-ID: <F001.00474EA4.20020605055322@fatcity.com>

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

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

when this select have records to count
means that the new interval overlap an existing one
and still is not corectly implement for update stament
where it should not consider the current record SELECT COUNT(*) INTO n FROM intervals WHERE start_time < p_end_time AND end_time > p_start_time; RETURN(n); END;

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

Received on Wed Jun 05 2002 - 08:53:22 CDT