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? > > --- [EMAIL PROTECTED] 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@oran > > > > ge.ro To: Multiple > > recipients > > of list ORACLE-L <[EMAIL PROTECTED]> > > Sent by: cc: (bcc: Jack > > van > > Zanen/nlzanen1/External/MEY/NL) > > [EMAIL PROTECTED] 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: [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: Mercadante, Thomas F 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).