1 -- @tag: delete_cvars_on_trans_deletion
2 -- @description: Einträge in benutzerdefinierten Variablen löschen, deren Bezugsbelege gelöscht wurde
3 -- @depends: release_3_1_0
5 -- 1. Alle benutzerdefinierten Variablen löschen, für die es keine
6 -- Einträge in den dazugehörigen Tabellen mehr gibt.
7 CREATE TEMPORARY TABLE cvars_to_delete AS
9 FROM custom_variables cv
10 LEFT JOIN custom_variable_configs cfg ON (cv.config_id = cfg.id)
12 -- 1.1. Alle CVars für Artikel selber (sub_module ist leer):
13 LEFT JOIN parts p ON (
15 AND ((cv.sub_module IS NULL) OR (cv.sub_module = ''))
16 AND (cv.trans_id = p.id))
18 -- 1.2. Alle CVars für Angebote/Aufträge, Lieferscheine, Rechnungen
19 -- (cv.sub_module gesetzt):
20 LEFT JOIN orderitems oi ON (
22 AND (cv.sub_module = 'orderitems')
23 AND (cv.trans_id = oi.id))
25 LEFT JOIN delivery_order_items doi ON (
27 AND (cv.sub_module = 'delivery_order_items')
28 AND (cv.trans_id = doi.id))
30 LEFT JOIN invoice i ON (
32 AND (cv.sub_module = 'invoice')
33 AND (cv.trans_id = i.id))
35 -- 1.3. Alle CVars für Kunden/Lieferanten:
36 LEFT JOIN customer c ON (
38 AND (cv.trans_id = c.id))
40 LEFT JOIN vendor v ON (
42 AND (cv.trans_id = v.id))
44 -- 1.4. Alle CVars für Ansprechpersonen:
45 LEFT JOIN contacts cp ON (
46 (cfg.module = 'Contacts')
47 AND (cv.trans_id = cp.cp_id))
49 -- 1.5. Alle CVars für Projekte:
50 LEFT JOIN project pr ON (
51 (cfg.module = 'Projects')
52 AND (cv.trans_id = pr.id))
54 -- Nun diejenigen Zeilen nehmen, für die es in keiner der verknüpften
55 -- Tabellen eine korrespondierende Zeile gibt.
62 AND (cp.cp_id IS NULL)
65 DELETE FROM custom_variables
68 FROM cvars_to_delete ctd
69 WHERE ctd.id = custom_variables.id
72 -- 2. Triggerfunktionen erstellen, die die benutzerdefinierten
75 -- 2.1. Parametrisierte Backend-Funktion zum Löschen:
76 CREATE OR REPLACE FUNCTION delete_custom_variables_with_sub_module(config_module TEXT, cvar_sub_module TEXT, old_id INTEGER)
79 DELETE FROM custom_variables
80 WHERE (config_id IN (SELECT id FROM custom_variable_configs WHERE module = config_module))
81 AND (COALESCE(sub_module, '') = cvar_sub_module)
82 AND (trans_id = old_id);
88 -- 2.2. Nun die Funktionen, die als Trigger aufgerufen wird und die
89 -- entscheidet, wie genau zu löschen ist:
90 CREATE OR REPLACE FUNCTION delete_custom_variables_trigger()
93 IF (TG_TABLE_NAME IN ('orderitems', 'delivery_order_items', 'invoice')) THEN
94 PERFORM delete_custom_variables_with_sub_module('IC', TG_TABLE_NAME, old.id);
97 IF (TG_TABLE_NAME = 'parts') THEN
98 PERFORM delete_custom_variables_with_sub_module('IC', '', old.id);
101 IF (TG_TABLE_NAME IN ('customer', 'vendor')) THEN
102 PERFORM delete_custom_variables_with_sub_module('CT', '', old.id);
105 IF (TG_TABLE_NAME = 'contacts') THEN
106 PERFORM delete_custom_variables_with_sub_module('Contacts', '', old.id);
109 IF (TG_TABLE_NAME = 'project') THEN
110 PERFORM delete_custom_variables_with_sub_module('Projects', '', old.id);
117 -- 3. Die eigentlichen Trigger erstellen:
120 DROP TRIGGER IF EXISTS orderitems_delete_custom_variables_after_deletion ON orderitems;
122 CREATE TRIGGER orderitems_delete_custom_variables_after_deletion
123 AFTER DELETE ON orderitems
124 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
126 -- 3.2. delivery_order_items
127 DROP TRIGGER IF EXISTS delivery_order_items_delete_custom_variables_after_deletion ON delivery_order_items;
129 CREATE TRIGGER delivery_order_items_delete_custom_variables_after_deletion
130 AFTER DELETE ON delivery_order_items
131 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
134 DROP TRIGGER IF EXISTS invoice_delete_custom_variables_after_deletion ON invoice;
136 CREATE TRIGGER invoice_delete_custom_variables_after_deletion
137 AFTER DELETE ON invoice
138 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
141 DROP TRIGGER IF EXISTS parts_delete_custom_variables_after_deletion ON parts;
143 CREATE TRIGGER parts_delete_custom_variables_after_deletion
144 AFTER DELETE ON parts
145 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
148 DROP TRIGGER IF EXISTS customer_delete_custom_variables_after_deletion ON customer;
150 CREATE TRIGGER customer_delete_custom_variables_after_deletion
151 AFTER DELETE ON customer
152 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
155 DROP TRIGGER IF EXISTS vendor_delete_custom_variables_after_deletion ON vendor;
157 CREATE TRIGGER vendor_delete_custom_variables_after_deletion
158 AFTER DELETE ON vendor
159 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
162 DROP TRIGGER IF EXISTS contacts_delete_custom_variables_after_deletion ON contacts;
164 CREATE TRIGGER contacts_delete_custom_variables_after_deletion
165 AFTER DELETE ON contacts
166 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();
169 DROP TRIGGER IF EXISTS project_delete_custom_variables_after_deletion ON project;
171 CREATE TRIGGER project_delete_custom_variables_after_deletion
172 AFTER DELETE ON project
173 FOR EACH ROW EXECUTE PROCEDURE delete_custom_variables_trigger();