How to update a table with a trigger-CodePudding

I am trying to create a trigger that reads from table 2 and updates a column in table 1. I tried with this method but an exception occurred: ORA-04091:table table1 is mutating .

CREATE OR REPLACE TRIGGER "TRG1"
AFTER INSERT OR UPDATE ON table1
FOR EACH ROW
 BEGIN
 UPDATE table1 SET name =(SELECT name FROM table2
WHERE table1.id = table2.id);
 END;

CodePudding user response：

Use a BEFORE UPDATE trigger (as you cannot modify values after they have been inserted or updated) and use the :NEW record (rather that trying to update the table and getting into an infinite loop of triggers):

CREATE OR REPLACE TRIGGER TRG1
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
BEGIN
  SELECT name
  INTO   :NEW.name
  FROM   table2
  WHERE  :NEW.id = id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    :NEW.name := NULL;
END;
/

fiddle

CodePudding user response：

You can only update the name in a before trigger

CREATE OR REPLACE TRIGGER "TRG1"
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
  DECLARE new_name varchar2(100);
 BEGIN
   
  
       SELECT name INTO new_name FROM table2
        WHERE :new.id = table2.id;
     :new.name :=new_name;   
 END;
/

MTO was so nice to make a fiddle