Reputation: 835
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
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