title: How Can I code a "IF UPDATING" trigger in Oracle Database 10g?
body: <p>I'm coding a Trigger to ensure only one type of money can be set as official. My intention is code a "BEFORE INSERT OR UPDATE" trigger. The INSERT section works fine but the problem is coding the UPDATING section because when I try to update the table I recieve ORA-04091 "mutanting table". Do you have any idea?</p>
  
  <p>Table (Only one record can be set as 'Y'):</p>
  
  <pre><code>    mon_id  mon_description  mon_official
  ----------------------------------------------
      E            EUR              N
      D            DOL              N
      P            PES              Y
  </code></pre>
  
  <p>Trigger:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER mon_oficial_ins_trg
  BEFORE
  INSERT OR UPDATE
  ON monedas
  FOR EACH ROW
  DECLARE
      v_count  NUMBER(8);
  BEGIN
      IF INSERTING THEN
  
          SELECT COUNT(mon_oficial)
          INTO   v_count
          FROM   monedas
          WHERE  mon_oficial = 'Y';
  
          IF v_count = 1 THEN
              RAISE_APPLICATION_ERROR(
                  -20010, 'Only one record can be set as 'Y'');
          END IF;
  
      END IF;
  
      IF UPDATING THEN
  
          SELECT COUNT(:OLD.mon_oficial)
          INTO   v_count
          FROM   monedas
          WHERE  :OLD.mon_oficial = 'Y';
  
          IF v_count = 1 AND :NEW.mon_oficial = 'Y' THEN
                  RAISE_APPLICATION_ERROR(
                      -20010, 'Only one record can be set as 'Y'');
          END IF;
  
      END IF;
  
  
  END mon_oficial_ins_trg;
  /
  SHOW ERRORS;
  </code></pre>