create trigger for IF condition in oracle

Question

I have created a trigger which inserts City as Others for all the data inserted with City name OTHERS

But it updates as null for text other than OTHERS. So I want to add IF condition for the same. Below is my trigger.

create or replace TRIGGER TGR_UPD_OTHERS_TVIPL
   BEFORE INSERT OR UPDATE ON IPCOLO_IPFEE_CALC_MST
FOR EACH ROW
 BEGIN
     new.CITY := case :NEW.CITY
                     when 'OTHERS' THEN 'Others'
                     end;
END;

Littlefoot · Accepted Answer · 2022-06-16 20:35:10Z

1

That's because you didn't say what to do "else":

:new.city := case when :new.city = 'OTHERS' then 'Others'
                  else :new.city
             end;

answered Jun 16, 2022 at 20:35

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Littlefoot Over a year ago

In front end; it is responsible for displaying values. Somewhere is something set to display 0 as "undefined". Could even be in front end's query (using CASE or DECODE).

Littlefoot Over a year ago

Should work for any table (unless there's something that prevents it to; perhaps another trigger?).

Littlefoot Over a year ago

If you can afford dropping it, do it and you'll see whether there's any difference. On the other hand, did you debug it first? I mean, maybe there's some (not that) obvious reason which prevents trigger from working.

HEEN Over a year ago

I cant see any reason for the same. yes i can afford deleting the table as its on dev env.

Littlefoot Over a year ago

What is "it"? Select statement you ran in e.g. SQL Developer, or your front-end application?

|

Collectives™ on Stack Overflow

create trigger for IF condition in oracle

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related