From 13ba701968d19e39a9a8d4cb55ba2db9a31706e9 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Sven=20Sch=C3=B6ling?= Date: Mon, 17 Sep 2012 15:29:02 +0200 Subject: [PATCH] =?utf8?q?record=5Flinks=20beim=20L=C3=B6schen=20von=20Bel?= =?utf8?q?egen=20aufr=C3=A4umen?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- .../record_links_post_delete_triggers.sql | 72 +++++++++++++++++++ 1 file changed, 72 insertions(+) create mode 100644 sql/Pg-upgrade2/record_links_post_delete_triggers.sql diff --git a/sql/Pg-upgrade2/record_links_post_delete_triggers.sql b/sql/Pg-upgrade2/record_links_post_delete_triggers.sql new file mode 100644 index 000000000..0f2b872f6 --- /dev/null +++ b/sql/Pg-upgrade2/record_links_post_delete_triggers.sql @@ -0,0 +1,72 @@ +-- @tag: record_links_post_delete_triggers +-- @description: Datenbankkonsistenz nach dem löschen von Belegen +-- @depends: release_2_7_0 +-- @encoding: utf8 + +-- When deleting records record_links weren't cleaned up until now +-- This wasn't rally 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 = 'oe' AND from_id NOT IN (SELECT id FROM oe); +DELETE FROM record_links WHERE to_table = 'oe' AND to_id NOT IN (SELECT id FROM oe); + +DELETE FROM record_links WHERE from_table = 'delivery_orders' AND from_id NOT IN (SELECT id FROM delivery_orders); +DELETE FROM record_links WHERE to_table = 'delivery_orders' AND to_id NOT IN (SELECT id FROM delivery_orders); + +DELETE FROM record_links WHERE from_table = 'ar' AND from_id NOT IN (SELECT id FROM ar); +DELETE FROM record_links WHERE to_table = 'ar' AND to_id NOT IN (SELECT id FROM ar); + +DELETE FROM record_links WHERE from_table = 'ap' AND from_id NOT IN (SELECT id FROM ap); +DELETE FROM record_links WHERE to_table = 'ap' AND to_id NOT IN (SELECT id FROM ap); + +CREATE OR REPLACE FUNCTION clean_up_record_links_before_oe_delete() RETURNS trigger AS $$ + BEGIN + DELETE FROM record_links + WHERE (from_table = 'oe' AND from_id = OLD.id) + OR (to_table = 'oe' AND to_id = OLD.id); + END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION clean_up_record_links_before_delivery_orders_delete() RETURNS trigger AS $$ + BEGIN + DELETE FROM record_links + WHERE (from_table = 'delivery_orders' AND from_id = OLD.id) + OR (to_table = 'delivery_orders' AND to_id = OLD.id); + END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION clean_up_record_links_before_ar_delete() RETURNS trigger AS $$ + BEGIN + DELETE FROM record_links + WHERE (from_table = 'ar' AND from_id = OLD.id) + OR (to_table = 'ar' AND to_id = OLD.id); + END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION clean_up_record_links_before_ap_delete() RETURNS trigger AS $$ + BEGIN + DELETE FROM record_links + WHERE (from_table = 'ap' AND from_id = OLD.id) + OR (to_table = 'ap' AND to_id = OLD.id); + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER before_delete_oe_trigger +BEFORE DELETE ON oe FOR EACH ROW EXECUTE +PROCEDURE clean_up_record_links_before_oe_delete(); + +CREATE TRIGGER before_delete_delivery_orders_trigger +BEFORE DELETE ON delivery_orders FOR EACH ROW EXECUTE +PROCEDURE clean_up_record_links_before_delivery_orders_delete(); + +CREATE TRIGGER before_delete_ar_trigger +BEFORE DELETE ON ar FOR EACH ROW EXECUTE +PROCEDURE clean_up_record_links_before_ar_delete(); + +CREATE TRIGGER before_delete_ap_trigger +BEFORE DELETE ON ap FOR EACH ROW EXECUTE +PROCEDURE clean_up_record_links_before_ap_delete(); -- 2.39.5