Function/Trigger Mutating
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
db<>fiddle here