Oracle Trigger to replace values in a record-CodePudding

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

CodePudding user response：

Something like this?

CREATE OR REPLACE TRIGGER test_trigger
   BEFORE INSERT
   ON "test_list"
   FOR EACH ROW
BEGIN
   IF     :new.text_lead_id IN ('150', '151')
      AND :old.test_id = '1000'
   THEN
      :new.text_id := '2000';
   END IF;
END;

CodePudding user response：

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;

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;