CODEWITHSUNDEEP
sqloracle-databasetriggers
John Smith
John Smith

Reputation: 129

How to set a column value according to another column value Trigger statement?

Imagine there are two columns in a table; weather and tool 

WEATHER TOOL 
sun
autumn 
rain

if weather = sun, then the tool should have value 'no umbrella' also if weather = autumn, then the tool should have value 'no umbrella' however if weather = rain, then the tool should have value 'umbrella'

The end table should be generated using trigger or during insert statement. 

CREATE OR REPLACE EDITIONABLE TRIGGER "UPDATE_TOOL" 
before insert on "UPDATING_TOOL_TABLE"
for each row
begin
IF WEATHER = 'sun' THEN 
    SET TOOL = 'no umbrella';
ELSE IF WEATHER = 'autumn' THEN
    SET TOOL = 'no umbrella'; 
ELSE IF WEATHER = 'rain' THEN 
    SET TOOL = 'umbrella'; 
END IF; 

end;
 /
ALTER TRIGGER "UPDATE_TOOL" ENABLE;

Please have a look on the Trigger statement I have created, which doesn't work. I am working in ORACLE SQL.

Thank you.

Upvotes: 1

Views: 190

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270763

In Oracle 11g+, you don't need to actually store the value. You can generate it on the fly:

alter table UPDATING_TOOL_TABLE
    add tool generated always as (case when weather in ('sun', 'autumn') then 'no umbrella' when weather in ('rain') then 'umbrella' end);

EDIT:

As for your trigger, I would expect something like:

begin
    select (case when :new.weather in ('sun', 'autumn') then 'no umbrella' 
                 when :new.weather in ('rain') then 'umbrella'
            end) into :new.tool
    from dual;
end;

or:

begin
    :new.tool := (case when :new.weather in ('sun', 'autumn') then 'no umbrella' 
                       when :new.weather in ('rain') then 'umbrella'
                  end);
end;

Upvotes: 1

Related Questions