1 -- @tag: record_links_post_delete_triggers_gl2
2 -- @description: Datenbankkonsistenz record_links nach Löschen von Dialogbuchungen und Briefen
3 -- @depends: release_3_5_3
5 -- When deleting records record_links weren't cleaned up until now
6 -- This wasn't really a problem apart from the fact that record_links slowly grew
7 -- but deleting records was seldom enough to not matter
8 -- Unfortunately delivery_plan decides if an order need to be displayed by the
9 -- number of record_links, which generates false negatives.
10 -- so, first clean up the database, and after that create triggers to
11 -- clean up automatically
13 DELETE FROM record_links WHERE from_table = 'letter' AND from_id NOT IN (SELECT id FROM letter);
14 DELETE FROM record_links WHERE to_table = 'letter' AND to_id NOT IN (SELECT id FROM letter);
16 DELETE FROM record_links WHERE from_table = 'gl' AND from_id NOT IN (SELECT id FROM gl);
17 DELETE FROM record_links WHERE to_table = 'gl' AND to_id NOT IN (SELECT id FROM gl);
19 CREATE OR REPLACE FUNCTION clean_up_record_links_before_letter_delete() RETURNS trigger AS $$
21 DELETE FROM record_links
22 WHERE (from_table = 'letter' AND from_id = OLD.id)
23 OR (to_table = 'letter' AND to_id = OLD.id);
28 CREATE OR REPLACE FUNCTION clean_up_record_links_before_gl_delete() RETURNS trigger AS $$
30 DELETE FROM record_links
31 WHERE (from_table = 'gl' AND from_id = OLD.id)
32 OR (to_table = 'gl' AND to_id = OLD.id);
38 CREATE TRIGGER before_delete_gl_trigger
39 BEFORE DELETE ON gl FOR EACH ROW EXECUTE
40 PROCEDURE clean_up_record_links_before_gl_delete();
42 CREATE TRIGGER before_delete_letter_trigger
43 BEFORE DELETE ON letter FOR EACH ROW EXECUTE
44 PROCEDURE clean_up_record_links_before_letter_delete();