Trigger after insert with nested if conditions pl sql

Question

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

Are you getting errors? If so, what errors? Is something not working? If so, what exactly? You've posted some code but not a reproducible example-- if you post some table definitions, sample data, and expected results, you'd be in a great position (ideally even with a link to a livesql, dbfiddle, etc. page with everything set up). — Justin Cave
– Justin Cave, Commented Nov 18, 2020 at 23:20
Off the top of my head, the initial select is doing a count but is missing the group_id. The next query in your if( total > 1) is missing the into and is looking at the warehouse with the smallest warehouse_id not the warehouse with the smallest warehouse_id that has the product. You don't want to delete from order_items, you want to throw an exception. And if you're going to update inventories you'd want to include a where clause so that you're updating a single row. — Justin Cave
– Justin Cave, Commented Nov 18, 2020 at 23:23
And taking a step back, you're better off having a stored procedure that does the insert into order_items and adjusts the quantity in inventories rather than putting a bunch of logic in a trigger. — Justin Cave
– Justin Cave, Commented Nov 18, 2020 at 23:24

Popeye · Accepted Answer · 2020-11-19 04:43:06Z

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

Collectives™ on Stack Overflow

Trigger after insert with nested if conditions pl sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related