CODEWITHSUNDEEP
postgresqltriggers
Boris Grebnevskyi
Boris Grebnevskyi

Reputation: 37

Postgres trigger inserts all rows

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)

Upvotes: 0

Views: 434

Answers (1)

eshirvana
eshirvana

Reputation: 24633

cleary in your proc you are inserting everything in the test.purchase table , if you want to insert newly inserted/updated columns you have to use NEW so for example :

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)
VaLUES(
NEW.product_id,
NEW.qty,
NEW.euro_stock_price,
NEW.euro_stock_price * qty, -- replaced stock_amt
NEW.import_id,
NEW.expiry_date
);
RETURN NEW;
END
$BODY$;

if this is what you want to do

Upvotes: 1

Related Questions