1 -- @tag: add_parts_price_history
2 -- @description: Tabelle für Entwicklung der Stammdatenpreise
3 -- @depends: release_3_4_0
4 DROP TRIGGER IF EXISTS add_parts_price_history_entry_after_changes_on_parts ON parts;
5 DROP FUNCTION IF EXISTS add_parts_price_history_entry();
6 DROP TABLE IF EXISTS parts_price_history;
8 CREATE TABLE parts_price_history (
10 part_id INTEGER NOT NULL,
11 valid_from TIMESTAMP NOT NULL,
12 lastcost NUMERIC(15, 5),
13 listprice NUMERIC(15, 5),
14 sellprice NUMERIC(15, 5),
17 FOREIGN KEY (part_id) REFERENCES parts (id) ON DELETE CASCADE
20 INSERT INTO parts_price_history (part_id, valid_from, lastcost, listprice, sellprice)
21 SELECT id, COALESCE(COALESCE(mtime, itime), now()), lastcost, listprice, sellprice
24 CREATE FUNCTION add_parts_price_history_entry() RETURNS "trigger" AS $$
26 IF (TG_OP = 'UPDATE') AND (OLD.lastcost = NEW.lastcost) AND (OLD.listprice = NEW.listprice) AND (OLD.sellprice = NEW.sellprice) THEN
30 INSERT INTO parts_price_history (part_id, lastcost, listprice, sellprice, valid_from)
31 VALUES (NEW.id, NEW.lastcost, NEW.listprice, NEW.sellprice, now());
37 CREATE TRIGGER add_parts_price_history_entry_after_changes_on_parts
38 AFTER INSERT OR UPDATE on parts
39 FOR EACH ROW EXECUTE PROCEDURE add_parts_price_history_entry();