I would like to insert an entry into a column depending on the column of the same table. For that I thought of creating a trigger.
This trigger checks which entry the certain column has in the table and then decides what to enter in another in the same table and in the same row.
create or replace TRIGGER INSERT_COLUMN2_TABLE1 BEFORE INSERT OR UPDATE ON TABLE1 FOR EACH ROW begin -- IF :NEW.COLUMN ='REQUESTED' THEN :NEW.COLUMN2 := 'REQUEST_PENDING'; ELSEIF :NEW.COLUMN ='NOT_REQUESTED' THEN :NEW.COLUMN2:='TEXT123' END IF; END;
but I get following errors when trying to execute the trigger:
|PLS-00103: Encountered the symbol "" when expecting one of the following: := . ( @ % ;|
|PLS-00103: Encountered the symbol ";" when expecting one of the following: if|
as well I think this would be an after insert or update trigger? For the reason that it should be fired when an entry is already done in the column..
But how can I in general reference column in a trigger from the same table where I try to fire a trigger on?
Maybe the better question is, how can one column value be populated based on another column in the same table. should I really use a trigger for that?
--Used a virtual column for that
ALTER table1 ADD ( column2 as ( case when column1 ='NOT_REQUESTED' then 'TEXT123' when column1 ='REQUESTED' then 'REQUEST_PENDING' end;) ) It works but how can it be entered automatically without always having to run the code snippet?