RE: Complex Integrity Checking

wouldn't an "INSTEAD OF" trigger solve the problem here? that way, he could program an insert/update any way he wants to...

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, June 04, 2002 4:26 PM
To: Multiple recipients of list ORACLE-L

I think that something such as

  insert into my_table
  select new_start_time, new_end_time (both constants)   from dual (well known view on x$dual)
  where not exists (select null

                    from my_table
                    where new_start_time between start_time and end_time
                    union
                    select null
                    from my_table
                    where new_end_time between start_time and end_time)

 should do the trick, making of course wildly optimistic assumptions about indices etc.
 And you can always check SQL%WHATITSNAME for the number of row processed if you need to keep track of rejects.

Rachel Carmichael wrote:
>
> 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
> >

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

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).