There are two tables test.purchase and test.stock. After filling test.purchase with INSERT query and 3 triggers which are calculate values in this table, it's last trigger which has to copy by INSERT INTO these values from test.purchase into test.stock.
But after each insert into test.purchase trigger inserts ALL rows from test.purchase into test.stock.
Please advise how to manage insert only last row from test.purchase after the query INSERT INTO test.purchase
CREATE TABLE test.purchase
(
import_id integer,
product_id integer,
usd_price numeric(10,2),
euro_price numeric(10,2),
qty integer,
euro_stock_price numeric(10,2),
expiry_date date,
euro_stock_amt numeric(10,2),
CONSTRAINT id UNIQUE (product_id)
INCLUDE(import_id),
CONSTRAINT purchase_import_id_fkey FOREIGN KEY (import_id)
REFERENCES test.imports (import_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT purchase_product_id_fkey FOREIGN KEY (product_id)
REFERENCES test.products (product_id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
CREATE TRIGGER tr_purchase_euro_after
AFTER INSERT OR UPDATE OF usd_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_price();
CREATE FUNCTION test.fn_purchase_euro_stock_price()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_stock_price = euro_price + euro_price * i.costs_per_euro
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER tr_purchase_euro_stock_amount
AFTER INSERT OR UPDATE OF euro_stock_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_amt();
CREATE FUNCTION test.fn_purchase_euro_stock_amt()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_stock_amt = euro_stock_price * qty
FROM test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END
$BODY$;
CREATE TRIGGER tr_purchase_euro_stock_price
AFTER INSERT OR UPDATE OF euro_price
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_euro_stock_price();
CREATE TRIGGER tr_purchase_stock_plus_after_insert
AFTER INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.fn_purchase_stock_plus();
CREATE FUNCTION test.fn_purchase_stock_plus()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
INSERT INTO test.stock(
product_id,
stock_qty,
stock_price,
stock_amt,
import_id,
expiry_date)
(SELECT DISTINCT
pr.product_id,
pr.qty,
pr.euro_stock_price,
pr.euro_stock_amt,
pr.import_id,
pr.expiry_date
FROM test.purchase pr
);
RETURN NEW;
END
$BODY$;
CREATE TABLE test.stock
(
product_id integer NOT NULL,
stock_qty integer,
stock_price numeric(10,2),
stock_amt numeric(10,2),
import_id integer,
expiry_date date
)
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,1,35,100, '12-21-2022');
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,2,35,100, '12-21-2022');
INSERT INTO test.purchase(
import_id, product_id, usd_price, qty, expiry_date)
VALUES (2,3,55,100, '12-21-2022');
beautylabs=# SELECT * FROM test.purchase;
| import_id | product_id | usd_price | euro_price | qty | euro_stock_price | expiry_date | euro_stock_amt |
|---|---|---|---|---|---|---|---|
| 2 | 1 | 35.00 | 28.60 | 100 | 32.84 | 2022-12-21 | 3284.00 |
| 2 | 2 | 35.00 | 28.60 | 100 | 32.84 | 2022-12-21 | 3284.00 |
| 2 | 3 | 55.00 | 44.94 | 100 | 51.60 | 2022-12-21 | 5160.00 |
(3 rows)
beautylabs=# SELECT * FROM test.stock;
| product_id | stock_qty | stock_price | stock_amt | import_id | expiry_date |
|---|---|---|---|---|---|
| 1 | 100 | 32.84 | 3284.00 | 2 | 2022-12-21 |
| 1 | 100 | 32.84 | 3284.00 | 2 | 2022-12-21 |
| 2 | 100 | 32.84 | 3284.00 | 2 | 2022-12-21 |
| 1 | 100 | 32.84 | 3284.00 | 2 | 2022-12-21 |
| 2 | 100 | 32.84 | 3284.00 | 2 | 2022-12-21 |
| 3 | 100 | 51.60 | 5160.00 | 2 | 2022-12-21 |
(6 rows)