1 -- @tag: record_links_post_delete_triggers
2 -- @description: Datenbankkonsistenz nach dem löschen von Belegen
3 -- @depends: release_2_7_0
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 = 'oe' AND from_id NOT IN (SELECT id FROM oe);
14 DELETE FROM record_links WHERE to_table = 'oe' AND to_id NOT IN (SELECT id FROM oe);
16 DELETE FROM record_links WHERE from_table = 'delivery_orders' AND from_id NOT IN (SELECT id FROM delivery_orders);
17 DELETE FROM record_links WHERE to_table = 'delivery_orders' AND to_id NOT IN (SELECT id FROM delivery_orders);
19 DELETE FROM record_links WHERE from_table = 'ar' AND from_id NOT IN (SELECT id FROM ar);
20 DELETE FROM record_links WHERE to_table = 'ar' AND to_id NOT IN (SELECT id FROM ar);
22 DELETE FROM record_links WHERE from_table = 'ap' AND from_id NOT IN (SELECT id FROM ap);
23 DELETE FROM record_links WHERE to_table = 'ap' AND to_id NOT IN (SELECT id FROM ap);
25 CREATE OR REPLACE FUNCTION clean_up_record_links_before_oe_delete() RETURNS trigger AS $$
27 DELETE FROM record_links
28 WHERE (from_table = 'oe' AND from_id = OLD.id)
29 OR (to_table = 'oe' AND to_id = OLD.id);
33 CREATE OR REPLACE FUNCTION clean_up_record_links_before_delivery_orders_delete() RETURNS trigger AS $$
35 DELETE FROM record_links
36 WHERE (from_table = 'delivery_orders' AND from_id = OLD.id)
37 OR (to_table = 'delivery_orders' AND to_id = OLD.id);
41 CREATE OR REPLACE FUNCTION clean_up_record_links_before_ar_delete() RETURNS trigger AS $$
43 DELETE FROM record_links
44 WHERE (from_table = 'ar' AND from_id = OLD.id)
45 OR (to_table = 'ar' AND to_id = OLD.id);
49 CREATE OR REPLACE FUNCTION clean_up_record_links_before_ap_delete() RETURNS trigger AS $$
51 DELETE FROM record_links
52 WHERE (from_table = 'ap' AND from_id = OLD.id)
53 OR (to_table = 'ap' AND to_id = OLD.id);
57 CREATE TRIGGER before_delete_oe_trigger
58 BEFORE DELETE ON oe FOR EACH ROW EXECUTE
59 PROCEDURE clean_up_record_links_before_oe_delete();
61 CREATE TRIGGER before_delete_delivery_orders_trigger
62 BEFORE DELETE ON delivery_orders FOR EACH ROW EXECUTE
63 PROCEDURE clean_up_record_links_before_delivery_orders_delete();
65 CREATE TRIGGER before_delete_ar_trigger
66 BEFORE DELETE ON ar FOR EACH ROW EXECUTE
67 PROCEDURE clean_up_record_links_before_ar_delete();
69 CREATE TRIGGER before_delete_ap_trigger
70 BEFORE DELETE ON ap FOR EACH ROW EXECUTE
71 PROCEDURE clean_up_record_links_before_ap_delete();