title: How to use trigger to insert a new row in same table (Oracle PL/SQL 10G)?
body: <p>My table structure is like this:</p>
  
  <pre><code>ID   NAME   DESG     START_DT     END_DT      CURRENT_FLAG
  101  John   Trainee  01-Jan-2016  01-08-2017  Yes
  </code></pre>
  
  <p>Now I want to update this record (change in Designation) &amp; at the same time his old history should be inserted as a new row. Somewhat like this:</p>
  
  <pre><code>ID   NAME   DESG       START_DT      END_DT       CURRENT_FLAG
  101  John   Trainee    01-01-2016   01-08-2017    No
  102  John   Associate  02-08-2016   01-01-2099    Yes
  </code></pre>
  
  <p>I tried using Trigger with following code but this returned me some error.</p>
  
  <p><strong>CODE</strong></p>
  
  <pre><code>CREATE or REPLACE TRIGGER TRI1
  BEFORE UPDATE OF type ON TB818 
  FOR EACH ROW
  BEGIN 
  INSERT INTO TB818
  VALUES 
  (:old.id,
   :old.name, 
   :old.desg, 
   :old.start_dt,
   :old.end_dt,
   :old.current_flag);
  END;
  
  ----------------------------
  
  &gt; Update TB818 Set Desg='G' where ID=1   
  </code></pre>
  
  <p><strong>ERROR:</strong></p>
  
  <pre><code>ORA-04091: table SYSTEM.TB818 is mutating, trigger/function may not see it
  ORA-06512: at "SYSTEM.TRI1", line 2
  ORA-04088: error during execution of trigger 'SYSTEM.TRI1'
  1. Update TB818 Set type='G' where Id=1
  </code></pre>
  
  <p>How can I achieve this ?</p>