Hello 🙂
I have created forms in which the user can enter data. With collections the information is saved and will be inserted in the corresponding tables after the forms are submitted.
Now one column in the table has remained empty and I am not sure how to solve it in APEX.
Namely, the table has a foreign key to another table.
But the ID of this table is generated only after submitting the forms.
Can I solve it, for example, with a trigger that then enters the foreign key into the table after the forms are submitted?
Would it be an after insert trigger like this:
CREATE OR REPLACE TRIGGER INSERT_FK
AFTER INSERT
ON TBL1
FOR EACH ROW
begin
INSERT INTO TBL2
VALUES (:NEW.ID);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20299)));
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (TO_CHAR (SQLERRM (-20298)));
end;
or is there another better solution for this?
Vinish Kapoor
You want to get the PK value to store in the detail tables as the FK value right?
Click on the Process tab and then select the first main form process. You will find a setting Return PK after insert which is by default on and it should be.
Then click on your other child table processes and change their source type to PL/SQL code and there you can handle the all insert as below:
Above P1_PKEMPNO is the master form primary key field which is getting processed after insert and the Apex will see it after processing.
Ana
I actually do not have a detail table but three forms to enter data.
and yes I would like to retrieve the PK from one table and insert it as FK in another table, together with the information stored in the collection.
With this process for example, I can insert the FK:
And with this process however I can insert the stored information from the collection into the corresponding tables.
However, I would like to develop a process in which both the foreign key and the stored information from the collections are entered into the tables at the same time.
The problem is, there are two tables. So when the form gets submitted, the stored data from collection will be inserted into tbl1. Then the ID will be generated. At the same when the form gets submitted, the stored data from other collection be inserted into tbl2 (this works) but as well I want to insert the Foreign Key from tbl1 (which is the generated ID from tbl1). And for this I have not found a solution yet.
Ana
Ok, now i have it.
It was actually just a combination between these two codes.
So first I had to declare the ID variable, use returning into statement and insert the variable into the other table.