1 -- @tag: record_links_post_delete_triggers
2 -- @description: Datenbankkonsistenz nach dem löschen von Belegen
3 -- @depends: release_2_7_0
6 -- When deleting records record_links weren't cleaned up until now
7 -- This wasn't rally a problem apart from the fact that record_links slowly grew
8 -- but deleting records was seldom enough to not matter
9 -- Unfortunately delivery_plan decides if an order need to be displayed by the
10 -- number of record_links, which generates false negatives.
11 -- so, first clean up the database, and after that create triggers to
12 -- clean up automatically
14 DELETE FROM record_links WHERE from_table = 'oe' AND from_id NOT IN (SELECT id FROM oe);
15 DELETE FROM record_links WHERE to_table = 'oe' AND to_id NOT IN (SELECT id FROM oe);
17 DELETE FROM record_links WHERE from_table = 'delivery_orders' AND from_id NOT IN (SELECT id FROM delivery_orders);
18 DELETE FROM record_links WHERE to_table = 'delivery_orders' AND to_id NOT IN (SELECT id FROM delivery_orders);
20 DELETE FROM record_links WHERE from_table = 'ar' AND from_id NOT IN (SELECT id FROM ar);
21 DELETE FROM record_links WHERE to_table = 'ar' AND to_id NOT IN (SELECT id FROM ar);
23 DELETE FROM record_links WHERE from_table = 'ap' AND from_id NOT IN (SELECT id FROM ap);
24 DELETE FROM record_links WHERE to_table = 'ap' AND to_id NOT IN (SELECT id FROM ap);
26 CREATE OR REPLACE FUNCTION clean_up_record_links_before_oe_delete() RETURNS trigger AS $$
28 DELETE FROM record_links
29 WHERE (from_table = 'oe' AND from_id = OLD.id)
30 OR (to_table = 'oe' AND to_id = OLD.id);
34 CREATE OR REPLACE FUNCTION clean_up_record_links_before_delivery_orders_delete() RETURNS trigger AS $$
36 DELETE FROM record_links
37 WHERE (from_table = 'delivery_orders' AND from_id = OLD.id)
38 OR (to_table = 'delivery_orders' AND to_id = OLD.id);
42 CREATE OR REPLACE FUNCTION clean_up_record_links_before_ar_delete() RETURNS trigger AS $$
44 DELETE FROM record_links
45 WHERE (from_table = 'ar' AND from_id = OLD.id)
46 OR (to_table = 'ar' AND to_id = OLD.id);
50 CREATE OR REPLACE FUNCTION clean_up_record_links_before_ap_delete() RETURNS trigger AS $$
52 DELETE FROM record_links
53 WHERE (from_table = 'ap' AND from_id = OLD.id)
54 OR (to_table = 'ap' AND to_id = OLD.id);
58 CREATE TRIGGER before_delete_oe_trigger
59 BEFORE DELETE ON oe FOR EACH ROW EXECUTE
60 PROCEDURE clean_up_record_links_before_oe_delete();
62 CREATE TRIGGER before_delete_delivery_orders_trigger
63 BEFORE DELETE ON delivery_orders FOR EACH ROW EXECUTE
64 PROCEDURE clean_up_record_links_before_delivery_orders_delete();
66 CREATE TRIGGER before_delete_ar_trigger
67 BEFORE DELETE ON ar FOR EACH ROW EXECUTE
68 PROCEDURE clean_up_record_links_before_ar_delete();
70 CREATE TRIGGER before_delete_ap_trigger
71 BEFORE DELETE ON ap FOR EACH ROW EXECUTE
72 PROCEDURE clean_up_record_links_before_ap_delete();