How to include a SUBSELECT in VALUES of INSERT to take values from different row?

Question

I want to make a trigger that will insert a value from a connected row. For example I have a table with 3 rows as below:

I create a trigger that will work once row 3 and 4 are deleted (in this case will be deleted at the same time). And I want to record invnr and extinvnr from row 1 based on idparent=id. I cannot seem to make it work though.

CREATE OR REPLACE TRIGGER LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
BEGIN
IF :old.invnr IS NULL THEN
INSERT INTO TABLE_LOG_DELETEDPAYMENTS (table_name, invnr, extinvnr, invdate, transactionid, info, createdby, deleted_by, date_of_delete)
values ('payments', :old.invnr, :old.extinvnr, :old.invdate, :old:transactionid, :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
END IF;
END;

How can I incorporate this into the trigger above?

Answer 1

Try it this way:

create or replace TRIGGER LOG_DELETEDPAYMENTS
BEFORE DELETE ON payments
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    Declare
        my_invnr    PAYMENTS.INVNR%TYPE;
        my_extinvnr PAYMENTS.EXTINVNR%TYPE;
    Begin
        IF :old.INVNR IS NULL THEN
            Select    INVNR, EXTINVNR
            Into      my_invnr, my_extinvnr
            From      PAYMENTS
            Where     ID = :old.IDPARENT;
            --
            INSERT INTO TABLE_LOG_DELETEDPAYMENTS (table_name, invnr, extinvnr, invdate, transactionid, info, createdby, deleted_by, date_of_delete)
            values ('payments', my_invnr, my_extinvnr, :old.invdate, :old:transactionid, :old.info, :old.createdby, sys_context('userenv','OS_USER'), SYSDATE);
        END IF;
    End;
END;

• You should select the values of INVNR and EXTINVNR based on ID - IDPARENT relationship and store it in the variables (my_invnr and my_extinvnr).
• Those variables are used in INSERT into the log statement.
• Because of the Select ... Into statement that is reading the affected table - trigger would fail with table PAYMENTS is mutating error.
• To avoid that (to separate transaction from the table) you should Declare the PRAGMA AUTONOMOUS_TRANSACTION.
• There will be two rows inserted into LOG as the trigger runs FOR EACH (deleted) ROW.
  Regards...