= 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
{{{#!sql
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:

{{{#!TXT

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.