Changes between Version 6 and Version 7 of Triggers

Timestamp:

09/04/25 03:41:03 (44 hours ago)

Author:

222004

Comment:

--

Triggers

@@ -48 +48 @@
 RETURNS TRIGGER
 LANGUAGE plpgsql AS $$
-DECLARE v_inc INT;
+DECLARE value INT;
 BEGIN
   IF NEW.status = 'завршено' THEN
     IF EXISTS (SELECT 1 FROM synergymed.clubcard WHERE id = NEW.client_id) THEN
-      v_inc := GREATEST(0, FLOOR(COALESCE(NEW.amount,0) / 10));
+      value := GREATEST(0, FLOOR(COALESCE(NEW.amount,0) / 10));
       UPDATE synergymed.clubcard
-      SET points = points + v_inc
+      SET points = points + value
       WHERE id = NEW.client_id;
     END IF;
@@ -64 +64 @@
 DROP TRIGGER IF EXISTS trg_payment_loyalty ON synergymed.payment;
 CREATE TRIGGER trg_payment_loyalty
-AFTER INSERT OR UPDATE OF status, amount ON synergymed.payment
+AFTER INSERT OR UPDATE OF status ON synergymed.payment
 FOR EACH ROW EXECUTE FUNCTION synergymed.fn_payment_loyalty_after();
 
@@ -83 +83 @@
 LANGUAGE plpgsql AS $$
 BEGIN
-  IF NEW.verification_status = 'верифицирано' THEN
-    UPDATE synergymed.client SET is_verified = TRUE WHERE id = NEW.client_id;
-  ELSIF NEW.verification_status = 'одбиено' THEN
-    UPDATE synergymed.client SET is_verified = FALSE WHERE id = NEW.client_id;
+  IF lower(NEW.verification_status) = 'верифицирано' THEN
+    UPDATE synergymed.client AS c
+       SET is_verified = TRUE
+     WHERE c.user_id = NEW.client_id;
+  ELSIF lower(NEW.verification_status) = 'одбиено' THEN
+    UPDATE synergymed.client AS c
+       SET is_verified = FALSE
+     WHERE c.user_id = NEW.client_id;
   END IF;
   RETURN NEW;
@@ -94 +98 @@
 DROP TRIGGER IF EXISTS trg_sensitiveclientdata_affect_client ON synergymed.sensitiveclientdata;
 CREATE TRIGGER trg_sensitiveclientdata_affect_client
-AFTER INSERT OR UPDATE OF verification_status ON synergymed.sensitiveclientdata
-FOR EACH ROW EXECUTE FUNCTION synergymed.fn_sensitiveclientdata_affect_client_after();
+AFTER INSERT OR UPDATE OF verification_status
+ON synergymed.sensitiveclientdata
+FOR EACH ROW
+EXECUTE FUNCTION synergymed.fn_sensitiveclientdata_affect_client_after();
 
 }}}
@@ -136 +142 @@
 FOR EACH ROW EXECUTE FUNCTION synergymed.fn_medicineinteraction_order_before();
 
-CREATE UNIQUE INDEX IF NOT EXISTS ux_mi_pair
-ON synergymed.medicineinteraction (medicine_id_1, medicine_id_2);
-
 }}}
 
@@ -154 +157 @@
 RETURNS TRIGGER
 LANGUAGE plpgsql AS $$
-DECLARE 
-  v_pharmacy_id INT;
-BEGIN
-  SELECT ph.company_id INTO v_pharmacy_id
-  FROM synergymed.inventory i
-  JOIN synergymed.facility f ON f.id = i.facility_id
-  JOIN synergymed.pharmacy ph ON ph.company_id = f.company_id
-  WHERE i.id = COALESCE(NEW.inventory_id, OLD.inventory_id)
+DECLARE
+  target_pharmacy_id INT;
+  target_branded_medicine_id INT;
+  source_inventory_id INT;
+BEGIN
+  target_branded_medicine_id := COALESCE(NEW.branded_medicine_id, OLD.branded_medicine_id);
+  source_inventory_id := COALESCE(NEW.inventory_id, OLD.inventory_id);
+
+  SELECT ph.company_id
+    INTO target_pharmacy_id
+  FROM synergymed.inventory AS i
+  JOIN synergymed.facility AS f ON f.id = i.facility_id
+  JOIN synergymed.pharmacy AS ph ON ph.company_id = f.company_id
+  WHERE i.id = source_inventory_id
   LIMIT 1;
 
-  IF v_pharmacy_id IS NOT NULL THEN
-    INSERT INTO synergymed.pharmacy_catalog(pharmacy_id, branded_medicine_id)
-    VALUES (v_pharmacy_id, COALESCE(NEW.branded_medicine_id, OLD.branded_medicine_id))
+  IF target_pharmacy_id IS NOT NULL THEN
+    INSERT INTO synergymed.pharmacy_catalog (pharmacy_id, branded_medicine_id)
+    VALUES (target_pharmacy_id, target_branded_medicine_id)
     ON CONFLICT DO NOTHING;
   END IF;
@@ -177 +186 @@
 CREATE TRIGGER trg_inventory_autocatalog_ins
 AFTER INSERT ON synergymed.inventory_brandedmedicine
-FOR EACH ROW EXECUTE FUNCTION synergymed.fn_inventory_autocatalog_after();
+FOR EACH ROW
+WHEN (NEW.quantity > 0)
+EXECUTE FUNCTION synergymed.fn_inventory_autocatalog_after();
 
 DROP TRIGGER IF EXISTS trg_inventory_autocatalog_upd ON synergymed.inventory_brandedmedicine;
 CREATE TRIGGER trg_inventory_autocatalog_upd
 AFTER UPDATE OF quantity ON synergymed.inventory_brandedmedicine
-FOR EACH ROW EXECUTE FUNCTION synergymed.fn_inventory_autocatalog_after();
+FOR EACH ROW
+WHEN (NEW.quantity > 0 AND (OLD.quantity IS DISTINCT FROM NEW.quantity))
+EXECUTE FUNCTION synergymed.fn_inventory_autocatalog_after();
 
 }}}