Changes between Initial Version and Version 1 of Trigger3

Timestamp:

08/21/26 07:44:16 (27 hours ago)

Author:

235018

Comment:

--

Trigger3

@@ +1 @@
+= 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
+{{{#!sql
+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.