CODEWITHSUNDEEP
sqloracle-database
reallybadatmath
reallybadatmath

Reputation: 315

Oracle SQL Trigger with IF Comparison

I want to create a trigger TR_rate, that checks a "features" field on new rows as they are inserted and adjusts a rate based on the features included.

The features field can contain multiple values, and I want the condition to apply for each value. So if the field contains 'Feature1, Feature2', the rate should go up by 35.

My current code approach only works if there is one feature. How can I rewrite it to accomodate for multiple features?

CREATE OR REPLACE TRIGGER TR_rate
BEFORE INSERT 
ON rates_table 
FOR EACH ROW
BEGIN
    IF(:NEW.features = 'Feature1') THEN 
        :NEW.rate := (:NEW.rate + 15); 
    IF(:NEW.features = 'Feature2') THEN 
        :NEW.rate := (:NEW.rate + 20); 
    IF(:NEW.features = 'Feature3') THEN 
        :NEW.rate := (:NEW.rate + 30); 
    END IF; 
    END IF; 
    END IF; 
END;
/

Upvotes: 0

Views: 63

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You need your if statements to be sequential rather than nested, as well as using like:

IF(:NEW.features like '%Feature1%') THEN 
    :NEW.rate := (:NEW.rate + 15); 
END IF;
IF(:NEW.features like '%Feature2%') THEN 
    :NEW.rate := (:NEW.rate + 20); 
END IF;
IF(:NEW.features like '%Feature3%') THEN 
    :NEW.rate := (:NEW.rate + 30); 
END IF;

However, I would just do this using a select:

select (new.rate +
        (case when :new.features like '%Feature1%' then 15 else 0 end) +
        (case when :new.features like '%Feature2%' then 20 else 0 end) +
        (case when :new.features like '%Feature3%' then 30 else 0 end)
       )
into :new.rate;

You can do the same thing at the PL/SQL level, but this captures the logic in a single statement.

Upvotes: 2

Alvin Thompson
Alvin Thompson

Reputation: 5448

Repeat for all three:

IF(:NEW.features like '%Feature1%') THEN

Upvotes: 1

Related Questions