Changes between Initial Version and Version 1 of Trigger2

Timestamp:

08/21/26 07:38:49 (27 hours ago)

Author:

235018

Comment:

--

Trigger2

@@ +1 @@
+= 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.