X-Git-Url: http://wagnertech.de/git?a=blobdiff_plain;ds=sidebyside;f=sql%2FPg-upgrade2%2Frecord_links_post_delete_triggers_gl.sql;fp=sql%2FPg-upgrade2%2Frecord_links_post_delete_triggers_gl.sql;h=595c8ca9aa56097f9aa5d3782912c7d9a48f8058;hb=53593baa211863fbf66540cf1bcc36c8fb37257f;hp=0000000000000000000000000000000000000000;hpb=deb4d2dbb676d7d6f69dfe7815d6e0cb09bd4a44;p=kivitendo-erp.git

diff --git a/sql/Pg-upgrade2/record_links_post_delete_triggers_gl.sql b/sql/Pg-upgrade2/record_links_post_delete_triggers_gl.sql
new file mode 100644
index 000000000..595c8ca9a
--- /dev/null
+++ b/sql/Pg-upgrade2/record_links_post_delete_triggers_gl.sql
@@ -0,0 +1,44 @@
+-- @tag: record_links_post_delete_triggers_gl2
+-- @description: Datenbankkonsistenz record_links nach Löschen von Dialogbuchungen und Briefen
+-- @depends: release_3_5_3
+
+-- When deleting records record_links weren't cleaned up until now
+-- This wasn't really a problem apart from the fact that record_links slowly grew
+-- but deleting records was seldom enough to not matter
+-- Unfortunately delivery_plan decides if an order need to be displayed by the
+-- number of record_links, which generates false negatives.
+-- so, first clean up the database, and after that create triggers to
+-- clean up automatically
+
+DELETE FROM record_links WHERE from_table = 'letter' AND from_id NOT IN (SELECT id FROM letter);
+DELETE FROM record_links WHERE to_table   = 'letter' AND to_id   NOT IN (SELECT id FROM letter);
+
+DELETE FROM record_links WHERE from_table = 'gl' AND from_id NOT IN (SELECT id FROM gl);
+DELETE FROM record_links WHERE to_table   = 'gl' AND to_id   NOT IN (SELECT id FROM gl);
+
+CREATE OR REPLACE FUNCTION clean_up_record_links_before_letter_delete() RETURNS trigger AS $$
+  BEGIN
+    DELETE FROM record_links
+      WHERE (from_table = 'letter' AND from_id = OLD.id)
+         OR (to_table   = 'letter' AND to_id   = OLD.id);
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION clean_up_record_links_before_gl_delete() RETURNS trigger AS $$
+  BEGIN
+    DELETE FROM record_links
+      WHERE (from_table = 'gl' AND from_id = OLD.id)
+         OR (to_table   = 'gl' AND to_id   = OLD.id);
+    RETURN OLD;
+  END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE TRIGGER before_delete_gl_trigger
+BEFORE DELETE ON gl FOR EACH ROW EXECUTE
+PROCEDURE clean_up_record_links_before_gl_delete();
+
+CREATE TRIGGER before_delete_letter_trigger
+BEFORE DELETE ON letter FOR EACH ROW EXECUTE
+PROCEDURE clean_up_record_links_before_letter_delete();