How to refer to tables in a trigger? / OR / How to look up FK values to be added on insert automatically

My goal is to have Oracle fill in FK values that correspond to the right PK values of a parent table.

I have this trigger:

CREATE OR REPLACE TRIGGER connect_purified_enz_to_produced
    BEFORE INSERT ON purified_enz
    FOR EACH ROW 
BEGIN
    SELECT produced.produced_id
        INTO :NEW.g_id
    FROM produced 
        JOIN pur_connector ext
            ON produced.construct_id = ext.construct_id
               AND produced.g_batch = ext.g_batch
    WHERE produced.produced_id = :NEW.purified_enz_id;
END;

This is my insert statement:

INSERT 
INTO purified_enz p
    (
    p.p_batch,
    p.final_buffer_system,
    p.buffer_mol,
    p.final_nacl,
    p.final_ph,
    p.final_add,
    p.yield,
    p.concentration ) 
SELECT  
    p_batch,    
    final_buffer_system,
    buffer_mol,
    final_nacl,
    final_ph,
    final_add,
    yield,
    concentration 
FROM EXTERNAL ((
  construct_id NUMBER(10),
  n_term VARCHAR2 (50),
  enz_name VARCHAR2 (50),
  c_term VARCHAR2 (50),
  cpp VARCHAR2 (50),
  mutations VARCHAR2 (50),
  g_batch VARCHAR2 (50),
  emptycol VARCHAR2(1),
  purified_enz_id VARCHAR2 (50),
  g_id VARCHAR2 (50),
  p_batch VARCHAR2 (50),
  final_buffer_system VARCHAR2 (50),
  buffer_mol NUMBER (6, 2),
  final_nacl NUMBER (4),
  final_ph NUMBER (4, 2),
  final_add VARCHAR2 (50),
  yield NUMBER (6, 2),
  concentration NUMBER (6, 2))

    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_purified_enz_from_purified_enz.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('PURIFIED_ENZ.CSV')
    REJECT LIMIT UNLIMITED) ext
 
    WHERE NOT EXISTS (
        SELECT * FROM construct c INNER JOIN produced pr
        ON c.construct_id = pr.construct_id
        INNER JOIN purified_enz p
        ON pr.produced_id = p.g_id 
        
            WHERE c.construct_id = ext.construct_id
            AND c.n_term = ext.n_term
            AND c.enz_name = ext.enz_name
            AND c.c_term = ext.c_term
            AND c.cpp = ext.cpp
            AND c.mutations = ext.mutations
            AND pr.g_batch = ext.g_batch
        );

I get this error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01403: no data found
ORA-06512: at "C##ELLIE.CONNECT_PURIFIED_ENZ_TO_PRODUCED", line 2
ORA-04088: error during execution of trigger 'C##ELLIE.CONNECT_PURIFIED_ENZ_TO_PRODUCED'

The insert statement works if the trigger doesn't exist.

This is my attempt at using lookup.

CREATE TABLE lookup_pufiried_FK NOLOGGING PARALLEL AS SELECT
FK_for_purified_enz, ext.g_batch, ext.p_batch 

FROM pur_connector ext INNER JOIN produced pr
ON pr.construct_id = ext.construct_id
AND pr.g_batch = ext.g_batch

WHERE FK_for_purified_enz = pr.produced_id;

The documentation says that a table created in the format WHERE X = Y should convert an X value into a Y value. I don't have a valid purified_enz value in the external table. I just have 2 values that can be used to identify which PK each record corresponds to if you do some joins. So idk if this is even the right approach.

Error:

WHERE FK_for_purified_enz = pr.produced_id
Error report -
ORA-00904: "FK_FOR_PURIFIED_ENZ": invalid identifier
00904. 00000 -  "%s: invalid identifier"

SOLVED:

So, I have finally figured out that I need two staging tables.

CREATE TABLE purified_enz_stage1
    (
      construct_id NUMBER(10),
      n_term VARCHAR2 (50),
      enz_name VARCHAR2 (50),
      c_term VARCHAR2 (50),
      cpp VARCHAR2 (50),
      mutations VARCHAR2 (50),
      g_batch VARCHAR2 (50),
      emptycol VARCHAR2(50),
      purified_enz_id VARCHAR2 (50),
      g_id VARCHAR2 (50),
      p_batch VARCHAR2 (50),
      final_buffer_system VARCHAR2 (50),
      buffer_mol NUMBER (6, 2),
      final_nacl NUMBER (4),
      final_ph NUMBER (4, 2),
      final_add VARCHAR2 (50),
      yield NUMBER (6, 2),
      concentration NUMBER (6, 2))
    ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY data_to_input
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE 
        SKIP 1
        BADFILE bad_files:'badflie_insert_into_pur_connector_from_purified_enz.bad'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL 
        ) 
    LOCATION ('PURIFIED_ENZ.CSV')
    ) REJECT LIMIT UNLIMITED;

CREATE TABLE purified_enz_stage2 AS SELECT

      ext.construct_id,
      ext.n_term,
      ext.enz_name,
      ext.c_term,
      ext.cpp,
      ext.mutations,
      ext.g_batch,
      ext.emptycol,
      ext.purified_enz_id,
      ext.g_id,
      ext.p_batch,
      ext.final_buffer_system,
      ext.buffer_mol,
      ext.final_nacl,
      ext.final_ph,
      ext.final_add,
      ext.yield,
      ext.concentration,
      pr.produced_id 

FROM purified_enz_stage1 ext INNER JOIN produced pr
ON pr.construct_id = ext.construct_id
AND pr.g_batch = ext.g_batch;

And then I just import from staging table 2!

INSERT 
INTO purified_enz
    (
    g_id,
    p_batch,
    final_buffer_system,
    buffer_mol,
    final_nacl,
    final_ph,
    final_add,
    yield,
    concentration ) 
SELECT  
    produced_id,
    p_batch,    
    final_buffer_system,
    buffer_mol,
    final_nacl,
    final_ph,
    final_add,
    yield,
    concentration 
FROM purified_enz_stage2;