sql - 如何使用 SQL 在触发器中使用 if 语句

Question

我的 IBMDB2 数据库中有以下表格:

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

我想创建一个触发器来自动计算 Sales_Price 的值。基本上

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

有人可以帮助我如何更正它，因为我没有使用 sql 的经验。谢谢。

Answer 1

我已经有很长时间没有使用 db/2 了，但是像这样的东西可能会起作用:

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

想法是引用新插入的行(此处为 n)并使用该行中的键来更新表。 (这可能会更好地作为插入前...？)