1 # @tag: custom_variables_delete_via_trigger
2 # @description: Benutzerdefinierte Variablen werden nun via Trigger gelöscht.
3 # @depends: custom_variable_configs_column_type_text custom_variables custom_variables_indices custom_variables_indices_2 custom_variables_parts_services_assemblies custom_variables_sub_module_not_null custom_variables_valid
5 package SL::DBUpgrade2::custom_variables_delete_via_trigger;
10 use parent qw(SL::DBUpgrade2::Base);
16 #Delete orphaned entries
17 q|DELETE FROM custom_variables WHERE sub_module = 'orderitems'
18 AND NOT EXISTS (SELECT id FROM orderitems WHERE orderitems.id = custom_variables.trans_id)|,
19 q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items'
20 AND NOT EXISTS (SELECT id FROM delivery_order_items WHERE delivery_order_items.id = custom_variables.trans_id)|,
21 q|DELETE FROM custom_variables WHERE sub_module = 'invoice'
22 AND NOT EXISTS (SELECT id FROM invoice WHERE invoice.id = custom_variables.trans_id)|,
25 q|CREATE OR REPLACE FUNCTION orderitems_before_delete_trigger() RETURNS trigger AS $$
27 DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id = OLD.id;
33 q|DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems|,
35 q|CREATE TRIGGER delete_orderitems_dependencies
36 BEFORE DELETE ON orderitems
37 FOR EACH ROW EXECUTE PROCEDURE orderitems_before_delete_trigger()|,
39 q|CREATE OR REPLACE FUNCTION delivery_order_items_before_delete_trigger() RETURNS trigger AS $$
41 DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id = OLD.id;
47 q|DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items|,
49 q|CREATE TRIGGER delete_delivery_order_items_dependencies
50 BEFORE DELETE ON delivery_order_items
51 FOR EACH ROW EXECUTE PROCEDURE delivery_order_items_before_delete_trigger()|,
53 q|CREATE OR REPLACE FUNCTION invoice_before_delete_trigger() RETURNS trigger AS $$
55 DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id = OLD.id;
61 q|DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice|,
63 q|CREATE TRIGGER delete_invoice_dependencies
64 BEFORE DELETE ON invoice
65 FOR EACH ROW EXECUTE PROCEDURE invoice_before_delete_trigger()|
68 $self->db_query($_) for @queries;