From bd389ba9a00c3b56958b9c541fa0ca0e6ebcd6bf Mon Sep 17 00:00:00 2001
From: Moritz Bunkus <m.bunkus@linet-services.de>
Date: Mon, 6 Jan 2014 16:27:50 +0100
Subject: [PATCH] =?utf8?q?Wiedervorlagen=20l=C3=B6schen,=20wenn=20dazugeh?=
 =?utf8?q?=C3=B6rige=20Belege=20gel=C3=B6scht=20werden?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

Behebt #2310.
---
 ...ollow_ups_when_order_is_deleted_closed.sql | 76 +++++++++++++++++++
 1 file changed, 76 insertions(+)
 create mode 100644 sql/Pg-upgrade2/delete_close_follow_ups_when_order_is_deleted_closed.sql

diff --git a/sql/Pg-upgrade2/delete_close_follow_ups_when_order_is_deleted_closed.sql b/sql/Pg-upgrade2/delete_close_follow_ups_when_order_is_deleted_closed.sql
new file mode 100644
index 000000000..ec96729f9
--- /dev/null
+++ b/sql/Pg-upgrade2/delete_close_follow_ups_when_order_is_deleted_closed.sql
@@ -0,0 +1,76 @@
+-- @tag: delete_close_follow_ups_when_order_is_deleted_closed
+-- @description: Wiedervorlagen lößchen/schließen, wenn dazugehörige Belege gelöscht/geschlossen werden
+-- @depends: release_3_0_0
+
+ALTER TABLE follow_up_links DROP CONSTRAINT follow_up_links_follow_up_id_fkey;
+ALTER TABLE follow_up_links ADD FOREIGN KEY (follow_up_id) REFERENCES follow_ups (id) ON DELETE CASCADE;
+
+CREATE OR REPLACE FUNCTION follow_up_delete_notes_trigger()
+RETURNS TRIGGER AS $$
+  BEGIN
+    DELETE FROM notes
+    WHERE (trans_id     = OLD.id)
+      AND (trans_module = 'fu');
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION follow_up_delete_when_oe_is_deleted_trigger()
+RETURNS TRIGGER AS $$
+  BEGIN
+    DELETE FROM follow_ups
+    WHERE id IN (
+      SELECT follow_up_id
+      FROM follow_up_links
+      WHERE (trans_id   = OLD.id)
+        AND (trans_type IN ('sales_quotation',   'sales_order',    'sales_delivery_order',    'sales_invoice',
+                            'request_quotation', 'purchase_order', 'purchase_delivery_order', 'purchase_invoice'))
+    );
+
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION follow_up_delete_when_customer_vendor_is_deleted_trigger()
+RETURNS TRIGGER AS $$
+  BEGIN
+    DELETE FROM follow_ups
+    WHERE id IN (
+      SELECT follow_up_id
+      FROM follow_up_links
+      WHERE (trans_id   = OLD.id)
+        AND (trans_type IN ('customer', 'vendor'))
+    );
+
+    DELETE FROM notes
+    WHERE (trans_id     = OLD.id)
+      AND (trans_module = 'ct');
+
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+-- ============================================================
+
+DROP TRIGGER IF EXISTS follow_up_delete_notes ON follow_ups;
+
+CREATE TRIGGER follow_up_delete_notes
+AFTER DELETE ON follow_ups
+FOR EACH ROW  EXECUTE PROCEDURE follow_up_delete_notes_trigger();
+
+DROP TRIGGER IF EXISTS oe_before_delete_clear_follow_ups ON oe;
+
+CREATE TRIGGER oe_before_delete_clear_follow_ups
+BEFORE DELETE ON oe
+FOR EACH ROW  EXECUTE PROCEDURE follow_up_delete_when_oe_is_deleted_trigger();
+
+DROP TRIGGER IF EXISTS customer_before_delete_clear_follow_ups ON customer;
+DROP TRIGGER IF EXISTS vendor_before_delete_clear_follow_ups   ON vendor;
+
+CREATE TRIGGER customer_before_delete_clear_follow_ups
+AFTER DELETE ON customer
+FOR EACH ROW  EXECUTE PROCEDURE follow_up_delete_when_customer_vendor_is_deleted_trigger();
+
+CREATE TRIGGER vendor_before_delete_clear_follow_ups
+AFTER DELETE ON vendor
+FOR EACH ROW  EXECUTE PROCEDURE follow_up_delete_when_customer_vendor_is_deleted_trigger();
-- 
2.20.1