Changes between Version 3 and Version 4 of App

Timestamp:

09/30/25 10:53:26 (4 weeks ago)

Author:

211561

Comment:

--

App

@@ -37 +37 @@
 = Тригери
 
+== Негативни/нулти количини и цени во {{{ORDER_BATCHES}}}
+
+Кога се извршува {{{INSERT}}} или {{{UPDATE}}} на {{{ORDER_BATCHES}}}, осигурај {{{quantity > 0}}} и {{{price_per_unit >= 0}}}; пресметај {{{total_price = quantity * price_per_unit}}} и постави {{{timestamps}}}.
+
+{{{
+CREATE OR REPLACE FUNCTION trg_order_batches_validate() RETURNS trigger AS $
+BEGIN
+  IF NEW.quantity IS NULL OR NEW.quantity <= 0 THEN
+    RAISE EXCEPTION 'quantity must be > 0';
+  END IF;
+  IF NEW.price_per_unit IS NULL OR NEW.price_per_unit < 0 THEN
+    RAISE EXCEPTION 'price_per_unit must be >= 0';
+  END IF;
+  NEW.total_price := NEW.quantity * NEW.price_per_unit;
+  NEW.updated_at := NOW();
+  IF TG_OP = 'INSERT' THEN
+    NEW.created_at := NOW();
+  END IF;
+  RETURN NEW;
+END;
+$ LANGUAGE plpgsql;
+
+CREATE TRIGGER order_batches_validate
+BEFORE INSERT OR UPDATE ON order_batches
+FOR EACH ROW EXECUTE FUNCTION trg_order_batches_validate();
+}}}
+
+== Нарачки со истечени серии
+
+Пред {{{INSERT}}} или {{{UPDATE}}} на {{{ORDER_BATCHES}}}, провери дали повиканата {{{BATCH}}} не е истечена според датумот на нарачката ({{{orders.date}}}). Се фрла {{{exception}}} ако {{{expiration_date < orders.date}}}.
+
+{{{
+CREATE OR REPLACE FUNCTION trg_order_batches_no_expired() RETURNS trigger AS $
+DECLARE
+  ord_date DATE;
+  batch_exp DATE;
+BEGIN
+  SELECT date INTO ord_date FROM orders WHERE id = NEW.order_id;
+  SELECT expiration_date INTO batch_exp FROM batches WHERE id = NEW.batch_id;
+  IF ord_date IS NULL OR batch_exp IS NULL THEN
+    RAISE EXCEPTION 'order or batch not found';
+  END IF;
+  IF batch_exp < ord_date THEN
+    RAISE EXCEPTION 'cannot add expired batch to order (batch % expired %)', NEW.batch_id, batch_exp;
+  END IF;
+  RETURN NEW;
+END;
+$ LANGUAGE plpgsql;
+
+CREATE TRIGGER order_batches_no_expired
+BEFORE INSERT OR UPDATE ON order_batches
+FOR EACH ROW EXECUTE FUNCTION trg_order_batches_no_expired();
+}}}
+
+== Бришење на {{{PRODUCERS/PRODUCTS/BATCHES}}} кои се реферирани
+
+Забрането бришење на:
+
+- {{{producer}}} ако постојат {{{products}}}
+- {{{product}}} ако постојат {{{batches}}}
+- {{{batch}}} ако е искористен во {{{order_batches}}}.
+
+{{{
+CREATE OR REPLACE FUNCTION trg_prevent_fk_deletes() RETURNS trigger AS $
+BEGIN
+  IF TG_TABLE_NAME = 'producers' THEN
+    IF EXISTS (SELECT 1 FROM products WHERE producer_id = OLD.id) THEN
+      RAISE EXCEPTION 'cannot delete producer with products';
+    END IF;
+  ELSIF TG_TABLE_NAME = 'products' THEN
+    IF EXISTS (SELECT 1 FROM batches WHERE product_id = OLD.id) THEN
+      RAISE EXCEPTION 'cannot delete product with batches';
+    END IF;
+  ELSIF TG_TABLE_NAME = 'batches' THEN
+    IF EXISTS (SELECT 1 FROM order_batches WHERE batch_id = OLD.id) THEN
+      RAISE EXCEPTION 'cannot delete batch referenced in orders';
+    END IF;
+  END IF;
+  RETURN OLD;
+END;
+$ LANGUAGE plpgsql;
+
+CREATE TRIGGER prevent_delete_producers
+BEFORE DELETE ON producers
+FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();
+
+CREATE TRIGGER prevent_delete_products
+BEFORE DELETE ON products
+FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();
+
+CREATE TRIGGER prevent_delete_batches
+BEFORE DELETE ON batches
+FOR EACH ROW EXECUTE FUNCTION trg_prevent_fk_deletes();
+}}}
+
+== Автоматско ажурирање на {{{ORDER.status}}} според плаќања
+
+Кога {{{PAYMENT}}} се означува како {{{paid}}} ({{{payment_status = 'paid'}}} и {{{payment_date}}} не е {{{NULL}}}), провери {{{SUM(payments.amount) >= orders.total_amount}}}, а потоа постави {{{orders.status = 'paid'}}}.
+
+{{{
+CREATE OR REPLACE FUNCTION trg_payments_update_order_status() RETURNS trigger AS $
+DECLARE
+  paid_sum NUMERIC;
+  ord_total NUMERIC;
+BEGIN
+  IF TG_OP = 'DELETE' THEN
+    RETURN OLD;
+  END IF;
+
+  SELECT COALESCE(SUM(amount),0) INTO paid_sum FROM payments WHERE order_id = NEW.order_id AND payment_status = 'paid';
+  SELECT COALESCE(total_amount,0) INTO ord_total FROM orders WHERE id = NEW.order_id;
+
+  IF paid_sum >= ord_total AND ord_total > 0 THEN
+    UPDATE orders SET status = 'paid' WHERE id = NEW.order_id;
+  END IF;
+  RETURN NEW;
+END;
+$ LANGUAGE plpgsql;
+
+CREATE TRIGGER payments_update_order_status
+AFTER INSERT OR UPDATE ON payments
+FOR EACH ROW EXECUTE FUNCTION trg_payments_update_order_status();
+}}}
+
+== Известувања за промена на статус на нарачка
+
+Известување кога нарачката го променила статусот ({{{ORDER.status}}}) — испраќа {{{RAISE NOTICE}}} со ордедена порака
+
+{{{
+CREATE OR REPLACE FUNCTION trg_order_status_change_fun()
+RETURNS trigger AS $
+BEGIN
+  IF TG_OP = 'UPDATE' AND NEW.status IS DISTINCT FROM OLD.status THEN
+    RAISE NOTICE 'Order % changed it's status: % → %', NEW.id, OLD.status, NEW.status;
+    IF NEW.status = 'shipped' OR NEW.status = 'delivered' THEN
+      RAISE NOTICE 'Good news! Order % is %!', NEW.id, NEW.status;
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS trg_order_status_change ON orders;
+CREATE TRIGGER trg_order_status_change
+AFTER UPDATE ON orders
+FOR EACH ROW
+WHEN (OLD.status IS DISTINCT FROM NEW.status)
+EXECUTE FUNCTION trg_order_status_change_fun();
+}}}
+
 = Трансакции