From f1faf9a9eca4073119f7c7e0f43e70399e041777 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Bernd=20Ble=C3=9Fmann?= Date: Mon, 30 Jun 2014 11:14:45 +0200 Subject: [PATCH] =?utf8?q?DB-Trigger,=20um=20sicher=20zu=20stellen,=20dass?= =?utf8?q?=20ein=20Lagerplatz=20auch=20zum=20Lager=20geh=C3=B6rt.?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit Betrifft #2485. --- .../check_bin_belongs_to_wh_trigger.sql | 28 +++++++++++++++++++ 1 file changed, 28 insertions(+) create mode 100644 sql/Pg-upgrade2/check_bin_belongs_to_wh_trigger.sql diff --git a/sql/Pg-upgrade2/check_bin_belongs_to_wh_trigger.sql b/sql/Pg-upgrade2/check_bin_belongs_to_wh_trigger.sql new file mode 100644 index 000000000..5b5e3d931 --- /dev/null +++ b/sql/Pg-upgrade2/check_bin_belongs_to_wh_trigger.sql @@ -0,0 +1,28 @@ +-- @tag: check_bin_belongs_to_wh_trigger +-- @description: Trigger, um sicher zu stellen, dass ein angegebener Lagerplatz auch zum Lager gehört. +-- @depends: delivery_orders warehouse +-- @encoding: utf-8 + +CREATE FUNCTION check_bin_belongs_to_wh() RETURNS "trigger" + AS 'BEGIN + IF NEW.bin_id IS NULL AND NEW.warehouse_id IS NULL THEN + RETURN NEW; + END IF; + IF NEW.bin_id IN (SELECT id FROM bin WHERE warehouse_id = NEW.warehouse_id) THEN + RETURN NEW; + ELSE + RAISE EXCEPTION ''bin (id=%) does not belong to warehouse (id=%).'', NEW.bin_id, NEW.warehouse_id; + RETURN NULL; + END IF; + END;' + LANGUAGE plpgsql; + + +CREATE TRIGGER check_bin_wh_delivery_order_items_stock BEFORE INSERT OR UPDATE ON delivery_order_items_stock + FOR EACH ROW EXECUTE PROCEDURE check_bin_belongs_to_wh(); + +CREATE TRIGGER check_bin_wh_inventory BEFORE INSERT OR UPDATE ON inventory + FOR EACH ROW EXECUTE PROCEDURE check_bin_belongs_to_wh(); + +CREATE TRIGGER check_bin_wh_parts BEFORE INSERT OR UPDATE ON parts + FOR EACH ROW EXECUTE PROCEDURE check_bin_belongs_to_wh(); -- 2.20.1