Как написать триггер, который бы изменял значения в таблице, на основе значений из другой таблицы?

Question

Имеются три таблицы: Заказы (Orders), Количество товаров в заказе (AmountIn), Количество товаров на складе (AmountOut). Собственно сами таблицы:

Orders:

ID	DateStart	DateEnd	Status	ShopId	WarehouseId
1	...	...	0	1	1

AmountIn:

ID	Amount	GoodsId	OrderId
1	20	1	1

AmountOut:

ID	Amount	GoodsId	WarehouseId
1	100	1	1

Требуется написать триггер, который при изменении статуса заказа, с 0 (не выполнен), на 1 (выполнен) удалял из таблицы AmountOut, количество товаров указанное в таблице AmountIn, учитывая склад, и после удалял весь заказ в таблице AmountIn. А при изменении статуса с 0 на 2 (отменён), просто удалял информацию о заказе в таблице AmountIn.

Например, для описанных таблицы результат будет таковым, что в таблице AmountOut, Amount будет равен 80, запись о заказе в таблице AmountIn будет удалена.

Сейчас имеется вот такой запрос:

CREATE OR REPLACE TRIGGER orders_status_update
AFTER UPDATE
    ON orders
    FOR EACH ROW
BEGIN
   IF (UPDATING (orders.status)) THEN
       BEGIN
          IF (:new.orders.status = 1) THEN
              -- Удаление из таблицы AmountOut отсутствует 
              DELETE FROM AMOUNT_IN a WHERE a.orders_id = :old.orders_id;
          ELSIF (:new.orders.status = 2) THEN
              DELETE FROM AMOUNT_IN a WHERE a.orders_id = :old.orders_id;
          END IF;
       END;
   END IF;
END;

Answer 1

Триггер тут не нужен. Создайте процедуру и используйте её для изменения статуса:

create or replace procedure setOrderStatus (id int, newstatus int) is
    WarehouseId int;
    GoodsId int;
    Amount int;
begin 
    update orders set status = newstatus 
    where id = setOrderStatus.id
    returning WarehouseId into WarehouseId;
    
    delete from amountin a where a.orderid = setOrderStatus.id
    returning Amount, GoodsId  into Amount, GoodsId;
    
    if (newstatus = 1) then
        update amountout set Amount = Amount - setOrderStatus.Amount
        where GoodsId     = setOrderStatus.GoodsId 
        and   WarehouseId = setOrderStatus.WarehouseId;
    end if;
end;
/

Результат (db<>fiddle):

exec setOrderStatus (1, 1)

select * from AmountOut union all
select * from AmountIn
/
        ID     AMOUNT    GOODSID WAREHOUSEID
---------- ---------- ---------- -----------
         1         80          1           1

Ознакомтесь в этой теме, особенно по ссылке на оф.документацию в ответе, когда рекокомендуется и имеет смысл использовать триггер. Если всё там приведённое не убедитeльно, то решение с триггером выглядит так:

create or replace trigger orders_status_update
after update of status on orders for each row
when (old.status = 0 and new.status in (1,2))  
begin
    <<my>> declare
        GoodsId int;
        Amount int;
    begin
        delete from amountin a where a.orderid = :new.id
        returning Amount, GoodsId into my.Amount, my.GoodsId;
        if (:new.status = 1) then
            update amountout set Amount = Amount - my.Amount
            where GoodsId     = my.GoodsId 
            and   WarehouseId = :new.WarehouseId;
        end if;
    end;    
end;
/

БЛОГ НА HUSL

Как написать триггер, который бы изменял значения в таблице, на основе значений из другой таблицы?

Ответы (1 шт):