Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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-----
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-----
<mailto:Iulian.ILIES_at_orange.ro> ]
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
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:
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
FUNCTION check_for_overlapped_intervals (
p_start_time IN NUMBER,
p_end_time IN NUMBER)
RETURN NUMBER
IS
n NUMBER;
BEGIN
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.
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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com <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-LReceived on Thu Jun 06 2002 - 02:13:20 CDT
(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 <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 <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: 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).