CODEWITHSUNDEEP
sqloracle-databaseplsql
sqlovers
sqlovers

Reputation: 67

Trigger after insert with nested if conditions pl sql

I want to create a trigger after there's an insert in 'order_items' table. The trigger is to minus the quantity of that product id in 'inventories' table by the quantity being insert to 'order_items' table. If the result of quantity after being subtracted is negative, then the quantity of 'inventories' will be changed back to the old quantity and the data in 'order_items' will be deleted (the insertion is failed). If the product id is in many warehouses, then just minus the quantity with the smallest warehouse_id

Example #1: 
Product ID '101' is available in Warehouse ID '1','2','3' 
So choose the product id '101' in warehouse id '1' (smallest) 
Product ID '101' Quantity in Inventories = 10 
Product ID '101' Quantity Order_Items = 15 
So 10-15=-5 (negative) 
So... 
Product ID '101' Quantity in Inventories = 10 (back to old data) 
Delete '101' datas from Order_Items


Example #2: 
Product ID '102' Quantity in Inventories = 20 
Product ID '102' Quantity Order_Items = 8 
So 20-8=12 
So... 
Product ID '102' Quantity in Inventories = 12

This is my code so far, any ideas? Thanks!

CREATE OR REPLACE TRIGGER update_qty
AFTER INSERT
ON order_items
FOR EACH ROW 
DECLARE
qty_diff number;
qty_in number;
total number;
BEGIN
    select quantity,count(product_id) into qty_in,total
    from inventories
    where product_id=:new.product_id;
    if(total>1) then
    select product_id from inventories where warehouse_id=(select min(warehouse_id) from inventories);
    else
    qty_diff:=qty_in-:new.quantity;
    if(qty_diff<0) then
    i.quantity:=:old.quantity;
    delete from order_items where product_id=:new.product_id;
    else
    update inventories
    set quantity=qty_diff;
    end if;
    end if;
END;
/
BEGIN
insert into order_items(order_id,item_id,product_id,quantity,unit_price) values(12345,12345,12345,100,200);
END;
/

Upvotes: 0

Views: 100

Answers (1)

Popeye
Popeye

Reputation: 35910

It is better to apply this logic at the time of insertion(using procedure in DB or application-level logic). but if you are doing it for learning purposes and if I followed you correctly, You can use the following simple update in the trigger.

CREATE OR REPLACE TRIGGER UPDATE_QTY AFTER
    INSERT ON ORDER_ITEMS
    FOR EACH ROW
BEGIN
    -- check and update the INVENTORIES table 
    -- lowest warehouse id with the product will be selected
    -- new quantity or more must be available in the INVENTORIES table 
    UPDATE INVENTORIES I
       SET
        QUANTITY = QUANTITY - :NEW.QUANTITY
     WHERE QUANTITY >= :NEW.QUANTITY
       AND PRODUCT_ID = :NEW.PRODUCT_ID
       AND NOT EXISTS (
        SELECT 1
          FROM INVENTORIES II
         WHERE II.PRODUCT_ID = :NEW.PRODUCT_ID
           AND II.WAREHOUSE_ID < I.WAREHOUSE_ID
    );

    -- if no record is selected means product is not available in any of the lowest warehouse 
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(
            -20000,
            'product not available in inventory'
        );
    END IF;
END;
/

Upvotes: 1

Related Questions