Oracle - trigger after update

Question

I have trigger:

CREATE OR REPLACE TRIGGER TR.TR_OUTPUT_EHT_UPDATE_OPE
  AFTER DELETE
  ON TR.TR_JN_OUTPUT_EHT
  FOR EACH ROW
DECLARE
   BEGIN
  UPDATE TR.TR_BP tbp
    SET tbp.DONE= 2, tbp.DATA_WYK = SYSDATE
    WHERE EXISTS (SELECT ARCH.NR
    FROM TR.TR_KB_OUTPUT_ARCH ARCH
        WHERE ARCH.NR = tbp.NRKK)
    AND NOT EXISTS (SELECT OUTPUT.NR_SERYJNY
        FROM TR.TR_JN_OUTPUT OUTPUT
        WHERE OUTPUT.NR = tbp.NRKK);
END;
/

After that I want to get the "NRKK" of just updated row and update another table which is connected by this "NRKK":

UPDATE P.TR_OPE OPE
SET OPE.DATA = SYSDATE, 
OPE.STATUS = OPE.STATUS + 1 
WHERE OPE.NRKK = "NRKK"

I tried to do:

SELECT tbp.NRKK INTO v_nrkk FROM TR.TR_BP tbp WHERE tbp.DATA = (SELECT 
MAX(tbp.DATA) FROM TR.TR_BP tbp1);

but I have errors: ORA 04091, ORA 06512 and ORA 04088.

EDITED:

I created another trigger:

CREATE OR REPLACE TRIGGER TR.TR_OP_E
  AFTER UPDATE ON TR.TR_BP
FOR EACH ROW
DECLARE
    v_nrkk TR.TR_BP.NRKK % TYPE;
BEGIN
  SELECT tbp.NRKK INTO v_nrkk FROM TR.TR_BPtbp WHERE tbp.DATA = (SELECT 
MAX(tbp.DATA) FROM TR.TR_BP tbp1);

  IF v_nrkk is NOT NULL THEN
  UPDATE P.TR_OPE OPE
    SET OPE.DATA = SYSDATE, OPE.STATUS = 1
    WHERE OPE.ID_BR = '00100300'
    AND OPE.NRKK = v_nrkk;
  END IF;
  END;
/

But I have still errors...

Possible duplicate of How to fix the mutating trigger in oracle — XING
Please look again at the post @XING placed in the comment. That edit you made doesn't fix the mutating trigger issue. If you want to query the same table which you are capturing the trigger, you need compound trigger. — Renato Afonso

XING XING · Accepted Answer · 2017-11-17T14:26:34

As mentioned by @Renato Afonso, you can avoid mutating table error via using a COMPOUND TRIGGER. See below demo;

--Tables

CREATE TABLE TABLE_1 (data NUMBER);
/
CREATE TABLE TABLE_2 (DATA1 NUMBER);
/

--Record in table_1

SQL> select * from TABLE_1;

      DATA
----------
         1

--Record in table_2

SQL> select * from TABLE_2;

     DATA1
----------
         5

Trigger:

CREATE OR REPLACE TRIGGER TR_OP_E FOR
   INSERT OR UPDATE OR DELETE
   ON TABLE_1
   COMPOUND TRIGGER
   v_nrkk   TABLE_1.DATA%TYPE;
   AFTER STATEMENT
   IS
   BEGIN
      --Selecting from the same table on which trigger is created
      SELECT tbp.data
        INTO v_nrkk
        FROM Table_1 tbp
       WHERE tbp.DATA = (SELECT MAX (tbp.DATA)
                           FROM Table_1 tbp1);

      IF v_nrkk IS NOT NULL
      THEN
         UPDATE TABLE_2 OPE
            SET OPE.DATA1 = v_nrkk;
      END IF;
   END
   AFTER STATEMENT;

END;
/

Execution:

SQL> UPDATE TABLE_1 SET DATA = 2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> select * from TABLE_1;

      DATA
----------
         2

SQL>  select * from TABLE_2;

     DATA1
----------
         2

Oracle - trigger after update

1 Answers