title: Oracle PL/SQL Trigger: Reset column value automatically after an update statement
body: <p>For testing purpose I would like to have the following scenario to be automated:</p>
  
  <ol>
  <li><p>Set column <strong>Status</strong> of my table to the value <strong>closed</strong> (using an update statement)</p></li>
  <li><p>Commit the update so that other user can be seeing new value of <strong>Status</strong></p></li>
  <li><p>Wait for 1 minutes</p></li>
  <li><p>Reset column <strong>Status</strong> of my table to its original value <strong>init</strong> (using a trigger)</p></li>
  <li><p>Commit the update so that other user can be seeing the original value of <strong>Status</strong></p></li>
  </ol>
  
  <p>I've tried using this trigger but it won't work and I got </p>
  
  <blockquote>
    <p>ORA-04091: table name is mutating, trigger/function may not see it</p>
  </blockquote>
  
  <pre><code>CREATE OR REPLACE TRIGGER RESET_COLUMN
  AFTER UPDATE OF STATUS ON MY_TABLE
  FOR EACH ROW
  WHEN (NEW.STATUS != 'INIT')
  BEGIN
      DBMS_LOCK.SLEEP(60);  
      UPDATE MY_TABLE SET STATUS = 'INIT';
      COMMIT;
  END;
  /
  </code></pre>
  
  <p>Since it is not allowed to commit inside a trigger, is there any other approach to solve this problem? Using a job based on event?</p>