| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Complex Integrity Checking
Iulian,
The solution for ORA-4091 is to create two triggers--a row level and a
statement level.
In the row level you simply record the values of :new.start_time and
:new.end_time into
a PL/SQL table. Then in the statement level trigger you perform the check
based on the
recorded values in the PL/SQL table. Record the values using a PL/SQL table
in a package,
that way you won't have to worry about simultaneous updates by different
sessions. Something
like this...
Interval
s date e date
create or replace package INTERVAL_PACKAGE as
type t_start is table of interval.s%TYPE index by binary_integer; type t_end is table of interval.e%TYPE index by binary_integer;
v_s t_start; v_e t_end; v_count binary_integer :=0;
end INTERVAL_PACKAGE;
create or replace trigger RINTERVAL
before insert or update on interval
for each row
begin
/* recored new time intervals in interval_package */
interval_package.v_count := interval_package.v_count + 1; interval_package.v_s(interval_package.v_count) := :new.s; interval_package.v_e(interval_package.v_count) := :new.e;end RINTERVAL;
create or replace trigger SInterval
after insert or update on interval
declare
v_start interval.s%TYPE; --from pl/sql table v_end interval.e%TYPE; v_start_curr interval.s%TYPE; --from interval table v_end_curr interval.e%TYPE;
no overlap */
for v_i in 1..interval_package.v_count loop
v_start := interval_package.v_s( v_i ); v_end := interval_package.v_e( v_i );
/* TEST */
...some test goes here...
if ... then
--raise_application_error(-20000,'date overlap'||v_start||v_end); end if;
end loop;
interval_package.v_count := 0; interval_package.v_s.DELETE; interval_package.v_e.DELETE;
end SInterval;
/
-----Original Message-----
Sent: Tuesday, June 04, 2002 12:38 PM
To: Multiple recipients of list ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Huntley INET: rhuntley_at_mindleaders.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).Received on Tue Jun 04 2002 - 13:38:13 CDT
 |
 |