Re: Deferrable constraints in O7

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 3 Jul 2001 10:02:37 -0700
Message-ID: <9hstrd02gqs@drn.newsguy.com>

In article <xil07.4422$A51.1022121_at_monolith.news.easynet.net>, "Benetnasch" says...
>
>Bother.
>
>Thanks for the info anyway!
>
>Hmmm... in that case, is there any other way around my predicament, which is
>thus:
>
>I have 2 tables, a header table (one) & a details one (to many). The header
>id is a foreign key
>in the details table.
>
>In the header record is a count & total of a number of transactions.
>
>At the moment, to insert both header & detail records, I have to execute 2
>cursors:
>
>- the 1st to get the header information (count & total, etc), and
>- the 2nd to get the detaill information.
>
>This is daft because I'm selecting twice from the same rows. I thought about
>merging the
>2 together, and calculate the count & totals as I loop through. However, I
>can't insert
>rows into the detail table BEFORE the header record because of the
>constraint, but I
>need to because I won't know the totals until the last detail record has
>been processed.
>

well, the detail rows will be in the buffer cache (you just read them after all) so their "re-retrieval" will be fast. On the other hand you can:

   loop over parent records

        insert PARENT (a null count and total ) returning rowid in l_rowid;
        loop over child rec
              insert child
              inc total and count
        end loop
        update parent set count = , total = 
          where rowid = l_rowid;

   end loop;

>Thanks!
>--
>Benetnasch
>Remove ".spam.begone"
>
>Stephen Bell <stephen.bell_at_cgi.ca> wrote in message
>news:3B41DD47.23236625_at_cgi.ca...
>> Hi,
>>
>> Unfortunately you can't in 7...
>> It was available only in 8, perhaps even 8i
>>
>> Steve
>>
>> Benetnasch wrote:
>>
>> > Can I defer a table constraint in Oracle 7? I've tried:
>> >
>> > ALTER SESSION SET CONSTRAINT <constraint name> DEFERRED
>> >
>> > ... but it doesn't like that...
>> >
>> > Any help'd be appreciated.
>> > --
>> > Benetnasch
>> > Remove ".spam.begone"
>>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jul 03 2001 - 12:02:37 CDT