title: Update statement in a trigger or a scheduled job in Oracle?
body: <p>I want to update certain rows if the status of a device is changed. Here is the plain update statement:</p>
  <pre><code>update REMINDER set FLAG_LIST = 0 where ID in 
  (select r.id from REMINDER r 
      join DEVICE d
          on d.id = (regexp_replace(r.origin_values, '[^0-9]', ''))         
          and d.status &lt;&gt; 0
          and r.context = 'DEVICE'
          and r.flag_list &lt;&gt; 0);
  </code></pre>
  <p>I wrote a trigger like below but I get an ORA-04091 (table DEVICE is mutating, trigger/function may not see ist) when i change the status of a device:</p>
  <pre><code>create or replace TRIGGER DEACTIVATE_REMINDER 
  AFTER UPDATE OF STATUS ON DEVICE 
  for each row
  BEGIN
  if updating then
      if (:old.STATUS = 0 and :new.STATUS != 0) then
          update REMINDER set FLAG_LIST = 0 where ID in 
              (select r.ID from REMINDER r 
                  join DEVICE d
                      on d.id = (regexp_replace(r.ORIGIN_VALUES, '[^0-9]', ''))         
                      and d.STATUS &lt;&gt; 0
                      and d.ID = :new.ID
                      and r.CONTEXT = 'DEVICE'
                      and r.FLAG_LIST &lt;&gt; 0);
      end if;
  end if;
  END;
  </code></pre>
  <p>Maybe i can not use a trigger here, because the same object whose modification triggered the trigger must not be modified or read in a row-level trigger? Is it better to use a scheduled job somehow?</p>
  <p>What I also want to do is to update the <code>FLAG_LIST</code> back to <code>1</code>, if the <code>STATUS</code> of a device is changed back to <code>0</code>. The only way i can imagine to do this is also in the trigger, but as i wrote I`m not sure if this is basically possible.</p>
  <p>Thank you in advance!</p>