0

I would like to insert a value into a column depending on the value of another column in the same table.

For this I created the following trigger:

 create or replace TRIGGER INSERT_COLUMN2_TBL1
 BEFORE INSERT OR UPDATE ON TBL1
  FOR EACH ROW
begin
:new.column1 := CASE 
  when :new.column1 LIKE 'REQUESTED' then 
  INSERT INTO tbl1 (column2)
  VALUES ('REQUEST_PENDING');
  when :new.column1 LIKE 'NOT_REQUESTED' then
  INSERT INTO tbl1 (column2)
  VALUES ('TEXT123');
end;

I am getting following errors:

PLS-00103: Encountered the symbol "INSERT" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> continue
PLS-00103: Encountered the symbol "ELSE"

I am not sure how I can reference columns from the same table I try to fire the trigger on. Can it be done like this or is there a better approach?

user111
  • 137
  • 1
  • 15

1 Answers1

0

You can't insert into tbl1 in a trigger on tbl1. It seems unlikely that you would want to create a new row in any case. My guess is that you really want to set the column2 value for the current row.

if :new.column1 = 'REQUESTED'
then 
  :new.column2 := 'REQUEST_PENDING';
else
  :new.column2 := 'TEXT123';
end if;

Of course, if you are doing this on insert or update, that strongly implies that there is no point to having the column2 column in the first place since the value will always be derivable from the column1 value.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • I wanted to check the column for several cases, so the trigger must involve several elseifs if :new.column1 = 'REQUESTED' then :new.column2 := 'REQUEST_PENDING'; elseif if :new.column1 = 'NOT_REQUESTED' :new.column2 := 'TEXT123'; end if; – user111 Apr 16 '21 at 14:36
  • @gisnow - OK. That wasn't in your question but it looks like you understand how to extend the `if` statement. It still appears to violate basic normalization to have a `column2` in the first place here given how the trigger is defined. – Justin Cave Apr 16 '21 at 14:37
  • Maybe the better question then would be how can one column value be populated based on another column in the same table. should I really use a trigger for that? – user111 Apr 16 '21 at 15:49
  • @gisnow - It's a question of should you use another column for that. If you were populating the new column with some initial value and it was subsequently allowed to change, that would potentially indicate that `column2` made sense as a separate column. – Justin Cave Apr 16 '21 at 21:19