Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using Trigger to Populate Data in One Table with Data From Another

MAFinanceDev-OracleNov 19 2015 — edited Nov 19 2015

Hi All,

I have three tables (TABLE_1, TABLE_2, & TABLE_3). I have a trigger on TABLE_1 that, when fired, inserts data from one column in TABLE_3 into TABLE_2. This trigger is fired when a new record is created on TABLE_1. This results in new records being added on TABLE_2 (3 to be exact). Additionally, when a new record is created on TABLE_1, a primary key (called PROJECT_ID) is automatically generated for the new record on TABLE_1.

Now what I need to do is create a trigger that will insert the primary key (PROJECT_ID) from the new record created on TABLE_1 to each of the new records created on TABLE_2 in the corresponding PROJECT_ID column when the first trigger is fired. The new records on TABLE_2 should all have the same PROJECT_ID matching the one created in TABLE_1.

Any help with the SQL code on this would be very helpful and also advice on what table the 2nd trigger should be added to.

Here is the code for the first trigger.  What can I add to make that PROJECT_ID column on TABLE_2 populate with the PROJECT_ID from TABLE_1?

create or replace trigger "TABLE_1_TRIGGER"

BEFORE

insert on "TABLE_1"

begin

insert into TABLE_2

(MILESTONE)

select MILESTONE

from TABLE_3;

end;

Comments

Regarding writing to Oracle, start by reading the https://oracle.github.io/node-oracledb/doc/api.html
Consuming "a web service" can be done with the http module which is part of Node.js. Check out the manual and doc like https://nodejs.org/en/knowledge/HTTP/clients/how-to-create-a-HTTP-request/

Billy Verreynne

Why nodejs and not use only PL/SQL? Far fewer moving parts.

Arif2018

i am not sure on how pl/sql works , Appreciate if you can post some links. All i want is to read the url and insert data into Oracle table.

Billy Verreynne

Oracle has the UTL_HTTP package API and the HttpUriType advance datatype (oo class).
Sample code provided in manual and you can also look at https://community.oracle.com/tech/developers/discussion/comment/10450611/#Comment_10450611 and as for the oo class:

SQL> select httpuritype( 'http://google.com' ).getClob() as "HTML" from dual;

HTML
-------------------------------------------------------------------------------
<!doctype html><html itemscope="" itemtype="http://schema.org/WebPage" lang="en
-ZA"><head><meta content="text/html; charset=UTF-8" http-equiv="Content-Type">
..snipped..

In PL/SQL use:

clobVariable := new HttpUriType(urlVariable).getClob();
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 17 2015
Added on Nov 19 2015
#apex-discussions, #insert, #trigger
1 comment
987 views