Forum Stats

  • 3,837,474 Users
  • 2,262,262 Discussions
  • 7,900,294 Comments

Discussions

Populating column in one table with a PK from another with a Trigger

MAFinanceDev-Oracle
MAFinanceDev-Oracle Member Posts: 13
edited Nov 18, 2015 6:31PM in APEX Discussions

Hi All,

I’m trying to populate a column on one table with the primary key from a column  on another table that is automatically created when a new row is added.

TABLE_1 automatically generates a primary key called PROJECT_ID when a new row is created. I’ve built a trigger to insert data from TABLE_3 into TABLE_2 when a new row is created on TABLE_1. TABLE_2 also has a column called PROJECT_ID and when the data from TABLE_3 populates TABLE_2, I want the PROJECT_ID on TABLE_2 to match the PROJECT_ID from the newly created row on TABLE_1.

I’m trying to do this with 2 insert into statements but only the first one seems to work. Any advice on how to do this? Thanks!

Here is my code:

create or replace trigger "Trigger_T1"

BEFORE

insert on "TABLE_1"

for each row

begin

insert into TABLE_2

(MILESTONE_DESCRIPTION)

select MILESTONE

from TABLE_3;

insert into TABLE_2

(PROJECT_ID)

select PROJECt_ID

from TABLE_1;

end;

Tagged:

Answers

  • Ender Polanco
    Ender Polanco Member Posts: 120 Bronze Badge
    edited Nov 18, 2015 6:31PM

    Hi.

    It looks like the second insert does not work because you are using a select over the same table that is triggering the code, and as the trigger is Before insert, the value has not been inserted when the trigger is executed.

    if you are doing this in apex, maybe you could create a plsql process on "After Submit" section, after the  ApplyMRU Process, and there insert the id on table 2, using the returned value of the pk item on page.

    Hope it helps

    Best Regards.

This discussion has been closed.