Sign Up

Hey, Dev!
Are you looking for a forum full of active developers to help you?
So if you want to:
➡️ Get answers for your development issues
➡️ Help others
➡️ Write an article
➡️ Get rewarded for your active participation
Then this place is just for you and it is 100% FREE.

Have an account? Sign In


Have an account? Sign In Now

Sign In

Forgot Password?

Don't have account, Sign Up Here

Forgot Password

Lost your password? Please enter your email address. You will receive a link and will create a new password via email.

Have an account? Sign In Now

Sorry, you do not have a permission to ask a question, You must login to ask question.

Forgot Password?

Need An Account, Sign Up Here
Sign InSign Up

OrclQA.Com

OrclQA.Com Logo OrclQA.Com Logo
Search
Ask A Question

Mobile menu

Close
Ask a Question
  • Home
  • Blog
  • New Questions
  • Tutorials
    • Oracle
    • Oracle Apex
    • Python
  • Tags
  • Users
  • Badges & Points
  • About
Home/Questions/Q 6407
In Process
Ana
Ana

Ana

  • 12 Questions
  • 17 Answers
  • 0 Best Answers
  • 91 Points
View Profile
  • 0
AnaProfessional
Asked: April 6, 20212021-04-06T17:27:31+05:30 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
  • 66
  • 0
  • 0
  • Share
    • Share on Facebook
    • Share on Twitter
Answer

    3 Answers

    1. Vinish

      Vinish

      • 1 Question
      • 547 Answers
      • 53 Best Answers
      • 3k Points
      View Profile
      Vinish Enlightened
      2021-04-07T08:08:48+05:30Added an answer on April 7, 2021 at 8:08 am
      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
      • Share
        Share
        • Share on Facebook
        • Share on Twitter
      • Ana

        Ana

        • 12 Questions
        • 17 Answers
        • 0 Best Answers
        • 91 Points
        View Profile
        Ana Professional
        2021-04-07T19:48:18+05:30Replied to answer on April 7, 2021 at 7:48 pm
        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
        • Share
          Share
          • Share on Facebook
          • Share on Twitter
        • Ana

          Ana

          • 12 Questions
          • 17 Answers
          • 0 Best Answers
          • 91 Points
          View Profile
          Ana Professional
          2021-04-08T13:49:57+05:30Replied to answer on April 8, 2021 at 1:49 pm

          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
          • Share
            Share
            • Share on Facebook
            • Share on Twitter

    You must login to add an answer.

    Forgot Password?

    Sidebar

    Ask Question
    Write a Post

    Recent Blog Posts

    • Vinish

      Oracle Apex - Display External File in Region

    • Vinish

      Oracle PL/SQL Download BLOB File to Disk

    • Vinish

      Oracle Convert External File to BLOB

    • Vinish

      Oracle Apex Calendar Example

    • Vinish

      Oracle Apex - Open Modal Dialog Page Using JavaScript

    Explore

    • Home
    • Blog
    • New Questions
    • Tutorials
      • Oracle
      • Oracle Apex
      • Python
    • Tags
    • Users
    • Badges & Points
    • About

    © 2021 OrclQA.Com. All Rights Reserved. Privacy Policy