Oracle Trigger to replace values in a record

Question

I want to update the inserted value in a column from a table but only if it matches a value from a different column. So both records are in the same table but we only want to update the value test_id if the test_lead_id corresponds with certain values. I am trying this but with no luck....

`CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON "test_list" 
FOR EACH ROW BEGIN 
 IF :old.test_id = '1000' then
  :new.test_id := '2000' 
WHEN test_lead_id in ('150','151');
 END IF; 
END; 
`

Any help is appreciated. Many thanks

Answer 1

There is no old value for an insert statement. Try this:

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON "test_list" 
FOR EACH ROW 
WHEN (NEW.test_lead_id in ('150','151'))
BEGIN 
 IF :new.test_id = '1000' then
  :new.test_id := '2000';
 END IF; 
END;

Or

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT ON "test_list" 
FOR EACH ROW 
WHEN (NEW.test_lead_id in ('150','151') and new.test_id = '1000')
BEGIN 
  :new.test_id := '2000';
END;