How do I fix ORA-04091: table is mutating, trigger/function may not see it?

user_13545696 0
Viewed 888

I want to create a trigger that makes the value of trgr equal to the value of seq+100 on the same row. This is what I did but I keep getting the error "table is mutating, trigger/function may not see it".

CREATE OR REPLACE TRIGGER update_tgr
AFTER INSERT ON challenge FOR EACH ROW
BEGIN
    UPDATE challenge
        SET trgr = :NEW.seq + 100
        WHERE seq = :NEW.seq;
END;

What can I do to fix it?

triggers plsql oracle
2 Answers

The same row? That is simply:

CREATE OR REPLACE TRIGGER update_tgr
BEFORE INSERT ON challenge FOR EACH ROW
BEGIN
  :NEW.trgr := :NEW.seq + 100;
END;

You see you should use a BEFORE INSERT trigger, because then you are still able to manipulate the new data.

user_22707620

I think creating a Virtual Column is a better option if your DB version is 11g+. Since no need to store a column on disk which can already been computed. This prevents inserting wrong values possibility for the column (trgr) unlike traditional definition style. Then, you can recreate your table such as

CREATE TABLE challenge (
  .....
  seq         INT,
  trgr        AS (seq + 100), -- [VIRTUAL]
  ----
);

no need to bother about the data type which is already determined based on the result of the expression(INT). You can query the the like an ordinary table

user_58413060
Related