trigger mutating error

Question

I have emp table in schema1 and emp_fianl in schema2.

emp  
empid  ename  estatus
1      abc    incomplete
2      xyz    complete
3      ifg    incomplete
4      mno    incomplete

Emp_final
empid  ename  estatus
2      xyz    complete

I have to create a trigger to insert data in Schema2 emp_final table when the estatus in schema1 emp table changes to complete. I have written below trigger for the same:

Create or replace trigger tri_emp_final  
After update on emp  
BEGIN  
IF :new.estatus='complete' then
Insert into emp_final  
(select :old.empid,:old.ename,:new.estatus from schem1.emp);  
END IF;  
END;  
/

I am getting mutating error message for the above code. When I am trying to update the status in emp table. I am a java developer and do not have much experience in Oracle, SQL. Can anyone please help?

Answer 1

First thing: we you want to use :old and :new, your trigger MUST be FOR EACH ROW. So you need to change : before update on emp for each row. Second: like Goran Stefanović wrote, you don't make that select to insert, just use the :old values.

Answer 2

Use

Create or replace trigger tri_emp_final  
After update on emp  for each row
BEGIN  
  IF :new.estatus='complete' then
    Insert into emp_final ( empid , ename , estatus)
    Values
    (:old.empid,:old.ename,:new.estatus );  
  END IF;  
END;  
/

---

You can find a working demo here