Home/ Questions/Q 6407
In Process
Ana
Ana

Ana

View Profile
  • 0
Professional
Asked: 2021-04-06T17:27:31+05:30

Inserting Foreign Key into table with trigger

  • 0

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?

 

foreign-keyoracle apextrigger

    3 Answers

    1. Vinish

      Vinish

      View Profile
      Enlightened
      This answer was edited.

      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:

      begin
case :APEX$ROW_STATUS
when 'C' then
insert into emp_detail ( empno, ename, deptno )
values ( :P1_PKEMPNO, :ENAME, :DEPTNO );
when 'U' then
update emp_detail
set ename = :ENAME,
deptno = :DEPTNO
where empno = :p1_PKEMPNO;
when 'D' then
delete emp_detail
where empno = :p1_PKEMPNO;
end case;
end;

      Above P1_PKEMPNO is the master form primary key field which is getting processed after insert and the Apex will see it after processing.

      • 0
      • Ana

        Ana

        View Profile
        Professional
        This answer was edited.

        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:

        DECLARE
  l_id table1.id%TYPE;
BEGIN
  INSERT INTO table1(ID) VALUES (ID_SEQ.NEXTVAL)
    RETURNING ID INTO l_id;
  INSERT INTO table2(FKID) VALUES (l_id);
END;

        And with this process however I can insert the stored information from the collection into the corresponding tables.

        begin
for i IN (SELECT c001 AS a,c002 AS b, c003 AS c, c004 AS d
          from APEX_COLLECTIONS
  WHERE COLLECTION_NAME='ITEM_DATA'
  ORDER BY 1)
 loop
    insert into table1 (USERID,TEST1,TEST2, TEST3)
    values ( i.a,i.b,i.c,i.d);
  end loop;
apex_collection.delete_collection(p_collection_name=>'ITEM_DATA');
end;

        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.

        • 0
        • Ana

          Ana

          View Profile
          Professional

          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.

          DECLARE
  l_id table1.id%TYPE;

          begin
for i IN (SELECT c001 AS a,c002 AS b, c003 AS c, c004 AS d
          from APEX_COLLECTIONS
  WHERE COLLECTION_NAME='ITEM_DATA')


           loop
    insert into table1 (PK,USERID,TEST1,TEST2, TEST3)
    values ( ID_NEXTVAL,i.a,i.b,i.c,i.d)
           RETURNING ID INTO l_id;
 end loop;
commit;
--loop through other collections
for i2 IN (SELECT c001 AS a,c002 AS b, c003 AS c, c004 AS d
          from APEX_COLLECTIONS
  WHERE COLLECTION_NAME='OTHER_DATA')

           loop
    insert into table2 (FK,USERID,TEST1,TEST2, TEST3)
    values ( l_id,i2.a,i2.b,i2.c,i2.d)
 end loop;
commit;
end;

I am not sure if this is an ideal solution but for now this will do. 
Thank you very much for your help!
          • 1

    You must login to add an answer.