title: ORA-04091: table [blah] is mutating, trigger/function may not see it
body: <p>I recently started working on a large complex application, and I've just been assigned a bug due to this error:</p>
  
  <pre><code>ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it
  ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4
  ORA-04088: error during execution of trigger 'SCMA.TRG_T1_TBL1_COL1'
  </code></pre>
  
  <p>The trigger in question looks like </p>
  
  <pre><code>    create or replace TRIGGER TRG_T1_TBL1_COL1
     BEFORE  INSERT OR UPDATE OF t1_appnt_evnt_id ON TBL1
     FOR EACH ROW
     WHEN (NEW.t1_prnt_t1_pk is not  null)
     DECLARE
          v_reassign_count number(20);
     BEGIN
         select count(t1_pk) INTO v_reassign_count from TBL1
                where  t1_appnt_evnt_id=:new.t1_appnt_evnt_id and t1_prnt_t1_pk is not null;
         IF (v_reassign_count &gt; 0) THEN
             RAISE_APPLICATION_ERROR(-20013, 'Multiple reassignments not allowed');
         END IF;
     END;
  </code></pre>
  
  <p>The table has a primary key "<code>t1_pk</code>", an "appointment event id"
  <code>t1_appnt_evnt_id</code> and another column "<code>t1_prnt_t1_pk</code>" which may or may
  not contain another row's <code>t1_pk</code>.</p>
  
  <p>It appears the trigger is trying to make sure that nobody else with the
  same <code>t1_appnt_evnt_id</code> has <strike>referred to the same one this row is referring to</strike> a referral to another row, if this one is referring to another row.</p>
  
  <p>The comment on the bug report from the DBA says "remove the trigger, and perform the check in the code", but unfortunately they have a proprietary code generation framework layered on top of Hibernate, so I can't even figure out where it actually gets written out, so I'm hoping that there is a way to make this trigger work.  Is there?</p>