title: How Can We Delete A Table Row Automatically After a Specific Time in PL SQL
body: <p>I have Tow table with Identical Columns Given Below:</p>
  
  <p>Table-1 PAYROLLFILE:</p>
  
  <p><a href="https://i.stack.imgur.com/z6xgO.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/z6xgO.png" alt="enter image description here"></a></p>
  
  <p>Table-2 TEMP_PAYROLLFILE:</p>
  
  <p><a href="https://i.stack.imgur.com/QCxKo.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QCxKo.png" alt="enter image description here"></a></p>
  
  <p>I have written a PL SQL Function, which inserts a duplicate row from Table PAYROLLFILE to TEMP_PAYROLLFILE and remove the original row from Table PAYROLLFILE.So in short, Table TEMP_PAYROLLFILE is keeping a backup for deleted data from  Table PAYROLLFILE.</p>
  
  <p>Here is the PL SQL Function Code:</p>
  
  <pre><code>FUNCTION Remove_transaction_by_id(employee_id NUMBER)
  RETURN CLOB
  AS
    cnt INT;
  BEGIN
      INSERT INTO temp_payrollfile
      SELECT *
      FROM   payrollfile
      WHERE  empid = employee_id;
  
      DELETE FROM payrollfile
      WHERE  empid = employee_id;
  
      SELECT Count(*)
      INTO   cnt
      FROM   payrollfile;
  
      COMMIT;
  
      RETURN '&lt;result&gt;&lt;status&gt;success&lt;/status&gt; &lt;row&gt;'
             ||cnt
             || '&lt;/row&gt;&lt;/result&gt;';
  EXCEPTION
    WHEN OTHERS THEN
               RETURN '&lt;result&gt;&lt;status&gt;Error&lt;/status&gt;&lt;/result&gt;';
  END remove_transaction_by_id; 
  </code></pre>
  
  <p>However, I want to do more than that, <strong>I want to Delete Backup rows Automatically</strong> from Table TEMP_PAYROLLFILE <strong>after a specific time period is over</strong> fort that <strong>specific row</strong>.Do i need triggers.I am new on that and never done this type of work before.If anyone know this kind of technique, then i would really appreciate your help.please let me know if further details required.Thanks</p>
  
  <p>Updates:</p>
  
  <p>I have write this job to cleanup old rows: will it work?</p>
  
  <pre><code>BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
     job_name             =&gt; 'oe.REMOVE_TEMP_PAYROLLFILE_JOB',
     job_type             =&gt; 'PLSQL_BLOCK',
     job_action           =&gt; 'BEGIN DBMS_STATS.GATHER_TABLE_STATS('oe',
                              'TEMP_PAYROLLFILE'); 
                              delete from TEMP_PAYROLLFILE where RECORDDATE &lt; sysdate-1;
                              END;',
     start_date           =&gt; '20-JAN-16 1.00.00AM US/Pacific',
     repeat_interval      =&gt; 'FREQ=DAILY', 
     end_date             =&gt; '25-JAN-16 1.00.00AM US/Pacific',
     enabled              =&gt;  TRUE,
     comments             =&gt; 'Gather table statistics');
  END;
  </code></pre>