Prevention of ordering unavailable products

Description

This trigger prevents customers from placing an order for a product when the requested quantity is greater than the quantity currently available in the store.

The trigger checks the available quantity before a product is added to an order. If there is not enough stock, the operation is rejected and an error message is returned.

This guarantees that the database cannot contain orders for products that are not available in the required quantity.

Tables involved

• Product
• Order
• includes
• sells

Type of trigger

• BEFORE INSERT
• BEFORE UPDATE

Activated on the includes table.

SQL Code

CREATE OR REPLACE FUNCTION check_product_availability()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_available_quantity INT;
    v_order_quantity INT;
BEGIN
    -- Get the quantity requested by the order
    SELECT quantity
    INTO v_order_quantity
    FROM "order"
    WHERE order_num = NEW.order_num;

    -- Get the currently available quantity of the product
    SELECT quantity
    INTO v_available_quantity
    FROM sells
    WHERE code = NEW.code;

    -- Check whether the product exists
    IF v_available_quantity IS NULL THEN
        RAISE EXCEPTION
            'Product % is not available in any store.',
            NEW.code;
    END IF;

    -- Check whether there is enough stock
    IF v_order_quantity > v_available_quantity THEN
        RAISE EXCEPTION
            'Insufficient stock for product %. Available: %, requested: %.',
            NEW.code,
            v_available_quantity,
            v_order_quantity;
    END IF;

    RETURN NEW;
END;
$$;


CREATE TRIGGER trg_check_product_availability
BEFORE INSERT OR UPDATE
ON includes
FOR EACH ROW
EXECUTE FUNCTION check_product_availability();

Logic explanation

Before a product is added to an order, the trigger:

1. Identifies the order using NEW.order_num. 2. Retrieves the requested quantity from the ORDER table. 3. Identifies the product using NEW.code. 4. Retrieves the available quantity from sells. 5. Compares the requested quantity with the available quantity. 6. If sufficient stock exists, the operation continues. 7. If insufficient stock exists, the trigger raises an exception and prevents the order from being created.

COALESCE is used so that a store with no reviews receives a rating of 0 instead of NULL.

Reason for trigger

This trigger is useful because:

• It prevents customers from ordering unavailable products.
• It protects the integrity of inventory data.
• The validation is performed directly by the database.
• The application cannot accidentally bypass the stock check.
• It provides an immediate error when there is insufficient stock.