sql/Pg-upgrade2/record_links_orderitems_delete_triggers.sql

   1 -- @tag: record_links_orderitems_delete_triggers
   2 -- @description: delete trigger für verknüpfte invoice(items), orderitems und delivery_order_items
   3 -- @depends: record_links_post_delete_triggers2 release_3_1_0
   4 CREATE OR REPLACE FUNCTION clean_up_record_links_before_orderitems_delete() RETURNS trigger AS $$
   5   BEGIN
   6     DELETE FROM record_links
   7       WHERE (from_table = 'orderitems' AND from_id = OLD.id)
   8          OR (to_table   = 'orderitems' AND to_id   = OLD.id);
   9     RETURN OLD;
  10   END;
  11 $$ LANGUAGE plpgsql;
  12
  13 CREATE OR REPLACE FUNCTION clean_up_record_links_before_delivery_order_items_delete() RETURNS trigger AS $$
  14   BEGIN
  15     DELETE FROM record_links
  16       WHERE (from_table = 'delivery_order_items' AND from_id = OLD.id)
  17          OR (to_table   = 'delivery_order_items' AND to_id   = OLD.id);
  18     RETURN OLD;
  19   END;
  20 $$ LANGUAGE plpgsql;
  21
  22 CREATE OR REPLACE FUNCTION clean_up_record_links_before_invoice_delete() RETURNS trigger AS $$
  23   BEGIN
  24     DELETE FROM record_links
  25       WHERE (from_table = 'invoice' AND from_id = OLD.id)
  26          OR (to_table   = 'invoice' AND to_id   = OLD.id);
  27     RETURN OLD;
  28   END;
  29 $$ LANGUAGE plpgsql;
  30
  31
  32
  33 CREATE TRIGGER before_delete_orderitems_trigger
  34 BEFORE DELETE ON orderitems FOR EACH ROW EXECUTE
  35 PROCEDURE clean_up_record_links_before_orderitems_delete();
  36
  37 CREATE TRIGGER before_delete_delivery_order_items_trigger
  38 BEFORE DELETE ON delivery_order_items FOR EACH ROW EXECUTE
  39 PROCEDURE clean_up_record_links_before_delivery_order_items_delete();
  40
  41 CREATE TRIGGER before_delete_invoice_trigger
  42 BEFORE DELETE ON invoice FOR EACH ROW EXECUTE
  43 PROCEDURE clean_up_record_links_before_invoice_delete();
  44
  45
  46