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' AND trans_id NOT IN (SELECT id FROM orderitems)|,
18 q|DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id NOT IN (SELECT id FROM delivery_order_items)|,
19 q|DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id NOT IN (SELECT id FROM invoice)|,
22 q|CREATE OR REPLACE FUNCTION orderitems_before_delete_trigger() RETURNS trigger AS $$
24 DELETE FROM custom_variables WHERE sub_module = 'orderitems' AND trans_id = OLD.id;
30 q|DROP TRIGGER IF EXISTS delete_orderitems_dependencies ON orderitems|,
32 q|CREATE TRIGGER delete_orderitems_dependencies
33 BEFORE DELETE ON orderitems
34 FOR EACH ROW EXECUTE PROCEDURE orderitems_before_delete_trigger()|,
36 q|CREATE OR REPLACE FUNCTION delivery_order_items_before_delete_trigger() RETURNS trigger AS $$
38 DELETE FROM custom_variables WHERE sub_module = 'delivery_order_items' AND trans_id = OLD.id;
44 q|DROP TRIGGER IF EXISTS delete_delivery_order_items_dependencies ON delivery_order_items|,
46 q|CREATE TRIGGER delete_delivery_order_items_dependencies
47 BEFORE DELETE ON delivery_order_items
48 FOR EACH ROW EXECUTE PROCEDURE delivery_order_items_before_delete_trigger()|,
50 q|CREATE OR REPLACE FUNCTION invoice_before_delete_trigger() RETURNS trigger AS $$
52 DELETE FROM custom_variables WHERE sub_module = 'invoice' AND trans_id = OLD.id;
58 q|DROP TRIGGER IF EXISTS delete_invoice_dependencies ON invoice|,
60 q|CREATE TRIGGER delete_invoice_dependencies
61 BEFORE DELETE ON invoice
62 FOR EACH ROW EXECUTE PROCEDURE invoice_before_delete_trigger()|
65 $self->db_query($_) for @queries;