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).
Received on Wed Jun 05 2002 - 03:33:22 CDT