title: Why doesn't the trigger update the column value in an Oracle database?
body: <p>I am using <strong>oracle 11gR1</strong> database. The problem is, I have a table which script is given below :- </p>
  
  <pre><code>CREATE TABLE TEST(
    ID       NUMBER, 
    CATEGORY VARCHAR2(19),
    CODE     VARCHAR2(20),
    URI      VARCHAR2(160));
  </code></pre>
  
  <p>Now my aim is to <strong>create a trigger</strong> which updates <code>URI</code> column every time with below calculated string when row in the <code>TEST</code> table <strong>either getting inserted or updated.</strong></p>
  
  <p><code>URI</code> column will be calculated using <code>'uri://cateogry/code'</code>. So I created a trigger:  </p>
  
  <pre><code>CREATE OR REPLACE TRIGGER TEST_URI_UPDATE_TRIG
  AFTER INSERT OR UPDATE OF CATEGORY, CODE ON TEST
  FOR EACH ROW
    DECLARE
      PRAGMA AUTONOMOUS_TRANSACTION; --added later after getting error(mutating, trigger/function may not see it error) during insertion
    BEGIN
      DBMS_OUTPUT.PUT_LINE('-------------EXECUTING TRIGGER----------');
  
      UPDATE TEST
         SET URI = 'uri://' || upper(:NEW.CATEGORY) || '/' || upper(:NEW.CODE)
       WHERE ID = :NEW.id;
  
      --Just to print the output
      DBMS_OUTPUT.PUT_LINE('OLD VALUE IS : ' || :OLD.ID || '   EXECUTING TRIGGER ID IS : ' || :NEW.ID);
      DBMS_OUTPUT.PUT_LINE('OLD VALUE IS : ' || UPPER(:OLD.CATEGORY) || '   EXECUTING TRIGGER ID IS : ' || upper(:NEW.CATEGORY));
      DBMS_OUTPUT.PUT_LINE('OLD VALUE IS : ' || UPPER(:OLD.CODE) || '   EXECUTING TRIGGER ID IS : ' || upper(:NEW.CODE));
      COMMIT; --added later after getting error(mutating, trigger/function may not see it error) during insertion
    END;
  /
  </code></pre>
  
  <p>Values will be inserted only in <code>ID, CATEGORY, CODE</code> columns not in <code>URI</code> column.</p>
  
  <p>Now if I am inserting or updating values in to TEST table.</p>
  
  <p>The trigger does not update <code>URI</code> column of <code>TEST</code> table (don't know why). Even though values are getting printed by <code>DBMS_OUTPUT</code> print message. </p>
  
  <p>I tried to do using <code>VIRTUAL COLUMN</code> in Oracle, but that doesn't satisfied my requirement, because I want <strong><code>URI</code> column values must be present in Database which is not possible with it.</strong></p>
  
  <p>So could you please any body help me and suggest any way to do it.</p>