I’m working on a Warehouse Database and commodity table.
I have an Inventory_Commodity_log table that has multiple columns as (id, inventory_commodity_id, amount, update_date TIMESTAMP(6),document_type_id BIGINT,….).I should do multiple modification on it:

First, I should change the amount of this table per day: Becasue of the update_date Type is TIMESTAMP, it may we have multiple entrance and exist of commodity. (document_type_id defines the type of commodity is entrance to warehouse or exit from warehouse)

Second, I should calculate cumulative summation of amount for each commodity.

For doing this, I defined a table and Trigger to handle it on PostgreSQL as follow:

CREATE TABLE summary_log(id BIGINT NOT NULL
CONSTRAINT pk_summary_log_id
PRIMARY KEY,
inventory_commodity_id BIGINT NOT NULL
CONSTRAINT fk_summary_log_inventorycommodity
REFERENCES storeroom.inventory_commodity,
amount DOUBLE PRECISION, update_date date);

`CREATE OR REPLACE FUNCTION Update_inventory_summary()
RETURNS TRIGGER AS $$
DECLARE
cum_amount DOUBLE PRECISION;
BEGIN

With cte AS ( SELECT inventory_commodity_id, update_date,amount,
CASE WHEN document_type_id=1919 THEN amount
Else -1*amount END AS positive_negative_amount
FROM storeroom.inventory_commodity_log),

amount_groupby AS (
Select cte.inventory_commodity_id,cte.update_date::date AS per_day,
sum(positive_negative_amount) AS amount
From cte
GROUP BY cte.inventory_commodity_id,per_day )

,cumulative_amounts AS (

SELECT inventory_commodity_id,per_day,
sum(amount) over (PARTITION BY inventory_commodity_id
ORDER BY per_day ASC
rows BETWEEN UNBOUNDED PRECEDING and CURRENT ROW ) AS cum_amount
from amount_groupby
)

Update the summary table with the new cumulative amount
UPDATE summary_log
SET amount = cumulative_amounts.cum_amount
FROM cumulative_amounts
WHERE summary_log.inventory_commodity_id = cumulative_amounts.inventory_commodity_id
AND summary_log.update_date = cumulative_amounts.per_day;

RETURN NEW;
END;
LANGUAGE plpgsql;

create TRIGGER update_inventory_trigger
AFTER INSERT or UPDATE ON storeroom.inventory_commodity_log
FOR EACH ROW
EXECUTE FUNCTION Update_inventory_summary();`

But, I didn’t get any values in my summary_log table: Could you help me what is the problem of my queris?

I put a lot of time on it, and I couldn’t solve it. If you know where is my problem, please help me.
Thank you.