Update foreign key in child table with parent ID in trigger
CometAug 24 2009 — edited Apr 3 2018I 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?