Re: SQL Question

From: <bialik_at_wis.weizmann.ac.il>
Date: Fri, 16 Oct 1998 21:43:17 GMT
Message-ID: <708elm$qvq$1@nnrp1.dejanews.com>

Hi.

I think following solution will work for Oracle 7 :

1. CREATE INDEX tax$xxx on tax ( id, final, init ).
2. CREATE TRIGGER ... ON INSERT FOR EACH ROW DECLARE i_date DATE; f_date DATE; BEGIN SELECT /*+ INDEX ( tax tax$xxx ) */ init, final INTO i_date, f_date FROM tax WHERE id = :new.id AND final >= :new.init AND rownum = 1; IF ( :new.init >= i_date ) OR ( :new.final >= i_date AND :new.final <= f_date ) THEN RAISE_APPLICATION_ERROR ... EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END;

  The solution will work for V7 on INSERT only ( for UPDATE you will   get MUTATING table error ).
  In version 8.x you can use INSTEAD trigger on VIEW to bypass that   problem.

  Good luck. Michael.
In article <707g3u$nig$1_at_duke.telepac.pt>,   "Fernando Carvalho" <f.carvalho_at_mail.telepac.pt> wrote:
> With the following table:
>
> create table Tax
> (
> ID number not null,
> Init date not null,
> Final date not null,
> constraint pk+AF8-tax primary key (ID)
> )
>
> How to create a constraint (or trigger) that doesn't let to insert
> overlapping date intervals?
>
> Regards
>
> Fernando Carvalho
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Oct 16 1998 - 16:43:17 CDT