how can i update Master detail block in oracle

Posted on 2009-02-24
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 47

    Accepted Solution

    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 34

    Expert Comment

    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

    Thanks a lot

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now