Automating Inventory Management: Using SQL Triggers to Update Product Inventory in Your Online Store

0 0

Scenario:

Suppose you have an online store database with two tables: Orders and Products. You want to automatically update the product inventory when a new order is placed to ensure that the available quantity reflects the purchased items.

Database Tables:

1. Orders Table:
   • OrderID (Primary Key)
   • ProductID (Foreign Key)
   • Quantity
2. Products Table:
   • ProductID (Primary Key)
   • ProductName
   • InventoryQuantity

SQL Trigger: We’ll create an AFTER INSERT trigger on the Orders table to update the Products table’s InventoryQuantity when a new order is placed.

DELIMITER //

CREATE TRIGGER update_inventory_after_order_insert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
    DECLARE ordered_quantity INT;
    SELECT NEW.Quantity INTO ordered_quantity;
    
    -- Update product inventory
    UPDATE Products
    SET InventoryQuantity = InventoryQuantity - ordered_quantity
    WHERE ProductID = NEW.ProductID;
END;
//

DELIMITER ;

Explanation:

• The trigger is named update_inventory_after_order_insert and is set to execute AFTER an INSERT operation on the Orders table.
• FOR EACH ROW indicates that the trigger will execute once for each row affected by the INSERT.
• Inside the trigger, we declare a variable ordered_quantity to store the quantity ordered in the new order.
• We use a SELECT statement to retrieve the ordered quantity from the newly inserted row and store it in ordered_quantity.
• Then, we update the Products table to decrement the InventoryQuantity of the corresponding product by the ordered quantity.

Usage: Now, whenever a new order is inserted into the Orders table, the trigger will automatically update the product’s inventory in the Products table. For example:

-- Insert a new order
INSERT INTO Orders (ProductID, Quantity) VALUES (1, 5);

After executing this INSERT statement, the trigger will reduce the inventory of the product with ProductID 1 by 5 units in the Products table.

This scenario demonstrates how an SQL trigger can automate actions and help maintain data consistency in a real-world development context.

About Post Author

Vicky Chhetri

developer@vickychhetri.com

https://vickychhetri.com/

Happy

0 0 %

Sad

0 0 %

Excited

0 0 %

Sleepy

0 0 %

Angry

0 0 %

Surprise

0 0 %

About Author

Vicky Chhetri

See author's posts