asked on

how can i update Master detail block in oracle

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

ASKER CERTIFIED SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Geerlings

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

ajayit

ASKER

Thanks a lot