Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g570RoK31556 for ; Thu, 6 Jun 2002 20:27:50 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id JAA46450; Thu, 6 Jun 2002 09:20:18 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0047628B; Thu, 06 Jun 2002 07:53:22 -0800 Message-ID: Date: Thu, 06 Jun 2002 07:53:22 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Richard Huntley Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Richard Huntley Subject: RE: Complex Integrity Checking Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----_=_NextPart_000_01C20D69.0B3D5050" ------_=_NextPart_000_01C20D69.0B3D5050 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C20D69.0B3D5050" ------_=_NextPart_001_01C20D69.0B3D5050 Content-Type: text/plain; charset="iso-8859-1" Iulian, here is everything you need to recreate that, table, package, row level trigger, statement level trigger and test data. Once you've duplicated this, feel free to modify and hopefully you'll be able to do this for your specific case. -----Original Message----- Sent: Thursday, June 06, 2002 3:13 AM To: Multiple recipients of list ORACLE-L Ok Richard, this seems to be what I want. I read carefully the message but I didn't find the trigger RHUNTLEY.SINTERVAL How did you do that? Thanks! iulian -----Original Message----- Sent: Wednesday, June 05, 2002 7:44 PM To: Multiple recipients of list ORACLE-L 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@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@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@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@mindleaders.com], Gogala, Mladen [MGogala@oxhp.com] - this really doesn't suit my needs, create 2 tables instead of one ======================================================================== ==== Attn: DENNIS WILLIAMS [DWILLIAMS@LIFETOUCH.COM], Aponte, Tony [AponteT@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@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@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@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@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@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@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). ------_=_NextPart_001_01C20D69.0B3D5050 Content-Type: text/html; charset="iso-8859-1" RE: Complex Integrity Checking
Iulian, here is everything you need to recreate that, table, package, row level trigger, statement level trigger and test data.
Once you've duplicated this, feel free to modify and hopefully you'll be able to do this for your specific case.
 
-----Original Message-----
From: Iulian.ILIES@orange.ro [mailto:Iulian.ILIES@orange.ro]
Sent: Thursday, June 06, 2002 3:13 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Complex Integrity Checking

Ok Richard, this seems to be what I want.
I read carefully the message but I didn't find the trigger RHUNTLEY.SINTERVAL
How did you do that?
Thanks!
 
iulian
 
-----Original Message-----
From: Richard Huntley [mailto:rhuntley@mindleaders.com]
Sent: Wednesday, June 05, 2002 7:44 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Complex Integrity Checking

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-----
From: Iulian.ILIES@orange.ro [mailto:Iulian.ILIES@orange.ro]
Sent: Wednesday, June 05, 2002 10:39 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Complex Integrity Checking


-----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@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@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@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@mindleaders.com], Gogala, Mladen
[MGogala@oxhp.com]
- this really doesn't suit my needs, create 2 tables instead of one


========================================================================
====
Attn: DENNIS WILLIAMS [DWILLIAMS@LIFETOUCH.COM], Aponte, Tony
[AponteT@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@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@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@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@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@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@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).

------_=_NextPart_001_01C20D69.0B3D5050-- ------_=_NextPart_000_01C20D69.0B3D5050 Content-Type: application/octet-stream; name="mutating_tables.sql" Content-Disposition: attachment; filename="mutating_tables.sql" SQL> desc interval Name Null? Type ----------------------------------------- -------- ---------------------------- S DATE E DATE drop table interval; create table interval ( s date, e date ); --PACKAGE create or replace package interval_package as type t_start is table of date index by binary_integer; type t_end is table of date index by binary_integer; v_s t_start; v_e t_end; v_count binary_integer :=0; end interval_package; / --ROW LEVEL TRIGGER create or replace trigger RInterval before insert or update on interval for each row begin /* just record values, do not perform any updates in order to avoid ORA-4091 */ interval_package.v_count := interval_package.v_count + 1; interval_package.v_s(interval_package.v_count) := :new.s; interval_package.v_e(interval_package.v_count) := :new.e; --dbms_output.put_line('first count: '||interval_package.v_count); end RInterval; / --STATEMENT LEVEL TRIGGER create or replace trigger SInterval after insert or update on interval declare v_start date; v_end date; v_start_curr date; v_end_curr date; v_matches binary_integer; begin /* loop through each date range being inserted or updated and verify there is no overlap */ for v_i in 1..interval_package.v_count loop --dbms_output.put_line('count: '||interval_package.v_count); v_start := interval_package.v_s( v_i ); v_end := interval_package.v_e( v_i ); --dbms_output.put_line('s '||v_start); --dbms_output.put_line('e '||v_end); --look for overlap select count(*) into v_matches from interval where v_start < e and v_end > s; --if overlap, raise error --dbms_output.put_line('matches: '||v_matches); if v_matches > 1 then raise_application_error(-20000,'date overlap '||v_start||' '||v_end); end if; end loop; interval_package.v_count := 0; interval_package.v_s.DELETE; interval_package.v_e.DELETE; end SInterval; / --should work as table is empty insert into interval values('01-JAN-2002','01-MAR-2002'); --should work, does not overlap insert into interval values('03-MAR-2002','26-MAR-2002'); --end date overlaps insert into interval values('03-FEB-2002','14-MAR-2002'); --start date overlaps insert into interval values('01-DEC-1999','01-JAN-2002'); --start date and end dates overlap insert into interval values('05-JAN-2002','01-FEB-2002'); ------_=_NextPart_000_01C20D69.0B3D5050-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Huntley INET: rhuntley@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@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).