1

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;
2
  • produced is not visible in SET g_id = produced.produced_id. There are other problems with your query as well. I suggest you add create table statements as well as insert statements for som sample data that illustrates the problem. Aug 13, 2020 at 11:34
  • @Lennart this is actually a [duplicate] (dba.stackexchange.com/questions/273609/…). My original question has more info, but no one responded, so I thought I'd isolate the part I care about the most. Is any of that information helpful? I can add create table statements, but I don't know what the problem is. I guess my question is more "how do i make a trigger?". I can't find any examples where people use a join to do what I'm doing inside a trigger. Aug 13, 2020 at 11:56

1 Answer 1

2

It was too complicated writing in a comment, so I'll do a sketch as an answer. I don't know Oracle, but if it resembles other DBMS something like:

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 <what?> = :NEW.purified_enz_id;
END;

I did not fully understand how produced, pur_connector, and purified_enz relate, but hopefully, it will give you some idea.

9
  • ! 1) Your code helped me write a trigger that actually compiled! Thank you! 2) The join you wrote perfectly captures the relationship between those three tables. 3) But I get an error when I use the trigger... I'll update my question with it. Also, why did you use before insert and not after? (It doesn't work with before insert either.) Aug 13, 2020 at 13:08
  • Since you are just modifying the row that is about to be inserted, there is no need for an after trigger. Aug 13, 2020 at 13:24
  • would it make a difference if my insert statement has a WHERE NOT EXISTS clause that performs a join. I use this to make sure I don't insert duplicates. Would that clause get executed before the trigger? Also, it appears that the error i'm getting happens when "a row-level trigger attempts to read or write the table from which the trigger was fired". So i'm gonna learn how one is supposed to update a table without having a trigger on that table. (stevenfeuersteinonplsql.blogspot.com/2016/12/…) Aug 13, 2020 at 13:32
  • 1
    @ellie-lumen - I recommend that you don't put the "lookup FK value" function inside a trigger. Put that functionality inside your INSERT .. SELECT statement. Aug 13, 2020 at 15:54
  • 1
    This would be better in Chat. chat.stackexchange.com/rooms/111769/discussion-with-ellie-lumen (I think you need 20 pts to participate; if its 200 pts, I'll drop the room) Aug 13, 2020 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.