RE: Complex Integrity Checking

Thu, 06 Jun 2002 09:06:20 -0700

Title: 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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
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:[EMAIL PROTECTED]]
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
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 [[EMAIL PROTECTED]]
- 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 [[EMAIL PROTECTED]]
- 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 [[EMAIL PROTECTED]]
- 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 [[EMAIL PROTECTED]], Gogala, Mladen
[[EMAIL PROTECTED]]
- this really doesn't suit my needs, create 2 tables instead of one


========================================================================
====
Attn: DENNIS WILLIAMS [[EMAIL PROTECTED]], Aponte, Tony
[[EMAIL PROTECTED]]
- 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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Attachment: mutating_tables.sql
Description: Binary data

Reply via email to