Oracle 11g trigger to replace part of a text string

Question

I'm having issues with a trigger that keeps mutating. The goal of the trigger is when a file path is updated change part of the string. Here is what I have so far, it compiles just fine.

CREATE OR REPLACE TRIGGER TAU_ATTACHMENTS
AFTER UPDATE
ON ATTACHMENTS
FOR EACH ROW
 BEGIN
  IF :new.PATH LIKE 'file://Y:%' THEN   
    UPDATE ATTACHMENTS SET PATH = REPLACE(:new.PATH, 'file://Y:','file://\\MPS7536');
  END IF;
END;
/

When I do a test of updating a single row I get the usual mutating table error. Any advice? I'd be extremely grateful!

Answer 1

You are obviously want to modify value of a column in trigger before storing it. But instead the provided code is trying to update whole attachments table. You can modify the value of record which is being updated like this:

CREATE OR REPLACE TRIGGER TAU_ATTACHMENTS
BEFORE UPDATE
ON ATTACHMENTS
FOR EACH ROW
 BEGIN
  IF :new.PATH LIKE 'file://Y:%' THEN   
    :new.PATH = REPLACE(:new.PATH, 'file://Y:','file://\\MPS7536');
  END IF;
END;
/

Note that this should be BEFORE UPDATE trigger.