How to fix table is mutating exception while updating the table after insert using trigger in oracle?

Question

I have made a trigger to update the table "SchemeComp" row while inserting a new row. I am inserting multiple/bulk row at a time but it throws an exception and the row does not get inserted. This is the exception I am getting:

Error report -
ORA-04091: table ABC009.SchemeComp is mutating, trigger/function may not see it
ORA-06512: at "ABCS009.TrCreateParentChildRelation", line 3
ORA-04088: error during execution of trigger 'ABCS009.TrCreateParentChildRelation' `

This is my Query for trigger:

CREATE OR REPLACE TRIGGER "TrCreateParentChildRelation"
AFTER INSERT ON "SchemeComp"
FOR EACH ROW
BEGIN
  IF :NEW."ParentComponentCode" IS NOT NULL THEN
    UPDATE "SchemeComp" sc1
    SET sc1."ParentId" = (
        SELECT sc2."Id"
        FROM "SchemeComp" sc2
        WHERE sc1."ParentComponentCode" = sc2."ComponentCode"
          AND sc1."SchemeId" = sc2."SchemeId"
    )
    WHERE sc1."ParentComponentCode" IS NOT NULL
      AND sc1."SchemeId" = :NEW."SchemeId";
  END IF;
END;
/

Below is my insert query, I am running both the insert query at once.

INSERT INTO "SchemeComp" ("ParentId", "ComponentCode", "ParentComponentCode", "ComponentName", "SchemeId", "EntryDate", "IsActive", "CreatedBy", "IP")
VALUES (null, 'B', null, 'Tribal Area Sub Plan', '0R55', SYSTIMESTAMP, '1', 'BANKADMIN002', '::1');

INSERT INTO "SchemeComp" ("ParentId", "ComponentCode", "ParentComponentCode", "ComponentName", "SchemeId", "EntryDate", "IsActive", "CreatedBy", "IP")
VALUES (null, 'B.1', 'B', 'Recurring', '0R55', SYSTIMESTAMP, '1', 'BANKADMIN002', '::1');

Answer 1

You can't directly update (or select values from) table into which you're inserting rows because it is - as error says - mutating.

But, if you rewrite trigger so that you first select desired value and then update it, then it should be OK. Something like this:

CREATE OR REPLACE TRIGGER "TrCreateParentChildRelation"
   BEFORE INSERT
   ON "SchemeComp"
   FOR EACH ROW
DECLARE
   l_parentid  "SchemeComp"."ParentId"%TYPE;
BEGIN
   IF :NEW."ParentComponentCode" IS NOT NULL
   THEN
      SELECT sc2."Id"
        INTO l_parentid
        FROM "SchemeComp" sc2
       WHERE     sc2."ComponentCode" = :new."ParentComponentCode"
             AND sc2."SchemeId" = :new."SchemeId";

      :new."ParentId" := l_parentid;
   END IF;
END;