From 28ceea79f873b8cdc0ef2e14102ad659bd21bb09 Mon Sep 17 00:00:00 2001 From: Moritz Bunkus Date: Wed, 1 Jun 2016 11:49:28 +0200 Subject: [PATCH] =?utf8?q?Artikelpreishistorie:=20Fix=20f=C3=BCr=20NULL-Be?= =?utf8?q?handlung=20im=20Trigger?= MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit --- sql/Pg-upgrade2/add_parts_price_history2.sql | 18 ++++++++++++++++++ 1 file changed, 18 insertions(+) create mode 100644 sql/Pg-upgrade2/add_parts_price_history2.sql diff --git a/sql/Pg-upgrade2/add_parts_price_history2.sql b/sql/Pg-upgrade2/add_parts_price_history2.sql new file mode 100644 index 000000000..ebf0036d7 --- /dev/null +++ b/sql/Pg-upgrade2/add_parts_price_history2.sql @@ -0,0 +1,18 @@ +-- @tag: add_parts_price_history2 +-- @description: Korrigierte Triggerfunktion für Entwicklung der Stammdatenpreise +-- @depends: add_parts_price_history +CREATE OR REPLACE FUNCTION add_parts_price_history_entry() RETURNS "trigger" AS $$ + BEGIN + IF (TG_OP = 'UPDATE') + AND ((OLD.lastcost IS NULL AND NEW.lastcost IS NULL) OR (OLD.lastcost = NEW.lastcost)) + AND ((OLD.listprice IS NULL AND NEW.listprice IS NULL) OR (OLD.listprice = NEW.listprice)) + AND ((OLD.sellprice IS NULL AND NEW.sellprice IS NULL) OR (OLD.sellprice = NEW.sellprice)) THEN + RETURN NEW; + END IF; + + INSERT INTO parts_price_history (part_id, lastcost, listprice, sellprice, valid_from) + VALUES (NEW.id, NEW.lastcost, NEW.listprice, NEW.sellprice, now()); + + RETURN NEW; + END; +$$ LANGUAGE plpgsql; -- 2.20.1