Merge branch 'b-3.6.1' of ../kivitendo-erp_20220811

[kivitendo-erp.git] / sql / Pg-upgrade2 / delete_cvars_on_trans_deletion_add_shipto.sql

diff --git a/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql
new file mode 100644 (file)
index 0000000..d2f887b
--- /dev/null
+++ b/sql/Pg-upgrade2/delete_cvars_on_trans_deletion_add_shipto.sql
@@ -0,0 +1,53 @@
+-- @tag: delete_cvars_on_trans_deletion_add_shipto
+-- @description: Löschen von benutzerdefinierten Variablen via Triggerfunktionen auch für shipto
+-- @depends: delete_cvars_on_trans_deletion delete_cvars_on_trans_deletion_fix1
+
+-- 1.6 Alle benutzerdefinierten Variablen löschen, für die es keine
+-- Einträge in shipto mehr gibt.
+DELETE FROM custom_variables WHERE id IN
+  (SELECT cv.id FROM custom_variables cv LEFT JOIN custom_variable_configs cvc ON (cv.config_id = cvc.id)
+   WHERE module LIKE 'ShipTo'
+     AND NOT EXISTS (SELECT shipto_id FROM shipto WHERE shipto_id = cv.trans_id));
+
+
+-- 2.2. Nun die Funktionen, die als Trigger aufgerufen wird und die
+-- entscheidet, wie genau zu löschen ist:
+CREATE OR REPLACE FUNCTION delete_custom_variables_trigger()
+RETURNS TRIGGER AS $$
+  BEGIN
+    IF (TG_TABLE_NAME IN ('orderitems', 'delivery_order_items', 'invoice')) THEN
+      PERFORM delete_custom_variables_with_sub_module('IC', TG_TABLE_NAME, old.id);
+    END IF;
+
+    IF (TG_TABLE_NAME = 'parts') THEN
+      PERFORM delete_custom_variables_with_sub_module('IC', '', old.id);
+    END IF;
+
+    IF (TG_TABLE_NAME IN ('customer', 'vendor')) THEN
+      PERFORM delete_custom_variables_with_sub_module('CT', '', old.id);
+    END IF;
+
+    IF (TG_TABLE_NAME = 'contacts') THEN
+      PERFORM delete_custom_variables_with_sub_module('Contacts', '', old.cp_id);
+    END IF;
+
+    IF (TG_TABLE_NAME = 'project') THEN
+      PERFORM delete_custom_variables_with_sub_module('Projects', '', old.id);
+    END IF;
+
+    IF (TG_TABLE_NAME = 'shipto') THEN
+      PERFORM delete_custom_variables_with_sub_module('ShipTo', '', old.shipto_id);
+    END IF;
+
+    RETURN old;
+  END;
+$$ LANGUAGE plpgsql;
+
+-- 3. Die eigentlichen Trigger erstellen:
+
+-- 3.9. shipto
+DROP TRIGGER IF EXISTS shipto_delete_custom_variables_after_deletion ON shipto;
+
+CREATE TRIGGER shipto_delete_custom_variables_after_deletion
+AFTER DELETE ON shipto
+FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();