From 18448fcab0660724c680264f4b966930e784a8b8 Mon Sep 17 00:00:00 2001
From: Niclas Zimmermann <niclas@kivitendo-premium.de>
Date: Tue, 7 Jan 2014 17:49:31 +0100
Subject: [PATCH] =?utf8?q?Noch=20mehr=20Trigger=20f=C3=BCr=20Benutzervaria?=
 =?utf8?q?blen?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Beim Löschen von Projekten, Ansprechpersonen, Kunden, Lieferanten,
Waren, Erzeugnissen und Dienstleistungen werden jetzt auch die
benutzerdefinierten Variablen gelöscht.
---
 .../custom_variables_delete_via_trigger_2.pl  | 109 ++++++++++++++++++
 1 file changed, 109 insertions(+)
 create mode 100644 sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl

diff --git a/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl b/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl
new file mode 100644
index 000000000..d97493d4f
--- /dev/null
+++ b/sql/Pg-upgrade2/custom_variables_delete_via_trigger_2.pl
@@ -0,0 +1,109 @@
+# @tag: custom_variables_delete_via_trigger_2
+# @description: Benutzerdefinierte Variablen werden nun via Trigger gelöscht (beim Löschen von Kunden, Lieferanten, Kontaktpersonen, Waren, Dienstleistungen, Erzeugnissen und Projekten).
+# @depends: custom_variables_delete_via_trigger
+
+package SL::DBUpgrade2::custom_variables_delete_via_trigger_2;
+
+use utf8;
+use strict;
+
+use parent qw(SL::DBUpgrade2::Base);
+
+sub run {
+  my ($self) = @_;
+
+  my @queries = (
+    #Delete orphaned entries
+    q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id NOT IN (SELECT id FROM customer UNION SELECT id FROM vendor)
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT'|,
+    q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id NOT IN (SELECT id FROM contacts)
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts'|,
+    q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id NOT IN (SELECT id FROM parts)
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC'|,
+    q|DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id NOT IN (SELECT id FROM project)
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects'|,
+
+    #Create trigger
+    q|CREATE OR REPLACE FUNCTION delete_cv_custom_variables_trigger() RETURNS trigger AS $$
+        BEGIN
+          DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id = OLD.id
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'CT';
+
+          RETURN OLD;
+        END;
+      $$ LANGUAGE plpgsql|,
+
+    q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON customer|,
+    q|DROP TRIGGER IF EXISTS delete_cv_custom_variables ON vendor|,
+
+    q|CREATE TRIGGER delete_cv_custom_variables
+      BEFORE DELETE ON customer
+      FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
+    q|CREATE TRIGGER delete_cv_custom_variables
+      BEFORE DELETE ON vendor
+      FOR EACH ROW EXECUTE PROCEDURE delete_cv_custom_variables_trigger()|,
+
+    #Create trigger
+    q|CREATE OR REPLACE FUNCTION delete_contact_custom_variables_trigger() RETURNS trigger AS $$
+        BEGIN
+          DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id = OLD.cp_id
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Contacts';
+
+          RETURN OLD;
+        END;
+      $$ LANGUAGE plpgsql|,
+
+    q|DROP TRIGGER IF EXISTS delete_contact_custom_variables ON contacts|,
+
+    q|CREATE TRIGGER delete_contact_custom_variables
+      BEFORE DELETE ON contacts
+      FOR EACH ROW EXECUTE PROCEDURE delete_contact_custom_variables_trigger()|,
+
+    #Create trigger
+    q|CREATE OR REPLACE FUNCTION delete_part_custom_variables_trigger() RETURNS trigger AS $$
+        BEGIN
+          DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id = OLD.id
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'IC';
+
+          RETURN OLD;
+        END;
+      $$ LANGUAGE plpgsql|,
+
+    q|DROP TRIGGER IF EXISTS delete_part_custom_variables ON parts|,
+
+    q|CREATE TRIGGER delete_part_custom_variables
+      BEFORE DELETE ON parts
+      FOR EACH ROW EXECUTE PROCEDURE delete_part_custom_variables_trigger()|,
+
+    #Create trigger
+    q|CREATE OR REPLACE FUNCTION delete_project_custom_variables_trigger() RETURNS trigger AS $$
+        BEGIN
+          DELETE FROM custom_variables WHERE (sub_module = '' OR sub_module IS NULL)
+                                         AND trans_id = OLD.id
+                                         AND (SELECT module FROM custom_variable_configs WHERE id = config_id) = 'Projects';
+
+          RETURN OLD;
+        END;
+      $$ LANGUAGE plpgsql|,
+
+    q|DROP TRIGGER IF EXISTS delete_project_custom_variables ON project|,
+
+    q|CREATE TRIGGER delete_project_custom_variables
+      BEFORE DELETE ON project
+      FOR EACH ROW EXECUTE PROCEDURE delete_project_custom_variables_trigger()|,
+
+    );
+
+  $self->db_query($_) for @queries;
+
+  return 1;
+}
+
+1;
-- 
2.20.1