From 07c31856a83b520e82d21a40a749f3a1a7c6df6d Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Wed, 24 Mar 2021 14:07:07 +0100 Subject: [PATCH] =?utf8?q?Mahnungen:=20DB-Trigger=20zum=20L=C3=B6schen=20v?= =?utf8?q?on=20Verkn=C3=BCpfungen=20beim=20L=C3=B6schen=20einer=20Mahnung?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- ...cord_links_dunning_post_delete_trigger.sql | 21 +++++++++++++++++++ 1 file changed, 21 insertions(+) create mode 100644 sql/Pg-upgrade2/record_links_dunning_post_delete_trigger.sql diff --git a/sql/Pg-upgrade2/record_links_dunning_post_delete_trigger.sql b/sql/Pg-upgrade2/record_links_dunning_post_delete_trigger.sql new file mode 100644 index 000000000..f7a097e7d --- /dev/null +++ b/sql/Pg-upgrade2/record_links_dunning_post_delete_trigger.sql @@ -0,0 +1,21 @@ +-- @tag: record_links_dunning_post_delete_trigger +-- @description: Verknüpfte Belege für Mahnungen beim Löschen entfernen +-- @depends: release_3_5_6_1 + +-- clean up old dangling links +DELETE FROM record_links WHERE from_table = 'dunning' AND NOT EXISTS (SELECT id FROM dunning WHERE id = from_id); +DELETE FROM record_links WHERE to_table = 'dunning' AND NOT EXISTS (SELECT id FROM dunning WHERE id = to_id); + +-- install a trigger to delete links on delete +CREATE OR REPLACE FUNCTION clean_up_record_links_before_dunning_delete() RETURNS trigger AS $$ + BEGIN + DELETE FROM record_links + WHERE (from_table = 'dunning' AND from_id = OLD.id) + OR (to_table = 'dunning' AND to_id = OLD.id); + RETURN OLD; + END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER before_delete_dunning_trigger +BEFORE DELETE ON dunning FOR EACH ROW EXECUTE +PROCEDURE clean_up_record_links_before_dunning_delete(); -- 2.20.1