CODEWITHSUNDEEP
oracle-databaseoracle11gtriggers
Krishna
Krishna

Reputation: 835

Why doesn't the trigger update the column value in an Oracle database?

I am using oracle 11gR1 database. The problem is, I have a table which script is given below :- 

CREATE TABLE TEST(
  ID       NUMBER, 
  CATEGORY VARCHAR2(19),
  CODE     VARCHAR2(20),
  URI      VARCHAR2(160));

Now my aim is to create a trigger which updates URI column every time with below calculated string when row in the TEST table either getting inserted or updated.

URI column will be calculated using 'uri://cateogry/code'. So I created a trigger: 

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;
/

Values will be inserted only in ID, CATEGORY, CODE columns not in URI column.

Now if I am inserting or updating values in to TEST table.

The trigger does not update URI column of TEST table (don't know why). Even though values are getting printed by DBMS_OUTPUT print message.

I tried to do using VIRTUAL COLUMN in Oracle, but that doesn't satisfied my requirement, because I want URI column values must be present in Database which is not possible with it.

So could you please any body help me and suggest any way to do it.

Upvotes: 1

Views: 1403

Answers (1)

Rene
Rene

Reputation: 10551

Create a before row trigger. then change the URI value as below

:NEW.URI := 'uri://'||upper(:NEW.CATEGORY)||'/'||upper(:NEW.CODE);

No need to select or update. The values are available using the :NEW pseudo column,

Upvotes: 5

Related Questions