I have created a trigger that should change the value of another column based on the value of one column in the table.
For this I use a biutrigger (BEFORE INSERT OR UPDATE) Trigger:
create or replace trigger INSERT_STEP_TBL1
before insert or update on TBL1
for each ROW
begin
IF :NEW.COLUMN1 ='NOT_REQUESTED' THEN
:NEW.COLUMN2 := 'TEXT123';
ELSIF :NEW.COLUMN1 ='REQUESTED' THEN
:NEW.COLUMN2:='OTHERTEXT123';
END IF;
END;
As soon as an entry is made in the table, and thus the column changes, the other column should change.
However, since this trigger is a before insert or update trigger, it takes the old value and sets the value of the other column based on that.
I have read in the forum that an UPDATE statement can be used to change the value of a row that has been entered.
'To effect a row after it's been inserted, you use a separate UPDATE statement. If the data needs to be changed during an insert, then you have to set the values BEFORE they actually get inserted. '
It is meant to use the update statement like this
create or replace TRIGGER INSERT_STEP_TBL1
BEFORE INSERT OR UPDATE ON TABLE1
FOR EACH ROW
begin
--Direct the student to the thesis registration page
IF :NEW.STATE='NOT_REQUESTED' THEN
update TABLE1
set COLUMN2='TEXT123';
end;
However I get no error on this, but the trigger still takes the before insert value from the table, which is not the intention.
Can you help me figuring out, what is meant with this comment. Since I am quite sure this is what I need.
'To effect a row after it's been inserted, you use a separate UPDATE statement. If the data needs to be changed during an insert, then you have to set the values BEFORE they actually get inserted. '
Thank you!