Skip to Main Content

SQL & PL/SQL

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!

Update foreign key in child table with parent ID in trigger

CometAug 24 2009 — edited Apr 3 2018
I defined a parent table lsu_trans_requests_t and its child table lsu_trans_resources_t each with a primary key. The table lsu_trans_resources_t has a foreign key trans_request_id which naturally holds the primary key value of the parent record. I created a trigger on both tables to set the primary key. The trigger for the table lsu_trans_resources_t is as follows:

create or replace trigger lsu_trans_resources_trigger
before insert on lsu_trans_resources_t
for each row
begin
select lsu_trans_resources_seq.nextval into :new.trans_resources_id from dual;
end;

It makes sense for the trigger logic to assign the foreign key column trans_request_id in the table lsu_trans_resources_t with the value of the primary key from the parent table. Can the assignment be invoked in lsu_trans_resources_trigger or do I need to define a new trigger on a different event? What is the basic approach to modifying this trigger to make the assignment and how do I pass to the trigger or retrieve - if from a global variable - the primary key of the parent table?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 21 2009
Added on Aug 24 2009
2 comments
1,158 views