sql/Pg-upgrade2/oe_do_delete_via_trigger.pl

   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
   4
   5 package SL::DBUpgrade2::oe_do_delete_via_trigger;
   6
   7 use utf8;
   8 use strict;
   9
  10 use parent qw(SL::DBUpgrade2::Base);
  11
  12 sub run {
  13   my ($self) = @_;
  14
  15   $self->drop_constraints(table => $_) for qw(periodic_invoices periodic_invoices_configs orderitems delivery_order_items delivery_order_items_stock);
  16
  17   my @queries = (
  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|,
  20
  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|,
  24
  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|,
  30
  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|,
  36
  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|,
  40
  41     q|CREATE OR REPLACE FUNCTION oe_before_delete_trigger() RETURNS trigger AS $$
  42         BEGIN
  43           DELETE FROM status WHERE trans_id = OLD.id;
  44           DELETE FROM shipto WHERE (trans_id = OLD.id) AND (module = 'OE');
  45
  46           RETURN OLD;
  47         END;
  48       $$ LANGUAGE plpgsql|,
  49
  50     q|DROP TRIGGER IF EXISTS delete_oe_dependencies ON oe|,
  51
  52     q|CREATE TRIGGER delete_oe_dependencies
  53       BEFORE DELETE ON oe
  54       FOR EACH ROW EXECUTE PROCEDURE oe_before_delete_trigger()|,
  55
  56     q|CREATE OR REPLACE FUNCTION delivery_orders_before_delete_trigger() RETURNS trigger AS $$
  57         BEGIN
  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');
  61
  62           RETURN OLD;
  63         END;
  64       $$ LANGUAGE plpgsql|,
  65
  66     q|DROP TRIGGER IF EXISTS delete_delivery_orders_dependencies ON delivery_orders|,
  67
  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()|);
  71
  72   $self->db_query($_) for @queries;
  73
  74   return 1;
  75 }
  76
  77 1;