Update Algorithm!

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. Received on Thu Sep 02 1999 - 09:57:28 CDT