From 024f31b4029316901a4e9e22af8c795f05d3d818 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Jan=20B=C3=BCren?= Date: Wed, 20 Feb 2019 11:40:15 +0100 Subject: [PATCH] =?utf8?q?Trigger=20zum=20L=C3=B6schen=20von=20RecordLinks?= =?utf8?q?=20auch=20f=C3=BCr=20verkn=C3=BCpfte=20Briefe=20und=20Dialogbuch?= =?utf8?q?ugen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../record_links_post_delete_triggers_gl.sql | 44 +++++++++++++++++++ 1 file changed, 44 insertions(+) create mode 100644 sql/Pg-upgrade2/record_links_post_delete_triggers_gl.sql 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(); -- 2.20.1