[Last Call] Learn how to a build a cloud-first strategyRegister Now


how can i update Master detail block in oracle

Posted on 2009-02-24
Medium Priority
Last Modified: 2013-12-19
I have a master detail block in oracle and i created cascading relationship in forms n now i want to update the primary key of master block but it display a error that child record found so now i want that if update the master block then detail block automatically updated

please help me
Question by:ajayit
LVL 48

Accepted Solution

schwertner earned 500 total points
ID: 23721955
You have to use PRE_UPDATE (BEFORE UPDATE in the DB) trigger and do the following

1. To read and to store in a associative array the rowid's of the child table
2. set NULL as foreign key there
3. change the primary key and commit the UPDATE
4. go through the associative array and to change the foreign key to the
new value of primary key

Changing the Primary Key is a bad practice and should be avoided.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 23723923
I like schwertner's last line the best: "Changing the Primary Key is a bad practice and should be avoided."

If you really want to support changing the primary key, this gets complicated if you have a master-detail Oracle Form, and especially complicated if you have records displayed in the detail block.  Neither the foreign keys in the database nor Oracle Forms support an "update cascade" functionality for changing a primary key value in a master table.

One way around this would be to add another column to the master and detail tables, and use this as an "artificial key" for the master-detail relationship instaed of the currecnt "logical key" that you probably have now. That would allow you to change the "logical key" value in the master block and use a post-update trigger on the master block to replicate that to the child block records in the database.

Another option: train the users to use the "duplicate record' feature in the master block to create a copy of the master record, change the primary key to the new value, then write a post-insert trigger to update the detail records to the new primary key, and delete (or set to obsolete) the old master record.  This will be easiest if you add a non-base table field in the master block to hold the previous primary key value

Author Closing Comment

ID: 31550543
Thanks a lot

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 5 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question