From 349cde397536c1fd61b8a8a69fe0c6709bb21b46 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= <bernd@kivitendo-premium.de>
Date: Mon, 24 Aug 2020 15:39:48 +0200
Subject: [PATCH] =?utf8?q?CVars:=20Eintr=C3=A4ge=20beim=20L=C3=B6schen=20d?=
 =?utf8?q?azugeh=C3=B6riger=20Lieferadressen=20automatisch=20l=C3=B6schen?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 ...ete_cvars_on_trans_deletion_add_shipto.sql | 53 +++++++++++++++++++
 1 file changed, 53 insertions(+)
 create mode 100644 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
index 000000000..ab1d1a4f5
--- /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 EXISTS
+  (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();
-- 
2.20.1