ORA-04091: table xyz.xyz is mutating, trigger/function may not see it

Question

So, I have create the trigger which will update the data in table if it's null.

CREATE OR REPLACE TRIGGER "XDA.GUID"
AFTER INSERT ON "TEST_GUID" 
FOR EACH ROW
BEGIN
    IF :NEW.GUID IS NULL THEN
        UPDATE "TEST_GUID"
        SET GUID = SYS_GUID()
        WHERE ID = :NEW.ID;
        ROLLBACK;
    END IF;
END;

I need to update the same table after insertion perform on that table.

Now i'm trying to perform

INSERT INTO TEST_GUID (ID, GUID)
VALUES (2, '');

then i'm getting below error.

Error starting at line : 2 in command -
INSERT INTO TEST_GUID (ID, GUID)
VALUES (2, '')
Error report -
ORA-04091: table SYSTEM.TEST_GUID is mutating, trigger/function may not see it
ORA-06512: at "SYSTEM.XDA.GUID", line 3
ORA-04088: error during execution of trigger 'SYSTEM.XDA.GUID'

Please suggest solution.

Answer 1

It's just

CREATE OR REPLACE TRIGGER "XDA.GUID"
BEFORE INSERT ON "TEST_GUID" 
FOR EACH ROW
BEGIN
  :new.guid := nvl(:new.guid, sys_guid());
END;

BTW, why are you rolling it back? Then there's no use in that trigger at all.

Answer 2

You can use:

CREATE OR REPLACE TRIGGER "XDA.GUID"
  BEFORE INSERT ON TEST_GUID
  FOR EACH ROW
  WHEN (NEW.guid IS NULL)
BEGIN
  :NEW.guid := sys_guid();
END;
/

To only run the trigger on NULL values. (Note: In Oracle, '' and NULL are identical.)

fiddle