Automatic update of product availability after an order

Description

This trigger automatically decreases the available quantity of a product when the product is included in a customer order.

The sells.quantity attribute represents the quantity of a product currently available in a store. When an order is placed, the quantity ordered must be deducted from the available quantity.

This ensures that product availability is automatically synchronized with customer orders.

Tables involved

• Product
• Order
• includes
• sells

Type of trigger

• AFTER INSERT
• AFTER UPDATE

Activated on the includes table.

SQL Code

CREATE OR REPLACE FUNCTION update_product_availability()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    -- When a product is newly added to an order,
    -- decrease the available quantity.
    IF TG_OP = 'INSERT' THEN

        UPDATE sells
        SET quantity = quantity - (
            SELECT o.quantity
            FROM "order" o
            WHERE o.order_num = NEW.order_num
        )
        WHERE code = NEW.code;

    -- If the product/order entry is modified,
    -- restore the old quantity and subtract the new quantity.
    ELSIF TG_OP = 'UPDATE' THEN

        UPDATE sells
        SET quantity = quantity
            + (
                SELECT o.quantity
                FROM "order" o
                WHERE o.order_num = OLD.order_num
            )
            - (
                SELECT o.quantity
                FROM "order" o
                WHERE o.order_num = NEW.order_num
            )
        WHERE code = NEW.code;

    END IF;

    RETURN NULL;
END;
$$;


CREATE TRIGGER trg_update_product_availability
AFTER INSERT OR UPDATE
ON includes
FOR EACH ROW
EXECUTE FUNCTION update_product_availability();

Logic explanation

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

1. Identifies the ordered product using NEW.code. 2. Identifies the order using NEW.order_num. 3. Retrieves the ordered quantity from ORDER. 4. Finds the corresponding product in sells. 5. Decreases the available quantity by the ordered amount.

For an update, the trigger compensates for the old quantity before applying the new quantity.

For example:

Initial product quantity:       20
Customer orders:                 3

New available quantity:         20 - 3 = 17

Reason for trigger

This trigger is useful because:

• Product availability must automatically reflect customer orders.
• It prevents the application from having to manually update inventory.
• It reduces the possibility of inconsistent inventory data.
• It centralizes inventory-related business logic inside the database.
• It ensures that every order affects product availability consistently.