Ensuring order availability with PL/SQL trigger

PL/SQL Trigger: Exercise-10 with Solution

Write a code in PL/SQL to create a trigger that validates the availability of items before allowing an order to be placed, considering stock levels and pending orders.

Sample Solution:

PL/SQL Code:

-- Create Products table
CREATE TABLE Products (
product_id NUMBER PRIMARY KEY,
product_nameVARCHAR2(100),
stock_quantity NUMBER
);

-- Create Orders table
CREATE TABLE Orders (
order_id NUMBER PRIMARY KEY,
product_id NUMBER,
order_quantity NUMBER
);

-- Create Trigger to validate availability before placing an order
CREATE OR REPLACE TRIGGER Validate_Order_Availability
BEFORE INSERT ON Orders
FOR EACH ROW
DECLARE
v_current_stock NUMBER;
v_pending_orders NUMBER;
BEGIN
    -- Get current stock for the product
    SELECT stock_quantity INTO v_current_stock
    FROM Products
    WHERE product_id= :NEW.product_id;

    -- Get total quantity of pending orders for the product
    SELECT NVL(SUM(order_quantity), 0) INTO v_pending_orders
    FROM Orders
    WHERE product_id= :NEW.product_id;

    -- Calculate total available quantity (stock - pending orders)
    IF v_current_stock - v_pending_orders - :NEW.order_quantity< 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock for the order');
    END IF;
END;
/

Let’s see how the trigger functions

PL/SQL Code:

-- Insert sample data into Products table
INSERT INTO Products (product_id, product_name, stock_quantity)
VALUES (1, 'Product A', 100);

-- Attempt to place an order with insufficient stock
INSERT INTO Orders (order_id, product_id, order_quantity)
VALUES (1, 1, 150); 
-- This should fail due to insufficient stock
-- Place an order within available stock
INSERT INTO Orders (order_id, product_id, order_quantity)
VALUES (2, 1, 50); 
-- This should succeed
-- Query the Orders table to see the placed orders
SELECT * FROM Orders;

Flowchart:

Explanation:

The provided PL/SQL code demonstrates the creation and utilization of a PL/SQL trigger in Oracle Database to enhance order management by ensuring the availability of items before permitting the placement of new orders.

The two tables Products and Orders are creates. The Products maintains records of available products, including product_id, product_name, and stock_quantity and the Orders table keeps track of orders, containing order_id, product_id, and order_quantity.

The trigger Validate_Order_Availability operates before an INSERT operation on the Orders table, implying that it executes prior to the insertion of new order records.

Inside the trigger body, PL/SQL logic performs the availability validation. Initially, the trigger retrieves the current stock_quantity for the product associated with the new order from the Products table.

Subsequently, the trigger calculates the total quantity of pending orders for the same product from the Orders table.

Finally, the trigger calculates the total available quantity by subtracting the sum of pending orders and the requested order_quantity from the stock_quantity. If the available quantity becomes negative (indicating insufficient stock), the trigger raises an application error with a custom message.

Previous: Automating running total calculation with PL/SQL trigger.
Next: Automating error notifications with PL/SQL Trigger.