title: Oracle Trigger Still carrying old value for New
body: <p>I write some trigger to automatically change value in state_id field in ESTABLISHMENT table after user updating ADDRESS table through online apps. The Issue is, when user updating OTHER_STATE_ID = 16 and POSTAL_STATE_ID = 16 in tble Address, the value for OTHER_STATE_ID in table ESTABLISHMENT still carrying old value. Only POSTAL_STATE_ID field updated with new value. I have check the trigger code and found out nothing weird code. It seems that the trigger keep old value for updating other_state_id. Here are data scenario:-</p>
  
  <ol>
  <li><p>Before updating (ADDRESS table)</p>
  
  <ul>
  <li>For ADDRESS_TYPE = 1, value LOCATION_STATE_ID = <strong>8</strong></li>
  <li>For ADDRESS_TYPE = 2, value POSTAL_STATE_ID = <strong>8</strong></li>
  <li>For ADDRESS_TYPE = 11, value OTHER_STATE_ID = <strong>8</strong></li>
  </ul>
  
  <p>Before triggering (ESTABLISHMENT table)</p>
  
  <ul>
  <li>value LOCATION_STATE_ID = <strong>8</strong></li>
  <li>value POSTAL_STATE_ID = <strong>8</strong></li>
  <li>value OTHER_STATE_ID = <strong>8</strong> </li>
  </ul></li>
  <li><p>Value after updating (ADDRESS table)</p>
  
  <ul>
  <li>User update value LOCATION_STATE_ID = <strong>8</strong></li>
  <li>For ADDRESS_TYPE = 2, value POSTAL_STATE_ID = <strong>16</strong></li>
  <li>For ADDRESS_TYPE = 11, value OTHER_STATE_ID = <strong>16</strong></li>
  </ul>
  
  <p>After triggering (ESTABLISHMENT table)</p>
  
  <ul>
  <li>value LOCATION_STATE_ID = <strong>8</strong></li>
  <li>value POSTAL_STATE_ID = <strong>16</strong></li>
  <li>value OTHER_STATE_ID = <strong>8</strong></li>
  </ul></li>
  </ol>
  
  <p>Maybe something weird happen in this lines of codes. Anyone..?</p>
  
  <p>Full Codes As below</p>
  
  <blockquote>
  <pre><code>    create or replace
  TRIGGER "TR_ADDRESS_EE"
     AFTER INSERT OR UPDATE
     ON ADDRESS
     FOR EACH ROW
  BEGIN
     IF INSERTING
     THEN
        IF (:NEW.STATE_ID IS NOT NULL)
        THEN
           IF (:NEW.RECORD_TYPE_ID = 401)
           THEN
              IF (:NEW.ADDRESS_TYPE = 1)
              THEN
                 UPDATE ESTABLISHMENT
                    SET LOCATION_STATE_ID = :NEW.STATE_ID
                  WHERE ESTABLISHMENT_ID = :NEW.RECORD_ID;
              ELSIF (:NEW.ADDRESS_TYPE = 2)
              THEN
                 UPDATE ESTABLISHMENT
                    SET POSTAL_STATE_ID = :NEW.STATE_ID
                  WHERE ESTABLISHMENT_ID = :NEW.RECORD_ID;
              ELSIF (:NEW.ADDRESS_TYPE = 11)
              THEN
                 UPDATE ESTABLISHMENT
                    SET OTHER_STATE_ID = :NEW.STATE_ID
                  WHERE ESTABLISHMENT_ID = :NEW.RECORD_ID;
              END IF;
           END IF;
        END IF;
     ELSIF UPDATING ('STATE_ID')
     THEN
        IF (:OLD.RECORD_TYPE_ID = 401 AND :NEW.STATE_ID &lt;&gt; :OLD.STATE_ID)
        THEN
           IF (:NEW.ADDRESS_TYPE = 1)
           THEN
              UPDATE ESTABLISHMENT
                 SET LOCATION_STATE_ID = :NEW.STATE_ID
               WHERE ESTABLISHMENT_ID = :OLD.RECORD_ID;
           ELSIF (:NEW.ADDRESS_TYPE = 2)
           THEN
              UPDATE ESTABLISHMENT
                 SET POSTAL_STATE_ID = :NEW.STATE_ID
               WHERE ESTABLISHMENT_ID = :OLD.RECORD_ID;
           ELSIF (:NEW.ADDRESS_TYPE = 11)
           THEN
              UPDATE ESTABLISHMENT
                 SET OTHER_STATE_ID = :NEW.STATE_ID
               WHERE ESTABLISHMENT_ID = :OLD.RECORD_ID;
           END IF;
        END IF;
     END IF;
  END;
  </code></pre>
  </blockquote>