How to use if statement in a trigger using SQL

Question

I have the following tables in my IBMDB2 database:

Produt(PID....etc) CombosAndPromotions(CP_ID, CP_Price...etc) PriceSize(size, price) Sales(PID, CP_ID, size, quantity, Sales_Price)

I want to create a trigger to automatically calculate the value of the Sales_Price. Basically

1-if the PID is null then the value is the price of the CP_ID * quantity.

2- if the CP_ID is null then the value of the Sales_Price is the price of the size of the Product bought(the price depends on the size not on the PID) * the quantity.

3-if Both of them are not null then the value of the Sales_Price is equal to the sum of both of the previous summations. I have tried the following SQL code, but it's not working.

create trigger calc_Price
after insert on sales
for every row mode db2sql 

if CP_ID is null
update table Sales
set Price = (PriceSize.price)*Quantity
where Sales.size = PriceSize.size

if PID is null
update table Sales
set price = CombosAndPromotions.CP_price * quantity
where CombosAndPromotions.CP_ID = Sales.CP_ID

can someone assist me on how to correct it since I don't have experience with sql. thank you.

Triggers are very database specific.. which database are you using? — thebjorn
– thebjorn, Commented Aug 18, 2014 at 16:00
Then you need the then keyword and end if; in your if statements (certain other databases like sql server and sybase don't require this). You also need a begin and end around the trigger body (possibly with atomic after begin..) — thebjorn
– thebjorn, Commented Aug 18, 2014 at 16:05
@thebjorn can you please give me a simple demonstration on how can i use these keys, i will really appreciate your help — Alladin
– Alladin, Commented Aug 18, 2014 at 16:08

thebjorn · Accepted Answer · 2014-08-18 16:20:35Z

1

It's been a very long time since I used db/2, but something like this might work:

create trigger calc_Price
after insert on sales
referencing new as n
for every row mode db2sql 
begin atomic
    declare cpprice int;

    if n.CP_ID is null then
        update table Sales
            set Price = (n.price) * n.Quantity
        where PID = n.PID
    end if;

    if n.PID is null then
        set cpprice = (select CP_price from CombosAndPromotions where CP_ID = n.CP_ID)
        update table Sales
           set price = cpprice * n.quantity
        where PID = n.PID
    end if;
end

The idea is to reference the newly inserted row (here as n) and use the key from that row to update the table. (this might work better as a before insert..?)

edited Aug 18, 2014 at 16:20

answered Aug 18, 2014 at 16:15

thebjorn

27.6k12 gold badges107 silver badges152 bronze badges

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

2 Comments

Alladin Over a year ago

Thanks a lot @thebjorn, although the code is not working but it will help me fix the errors and correct everything. thank you

thebjorn Over a year ago

Please feel free to edit my answer with the correct code once you get it working :-)

Collectives™ on Stack Overflow

How to use if statement in a trigger using SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related