| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Update Algorithm!
rajini_1000_at_my-deja.com wrote:
>
> here's the specs!!
>
> There are three tables with the foll. relationship.
>
> Table A with USER_SSN as primary key
> Table B with CITY_ID as primary key and USER_SSN as foregin key
> Table C with HOBBY_ID as UNIQUE field and USER_SSN as foreign key.
>
> There may be zero or one or many records in TABLE C for any USER_SSN in
> Table A.
> There will be only one record in Table B for any USER_SSN in Table A.
>
> And Now, a USER_SSN needs to be changed to a new value. It's assumed
> that the NEW USER_SSN is never present in the table before.
>
> I have decided the following algorithm solves the problem in a
> PROCEDURE.
> Pls. suggest if anything could be corrected to get better performance.
>
> 1. Lock Table A ( USER_SSNs)
> ( using a cursor that selects entire record with UPDATE of USER_SSN)
> 2. Lock Table B and Table C
> ( using two cursors that select records with UPDATE of USER_SSN)
> 3. Fetch records from cursors on Table B and Table C
> ( fetch record from Table C into an appropriate PL/SQL table as
> there will be many records)
> 4. delete records from Table B and Table C
> ( using WHERE CURRENT OF)
> 5. update table A with new USER_SSN
> 6. if (5) succeeds insert into table B and Table C the records already
> fetched, after changing the USER_SSN to new USER_SSN. if (5) fails,
> rollback and return an error.
> 7. if insert succeeds in (6) and update succeeds in (5) commit.
> 8. handle with proper EXCEPTIONs for NO_DATA_FOUND on selects,
> or OTHERS in general.
>
> Thanks in advance for your suggestions!
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Sounds to me like you need the equivalent of update cascade.
In Oracle 7, you can get one from:
http://govt.us.oracle.com/~tkyte
In Oracle 8, look at deferrable constraint enforcement...
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 02 1999 - 05:49:27 CDT
 |
 |