Re: Update Algorithm!

A copy of this was sent to rajini_1000_at_my-deja.com (if that email address didn't require changing) On Thu, 02 Sep 1999 14:57:28 GMT, you 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.
>

update cascade.

you can see some stuff i have at my web site (address in my signature below) that generates triggers to do update cascade. works for 7.1 on up.

if you are using 8.0 or above, you can define the foreign keys to be deferrable constraints. Then, to do an update cascade type of thing would be:

tkyte_at_8.0> create table p ( x int primary key ); Table created.

tkyte_at_8.0> create table c1 ( x int references p deferrable ); Table created.

tkyte_at_8.0> create table c2 ( x int references p deferrable ); Table created.

tkyte_at_8.0> insert into p values ( 1 );
tkyte_at_8.0> insert into c1 values ( 1 );
tkyte_at_8.0> insert into c2 values ( 1 );

tkyte_at_8.0> set constraints all deferred; Constraint set.

tkyte_at_8.0> update p set x = 2 where x = 1; 1 row updated.

tkyte_at_8.0> update c1 set x = 2 where x = 1; 1 row updated.

tkyte_at_8.0> update c2 set x = 2 where x = 1; 1 row updated.

tkyte_at_8.0> set constraints all immediate; Constraint set.

the set constraints all deferred makes it so that constraints are checked either

• when you commit
• when you make them immediate again

so, you can update cascade easily by deferring constraints -- doing the needed updates -- un-deferrering the contraints to verify all is well and then continue about your business.

>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.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 13:07:36 CDT