SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Deleting parent and child rows

2823391Aug 25 2011 — edited Aug 26 2011

Hi all, hope you can help. I was wondering what the most effective method is of performing this:

There are 3 tables involved.

TBL_APPLICATION ( appl_no ( PK ) )
TBL_ROOM ( appl_no ( FK ) )
TBL_ROOM_MATE ( appl_no ( FK ) )

TBL_APPLICATION is the main table and has a 1 to many relationship with both TBL_ROOM & TBL_ROOM_MATE.

I have 80 or so records i need to delete from TBL_APPLICATION and atomically delete their child references in TBL_ROOM & TBL_ROOM_MATE aswell.

I have considered a Procedure which cycles through each app_no and deletes child references first then parant from TBL_APPLICATION.

Some advise and examples would be much appreciated, as its important I dont mess this up and i want to keep each transaction atomic incase something fails. :D

Many thanks

This post has been answered by Tubby on Aug 26 2011

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 23 2011

Added on Aug 25 2011

5 comments

610 views