Oracle SQL Trigger with IF Comparison

Question

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;
/

A column in a database should never, ever, under any circumstances, contain a comma-separated list of values. Ever. You really, really, really want to create a separate table that maps rows in rates_table to features (i.e. rate_feature). You could then iterate through the features associated with a particular rate. — Justin Cave
– Justin Cave, Commented May 23, 2014 at 18:02

Gordon Linoff · Accepted Answer · 2014-05-23 17:28:50Z

2

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.

answered May 23, 2014 at 17:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Alvin Thompson Over a year ago

I assume from his code that you can only have Feature2 if you also have Feature1, etc.

Alvin Thompson · Accepted Answer · 2014-05-23 17:27:14Z

1

Repeat for all three:

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

answered May 23, 2014 at 17:27

Alvin Thompson

5,4784 gold badges28 silver badges43 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Trigger with IF Comparison

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related