Function/Trigger Mutating-CodePudding

Is there any way to write after update trigger on the same table for the below problem

Problem statement: whenever the user updates selection column from yes to no with items as 'TV' and id as 1 then selection for the same id and items as 'remote' record should be updated with selection as 'No'

Trigger:

Create trigger ttt 
After update on home_items
For each row
Begin
If (new.items = 'TV' AND new.selection = 'No') Then
Update home_items h set h.selection = 'No' where new.id = h.id and h.items = 'Remote';
End If;
End;

Error: ORA-04091: table home_items is mutating, trigger/f0unction may not see it

CodePudding user response：

Use a compound trigger:

CREATE TRIGGER remove_tv_and_remote 
FOR UPDATE ON home_items
COMPOUND TRIGGER
  TYPE ids_type IS TABLE OF HOME_ITEMS.ID%TYPE;
  ids ids_type := ids_type();
AFTER EACH ROW
  IS
  BEGIN
    IF     :new.items     = 'TV'
       AND :new.selection = 'No'
       AND :old.selection = 'Yes'
    THEN
      ids.EXTEND(1);
      ids(ids.COUNT) := :new.id;
    END IF;
  END AFTER EACH ROW;
AFTER STATEMENT
  IS
  BEGIN
    FORALL i IN 1 .. ids.count
      UPDATE home_items
      SET   selection = 'No'
      WHERE id        = ids(i)
      AND   selection = 'Yes'
      AND   items     = 'Remote';
  END AFTER STATEMENT;
END;
/

If you have the table:

CREATE TABLE home_items (id, items, selection) AS
SELECT 1, 'TV',     'Yes' FROM DUAL UNION ALL
SELECT 1, 'Remote', 'Yes' FROM DUAL;

And then do:

UPDATE home_items
SET   selection = 'No'
WHERE items     = 'TV';

Then:

SELECT * FROM home_items;

Outputs:

ID ITEMS SELECTION

1 TV No

1 Remote No

ID	ITEMS	SELECTION
1	TV	No
1	Remote	No

db<>fiddle here

CodePudding user response：

This infamous ORA-04091: table ... is mutating means, Oracle is telling you: "I cannot guarantee, that what you want is deterministic".

Imagine that you run an update on a table home_items, this update would modify 2 rows in random order (the order of updates cannot be predicted).

After update of 1st row you execute a trigger, which will see 2nd row and not-modified. And now imagine that due to change of exec plan the order of rows updated is opposite.

There are various ways how to overcome this "problem", many articles were written about this topic. But the root of the problem is determinism of PL/SQL code, Oracle is not smart enough to deduce whether your code is deterministic or not.

PS: in your case you might need just change your trigger into before update.