1 # @tag: oe_do_delete_via_trigger
2 # @description: Aus oe/delivery_orders via Trigger löschen können
3 # @depends: orderitems_delivery_order_items_invoice_foreign_keys
5 package SL::DBUpgrade2::oe_do_delete_via_trigger;
10 use parent qw(SL::DBUpgrade2::Base);
15 $self->drop_constraints(table => $_) for qw(periodic_invoices periodic_invoices_configs orderitems delivery_order_items delivery_order_items_stock);
18 q|ALTER TABLE periodic_invoices ADD CONSTRAINT periodic_invoices_ar_id_fkey FOREIGN KEY (ar_id) REFERENCES ar (id) ON DELETE CASCADE|,
19 q|ALTER TABLE periodic_invoices ADD CONSTRAINT periodic_invoices_config_id_fkey FOREIGN KEY (config_id) REFERENCES periodic_invoices_configs (id) ON DELETE CASCADE|,
21 q|ALTER TABLE periodic_invoices_configs ADD CONSTRAINT periodic_invoices_configs_ar_chart_id_fkey FOREIGN KEY (ar_chart_id) REFERENCES chart (id) ON DELETE RESTRICT|,
22 q|ALTER TABLE periodic_invoices_configs ADD CONSTRAINT periodic_invoices_configs_oe_id_fkey FOREIGN KEY (oe_id) REFERENCES oe (id) ON DELETE CASCADE|,
23 q|ALTER TABLE periodic_invoices_configs ADD CONSTRAINT periodic_invoices_configs_printer_id_fkey FOREIGN KEY (printer_id) REFERENCES printers (id) ON DELETE SET NULL|,
25 q|ALTER TABLE orderitems ADD CONSTRAINT orderitems_parts_id_fkey FOREIGN KEY (parts_id) REFERENCES parts (id) ON DELETE RESTRICT|,
26 q|ALTER TABLE orderitems ADD CONSTRAINT orderitems_price_factor_id_fkey FOREIGN KEY (price_factor_id) REFERENCES price_factors (id) ON DELETE RESTRICT|,
27 q|ALTER TABLE orderitems ADD CONSTRAINT orderitems_pricegroup_id_fkey FOREIGN KEY (pricegroup_id) REFERENCES pricegroup (id) ON DELETE RESTRICT|,
28 q|ALTER TABLE orderitems ADD CONSTRAINT orderitems_project_id_fkey FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL|,
29 q|ALTER TABLE orderitems ADD CONSTRAINT orderitems_trans_id_fkey FOREIGN KEY (trans_id) REFERENCES oe (id) ON DELETE CASCADE|,
31 q|ALTER TABLE delivery_order_items ADD CONSTRAINT delivery_order_items_delivery_order_id_fkey FOREIGN KEY (delivery_order_id) REFERENCES delivery_orders (id) ON DELETE CASCADE|,
32 q|ALTER TABLE delivery_order_items ADD CONSTRAINT delivery_order_items_parts_id_fkey FOREIGN KEY (parts_id) REFERENCES parts (id) ON DELETE RESTRICT|,
33 q|ALTER TABLE delivery_order_items ADD CONSTRAINT delivery_order_items_price_factor_id_fkey FOREIGN KEY (price_factor_id) REFERENCES price_factors (id) ON DELETE RESTRICT|,
34 q|ALTER TABLE delivery_order_items ADD CONSTRAINT delivery_order_items_pricegroup_id_fkey FOREIGN KEY (pricegroup_id) REFERENCES pricegroup (id) ON DELETE RESTRICT|,
35 q|ALTER TABLE delivery_order_items ADD CONSTRAINT delivery_order_items_project_id_fkey FOREIGN KEY (project_id) REFERENCES project (id) ON DELETE SET NULL|,
37 q|ALTER TABLE delivery_order_items_stock ADD CONSTRAINT delivery_order_items_stock_bin_id_fkey FOREIGN KEY (bin_id) REFERENCES bin (id) ON DELETE RESTRICT|,
38 q|ALTER TABLE delivery_order_items_stock ADD CONSTRAINT delivery_order_items_stock_delivery_order_item_id_fkey FOREIGN KEY (delivery_order_item_id) REFERENCES delivery_order_items (id) ON DELETE CASCADE|,
39 q|ALTER TABLE delivery_order_items_stock ADD CONSTRAINT delivery_order_items_stock_warehouse_id_fkey FOREIGN KEY (warehouse_id) REFERENCES warehouse (id) ON DELETE RESTRICT|,
41 q|CREATE OR REPLACE FUNCTION oe_before_delete_trigger() RETURNS trigger AS $$
43 DELETE FROM status WHERE trans_id = OLD.id;
44 DELETE FROM shipto WHERE (trans_id = OLD.id) AND (module = 'OE');
50 q|DROP TRIGGER IF EXISTS delete_oe_dependencies ON oe|,
52 q|CREATE TRIGGER delete_oe_dependencies
54 FOR EACH ROW EXECUTE PROCEDURE oe_before_delete_trigger()|,
56 q|CREATE OR REPLACE FUNCTION delivery_orders_before_delete_trigger() RETURNS trigger AS $$
58 DELETE FROM status WHERE trans_id = OLD.id;
59 DELETE FROM delivery_order_items_stock WHERE delivery_order_item_id IN (SELECT id FROM delivery_order_items WHERE delivery_order_id = OLD.id);
60 DELETE FROM shipto WHERE (trans_id = OLD.id) AND (module = 'OE');
66 q|DROP TRIGGER IF EXISTS delete_delivery_orders_dependencies ON delivery_orders|,
68 q|CREATE TRIGGER delete_delivery_orders_dependencies
69 BEFORE DELETE ON delivery_orders
70 FOR EACH ROW EXECUTE PROCEDURE delivery_orders_before_delete_trigger()|);
72 $self->db_query($_) for @queries;