Building a Real-Time Inventory Management System with SQL and Triggers

Managing inventory in real-time is crucial for businesses to keep track of stock levels and avoid shortages or overstock. In this tutorial, we'll build a beginner-friendly inventory management system using SQL and triggers. Triggers allow automatic reactions to changes like updates and inserts, making them perfect for maintaining accurate stock counts.

We'll start by creating two main tables: one for the products and their stock, and another to record inventory transactions like sales or restocking. Then, we'll use a trigger to update the product stock automatically when a new transaction is recorded.

Let's dive into the SQL code!

-- Create a table to store products and their stock
CREATE TABLE Products (
  ProductID INT PRIMARY KEY,
  ProductName VARCHAR(100),
  Stock INT DEFAULT 0
);

-- Create a table to record inventory transactions
-- Quantity is positive for stock added, negative for stock sold
CREATE TABLE InventoryTransactions (
  TransactionID INT PRIMARY KEY AUTO_INCREMENT,
  ProductID INT,
  Quantity INT,
  TransactionDate DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Now we have a `Products` table holding each product and their current `Stock`, and an `InventoryTransactions` table to log every stock movement. Next, we'll create a trigger that updates the `Stock` in the `Products` table whenever a new transaction is inserted.

-- Trigger to update product stock after inserting a transaction
DELIMITER //
CREATE TRIGGER update_stock_after_transaction
AFTER INSERT ON InventoryTransactions
FOR EACH ROW
BEGIN
  UPDATE Products
  SET Stock = Stock + NEW.Quantity
  WHERE ProductID = NEW.ProductID;
END;//
DELIMITER ;

This trigger listens for new rows inserted into `InventoryTransactions`, then adjusts the corresponding product's stock by adding the new transaction's quantity. Positive quantities add stock (e.g., restocking), and negative quantities reduce stock (e.g., sales).

Let's add some sample products and transactions to see the system in action.

-- Insert sample products
INSERT INTO Products (ProductID, ProductName, Stock) VALUES
(1, 'Keyboard', 50),
(2, 'Mouse', 100);

-- Add a transaction for selling 3 keyboards
INSERT INTO InventoryTransactions (ProductID, Quantity) VALUES (1, -3);

-- Add a transaction for restocking 20 mice
INSERT INTO InventoryTransactions (ProductID, Quantity) VALUES (2, 20);

-- Check updated stock levels
SELECT * FROM Products;

After running these commands, the stock for the Keyboard will decrease by 3, and the stock for the Mouse will increase by 20 automatically. This approach keeps your inventory data accurate in real-time without manual stock adjustments.

You can extend this solution by adding more features like tracking users who make transactions, handling different transaction types, or adding alerts when stock is low. But this basic system provides a solid foundation for real-time inventory management using SQL and triggers.

Happy coding and managing your inventory efficiently!