Unanswered: PL/SQL script to inserting into multiple tables while maintaining integrity
I am very new to PL/SQL, so any pointers I can get would be helpful.
The trouble I am having involves inserting data into multiple database tables at once while maintaining referential integrity. I have a temporary table created in the database which stores all the data I need to insert into other tables within the database. The main table I will be inserting into is called 'Inventory' and the primary key is Inventory_ID which is filled with a trigger. I have to insert data into Inventory and then take the primary key value that was created for each row and insert that as the foreign key into many child tables while also taking more data from the temporary table which relates to the child tables. So basically I have to take data from the temp table and insert it into a parent and multiple child tables while passing primary and foreign keys. Make sense?
I have been asked to create a PL/SQL script for this process and so far I think I should use cursors from what I have been reading to accomplish it but I am not sure at all where to start or how to getting it working like I described above.
Any ideas, examples, etc would be greatly appreciated.
There are a few possible solutions to the problem. One of them might consider re-writing referential integrity constraints using the deferring constraint checking option. Read more about it here.
The other one would be changing the way INVENTORY_ID is populated. Code that is written in a database trigger should be moved to the beginning of your PL/SQL procedure OR even earlier, during insert into the TEMP table you use. This might be simpler solution than the previous one as it would require only minor code changes.
I'd suggest you to fill the TEMP.INVENTORY_ID column BEFORE processing its contents into main and child tables.