title: Trigger to insert old values
body: <p>I have the following trigger on my table, issue I am facing is in <code>my_hist</code> table both old and new values are getting inserted when condition becomes <code>updated = 'Y'</code>.</p>
  
  <p>How can I efficiently insert old values into my_hist table when condition becomes <code>updated = 'Y'</code>.  </p>
  
  <p>Thanks</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER mytrig
      AFTER UPDATE
      ON mytab
      FOR EACH ROW
  WHEN (
  new.updated = 'Y'
        )
  BEGIN
      INSERT INTO my_hist
        VALUES   (
                          :old.id,
                          :old.no,                        
                          :old.start_date,
                          :old.end_date,
                          SYSDATE
                    );
  END mytrig;
  /
  </code></pre>
  
  <p>Update 1</p>
  
  <p>If I am updating <code>updated = 'Y'</code> then if I have records</p>
  
  <p>id = 3, start_date='01-Jan-2014' and end_date='31-Jan-2014' and updated data
  <code>id = 3, start_date=='01-Jan-2014' and end_date='31-Mar-2014'</code> then I will have two records in my_hist table are</p>
  
  <pre><code>id = 3 and start_date='01-Jan-2014' and end_date='31-Jan-2014'
  </code></pre>
  
  <p>and </p>
  
  <pre><code>id = 3 and start_date='01-Jan-2014' and end_date='31-Mar-2014'
  </code></pre>
  
  <p>Ideally I should have only <code>id = 3 and start_date='01-Jan-2014' and end_date='31-Jan-2014'</code> in my_hist table, not the second row with <code>end_date='31-Mar-2014'</code> because that is new data.</p>