How to take value from another row in same table that is being INSERT-ed into?

Question

I have table payments. In it I have 1 row for invoice and 1 row for payment for that invoice. The connection happens on idparent=id.

CREATE OR REPLACE TRIGGER UDX_TR_LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
IF :old.invnrIS NULL THEN
INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
values (:old.id, 'payments', null, :old.idparent, :old.extnr, :old.date, :old.transactionid, :old.info, :old.partner, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
END;

I need to replace the null with a statement that will select invnr where id=idparent and put that value.

I've tried a lot of options, but get errors each time:

1. Tried SELECT instead of VALUES;
2. Tried a separate TRIGGER AFTER INSERT in the new table;
3. Tried UPDATE in the same trigger after the INSERT.

Each time getting errors like ora-04091 and others. How can I achieve that?

EDIT: To test:

CREATE TABLE UDX_TABLE_LOG_DELETEDPAYMENTS 
(
id number generated by default as identity,
idaopkopf number(10),
TABLE_NAME VARCHAR2(20),
invnr VARCHAR2(20),
IDPARENT VARCHAR2(20),
extnr VARCHAR2(20),
date DATE,
TRANSACTIONID NUMBER(15),
INFO VARCHAR2(200),
partner number(15),
CREATEDBY VARCHAR2(20),
DELETED_BY VARCHAR2(20),
DATE_OF_DELETE DATE
);

You can insert the following rows in UDX_TABLE_LOG_DELETEDPAYMENTS:

INSERT INTO (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
VALUES (34042887, 'aopkopf', null, 29335828, null, 22-06-01, 34042886, null, 3433534, 9083446, 'pesho', SYSDATE);
INSERT INTO (idaopkopf, table_name, invnr, idparent, extnr, date, transactionid, info, partner, createdby, deleted_by, date_of_delete)
VALUES (34042000, 'aopkopf', null, 29335828, null, 22-01-01, 34042886, null, 3433534, 9083446, 'sasho', SYSDATE);

Table payments will have lets say same columns but information like this: CREATE TABLE payments AS( id number(15), idparent number(15), invnr number(20), date date);

INSERT INTO (id, invnr, idparent, date)
VALUES(29335828, 1111112234, 22-01-20);
INSERT INTO (id, invnr, idparent, date)
VALUES(29335555, 1555112234, 22-12-14);

Answer 1

Based on this blog by Steven Feuerstein, you can create a compound trigger. I simplified the table structure (add columns as needed) and fixed the sample script.

create table payments 
  ( id number(15)
   ,idparent number(15)
   ,invnr number(20)
   ,thedate date
);

INSERT INTO payments (id, invnr, idparent, thedate)
VALUES(1,150, NULL, DATE'2022-01-20');
INSERT INTO  payments(id, invnr, idparent, thedate)
VALUES(2,NULL, 1, DATE'2022-12-14');


CREATE TABLE udx_table_log_deletedpayments 
(
id number generated by default as identity,
idaopkopf number(10),
TABLE_NAME VARCHAR2(20),
invnr VARCHAR2(20),
IDPARENT VARCHAR2(20)
);


create or replace TRIGGER udx_tr_log_deletedpayments    
FOR DELETE ON payments    
COMPOUND TRIGGER     
   TYPE id_payment_rt IS RECORD (    
      id           payments.id%TYPE    
    , invnr        payments.invnr%TYPE  
    , idparent     payments.idparent%TYPE 
   );    

   TYPE row_level_info_t IS TABLE OF id_payment_rt  INDEX BY PLS_INTEGER;    


   g_row_level_info   row_level_info_t; 
   l_invnr payments.invnr%TYPE;

   AFTER EACH ROW IS    
   BEGIN
      g_row_level_info (g_row_level_info.COUNT + 1).id :=    
           :OLD.id;    
      g_row_level_info (g_row_level_info.COUNT).invnr :=    
           :OLD.invnr;    
      g_row_level_info (g_row_level_info.COUNT).idparent :=    
           :OLD.idparent;    
   END AFTER EACH ROW;    

   AFTER STATEMENT IS    
    BEGIN      
      FOR indx IN 1 .. g_row_level_info.COUNT    
      LOOP  
         IF g_row_level_info (indx).invnr IS NULL THEN
           SELECT invnr INTO l_invnr FROM payments WHERE id = g_row_level_info (indx).idparent;
           INSERT INTO UDX_TABLE_LOG_DELETEDPAYMENTS (idaopkopf, table_name, invnr, idparent)
             VALUES (g_row_level_info (indx).id,'payments',l_invnr,g_row_level_info(indx).idparent);
         END IF;    
      END LOOP;    
   END AFTER STATEMENT;    
END udx_tr_log_deletedpayments; 
/

delete from payments where id = 2;

1 row deleted.

select * from udx_table_log_deletedpayments;

ID  IDAOPKOPF TABLE_NAME           INVNR                IDPARENT            
-- ---------- -------------------- -------------------- --------
 1          2 payments             150                  1