RE: Complex Integrity Checking

I don't believe that you can check for overlapping interval within a database trigger. For that, you'd need two tables: one to enter interval and one to check against. Of course, the second table should also be populated, most likely by a 'AFTER INSERT' trigger.

> -----Original Message-----
> From: Iulian.ILIES_at_orange.ro [mailto:Iulian.ILIES_at_orange.ro]
> Sent: Tuesday, June 04, 2002 12:08 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Complex Integrity Checking
>
>
> I'm reticent about putting the checking code in the application before
> insert&update statement, although I'm not sure why. I'm just
> thinking about
> concurency and all the implications.
> On the other hand I wanted to put the bussiness rules on the
> database side.
> Anyway, I'll use this approach, you told me, after all.
> But what if after I check for overlapped intervals, but
> before inserting,
> another user insert another record with an interval extending
> over mine. I
> know it sounds crazy but I really like to know how this kind
> of stuff are
> implemented. That's the way the unique values constraint
> work, I guess, and
> I'd like to know more about it.
>
> iulian
>
> -----Original Message-----
> Sent: Tuesday, June 04, 2002 6:24 PM
> To: Multiple recipients of list ORACLE-L
>
>
> maybe I'm being simplistic and I know this will impact performance but
> why not simply do a select to see if the condition exists before the
> insert or update?
>
>
> --- Iulian.ILIES_at_orange.ro wrote:
> > I said something like "the way the unique constraints work".
> > Ok. Here's my context.
> > I have a table say intervals and 2 columns start_time and end_time.
> > I want to check for overlapped intervals.
> > I know what conditions to check but I can't implement them.
> > Thanks!
> >
> > iulian
> >
> > -----Original Message-----
> > Sent: Tuesday, June 04, 2002 5:13 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> **********************************************************************
> > This email has been tested for viruses by F-Secure Antivirus
> > administered by IT Network Department.
> >
> **********************************************************************
> >
> >
> > Hi
> >
> >
> > if unique does not suit your need what exactly do you need to check?
> > duplicates: use primary key
> >
> >
> > Jack
> >
> >
> >
> >
> > Iulian.ILIES_at_oran
> >
> > ge.ro To: Multiple
> > recipients
> > of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Sent by: cc: (bcc: Jack
> > van
> > Zanen/nlzanen1/External/MEY/NL)
> > root_at_fatcity.com Subject: Complex
> > Integrity
> > Checking
> >
> >
> >
> >
> > 04-06-2002 15:58
> >
> > Please respond to
> >
> > ORACLE-L
> >
> >
> >
> >
> >
> >
> >
> >
> > Hi guys. Here's my problem.
> > I want to check the new values (when inserting&updating a table)
> > against
> > the
> > ones in the existing rows. Something like checking for duplicate
> > values,
> > but
> > using a unique constraint doesn't suit my needs.
> > I think of a before insert&update trigger, wherein checking my
> > condition
> > and
> > raise a error if not valid. The problem is, in case of an update
> > statement,
> > I get the mutating "ORA-04091 table <my table> is mutating....".
> > I read a lot of doc but I didn't find any helping ideas.
> Can you give
> > me
> > some, or maybe a new approach to this kind of problem?
> > Thanks in advance!
> >
> > 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).
> >
> >
> >
> >
> > ==================================================================
> > De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
> > uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
> > vermenigvuldiging, verspreiding en/of verstrekking van deze
> > informatie aan
> > derden is, behoudens voorafgaande schriftelijke toestemming
> van Ernst
> > &
> > Young, niet toegestaan. Ernst & Young staat niet in voor de
> juiste en
> > volledige overbrenging van de inhoud van een verzonden
> e-mailbericht,
> > noch
> > voor tijdige ontvangst daarvan. Ernst & Young kan niet
> garanderen dat
> > een
> > verzonden e-mailbericht vrij is van virussen, noch dat
> > e-mailberichten
> > worden overgebracht zonder inbreuk of tussenkomst van onbevoegde
> > derden.
> >
> > Indien bovenstaand e-mailbericht niet aan u is gericht,
> verzoeken wij
> > u
> > vriendelijk doch dringend het e-mailbericht te retourneren aan de
> > verzender
> > en het origineel en eventuele kopieën te verwijderen en te
> > vernietigen.
> >
> > Ernst & Young hanteert bij de uitoefening van haar werkzaamheden
> > algemene
> > voorwaarden, waarin een beperking van aansprakelijkheid is
> opgenomen.
> > De
> > algemene voorwaarden worden u op verzoek kosteloos toegezonden.
> >
> =====================================================================
> > The information contained in this communication is confidential and
> > is
> > intended solely for the use of the individual or entity to
> whom it is
> > addressed. You should not copy, disclose or distribute this
> > communication
> > without the authority of Ernst & Young. Ernst & Young is neither
> > liable for
> > the proper and complete transmission of the information contained in
> > this
> > communication nor for any delay in its receipt. Ernst & Young does
> > not
> > guarantee that the integrity of this communication has been
> > maintained nor
> > that the communication is free of viruses, interceptions or
> > interference.
> >
> > If you are not the intended recipient of this communication please
> > return
> > the communication to the sender and delete and destroy all copies.
> >
> > In carrying out its engagements, Ernst & Young applies general terms
> > and
> > conditions, which contain a clause that limits its liability. A copy
> > of
> > these terms and conditions is available on request free of charge.
> > ===================================================================
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Jack van Zanen
> > INET: nlzanen1_at_EY.NL
> >
> > 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
> >
> === message truncated ===
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! - Official partner of 2002 FIFA World Cup
> http://fifaworldcup.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.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
> --
> 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: Gogala, Mladen
  INET: MGogala_at_oxhp.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).