0

I got an example that better explains the situation. I have a table A

CREATE TABLE A( ID NUMBER, VAL NVARCHAR2(255) )

and I create a trigger that does an update on the row it's just inserted

CREATE OR REPLACE TRIGGER XXX
    AFTER INSERT 
    ON A
    FOR EACH ROW

DECLARE

BEGIN
    UPDATE A SET VAL = 'LOL' WHERE ID = :NEW.ID;
END;

When I perform an insert

INSERT INTO A VALUES(1, 'XX')

I get 

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

Is there a workaround?

Phate01
  • 2,499
  • 2
  • 30
  • 55
  • 1
    Possible duplicate of [Update same table after Insert trigger](http://stackoverflow.com/questions/26072577/update-same-table-after-insert-trigger) – vc 74 Apr 01 '16 at 09:14
  • That linked (duplicate) question shows one approach; but that has a reason (sort of) to insert and then update. In your dummy code it isn't obvious why you'd want to do this, rather than just supplying 'LOL' in the insert or a before-insert trigger. There may be a more suitable or more complete answer if you explain your real-worl requirement. – Alex Poole Apr 01 '16 at 10:02

1 Answers1

0

You don't need an update, just assign the new value in a BEFORE trigger.

CREATE OR REPLACE TRIGGER XXX
    BEFORE INSERT --<< You need a BEFORE trigger for this to work.
    ON A
    FOR EACH ROW
BEGIN
    :new.val := 'LOL';
END;
  • 1
    @Phate01: you should really take the time to read the Oracle manual. This is all documented there –  Apr 01 '16 at 10:09