3 Replies Latest reply: Jun 27, 2014 5:03 PM by Roadling RSS

    Need help documenting value_ID in table_A of a record inserted into table_B

    jennis512

      Hi everyone. I just joined the community and I need some advice. I am a college student studying computer science going into my sophomore year, so my understanding of SQL is mediocre at best. I am working with apex, I have a tabular form where the user can add records. I created a button and added dynamic action, where upon clicking the button, I have apex execute pl/sql code:

       

      INSERT INTO table_B (value_ID, value_A, value_B, value_C)

      SELECT sequence.NEXTVAL, value_A, value_B, value_C

      FROM table_A

      WHERE condition LIKE '%so and so%';

       

      After the code, table_B now has value_ID. I need to take that value_ID and insert it back into table_A for reference purposes.

       

      I was using (and I know its wrong) the following code

      UPDATE table_A

      SET value_ID = (SELECT MAX(value_id)

                                  FROM table_B)

      WHERE condition LIKE ‘%so and so%’;

       

      This pulls the maximum value each and every time and re-updates all records. So if I inserted one record (value_ID 100), and then inserted three more records, instead of having 100,101,102,103, all value_ID’s are displayed as 103. I need to document inserted ID for each record so it corresponds with the ID in table_B

       

      Is there a way I can do this?

      Thanks

        • 1. Re: Need help documenting value_ID in table_A of a record inserted into table_B
          Paul Broughton

          Hi,

          I'm sorry but it is a little unclear to me exactly what you are trying to do.  My suggestion would be that you use a trigger for this, that way every time a row is inserted into table_b the trigger will fire and either insert a new row or update a row in table_a.  Try something like:

           

          create or replace trigger table_b_trg
          after insert on table_b
          for each row begin
            update table_a
            set value_id = :new.value_id
          WHERE condition LIKE ‘%so and so%’;
          

           

          You will need to ensure you have a condition though otherwise you will have the same problem and update every row.

          Thanks

          Paul

          • 2. Re: Need help documenting value_ID in table_A of a record inserted into table_B
            Mike Kutz

            Oracle Sequences do not guarantee "gap free" numbers.

            Oracle Sequences do not guarantee ORDER. -- this is especially true for RAC environments.

             

            It is VERY POSSIBLE for the second user to get a SMALLER VALUE than what the first user had received.

             

            I suggest you CREATE VIEW

             

            MK

             

            PS - "triggers" have their own set of problems.  I recommend against using them.

            • 3. Re: Need help documenting value_ID in table_A of a record inserted into table_B
              Roadling

              It would help to know a little more about your use case and/or the realtionship between tables A and B. Something seems backwards that you would create a foreign reference and then update what sounds like a parent table with a foreign key.

               

              That being said, you should only get the nextval from your sequence as often as you need a new value. if you are going to insert 1 or 5 or 10 rows and you wnat them all to have the same value_ID then get that value first

               

              select sequence.nextval from dual;
              

               

              put that into a page item or other variable and use it for as long as you need the number and then clear it when you are done. this way you wont have to lookup a value you just inserted.

               

              Regards,

              Brad