Trigger keeps updating value in every row instead of particular one-CodePudding

I am trying to calculate a sum for each particular order. I am using this trigger but it doesn't work properly, it updates every row with the same value instead of the only one with proper id.

done_services table

id
service_id
price

service table

id
name

payment table

id
sum
service_id

CREATE FUNCTION make_sum() RETURNS TRIGGER
    AS $$
        BEGIN
            UPDATE payment
                SET sum = (select sum(price) from done_services where service_id = new.service_id);
            RETURN NULL;
    END;$$ LANGUAGE plpgsql;
    
CREATE TRIGGER make_sum
AFTER INSERT ON basket FOR EACH ROW EXECUTE FUNCTION make_sum();

I used this command to enter an item insert into done_services(id, service_id, price) values(uuid_generate_v4(), '76594d2f-7153-495f-9671-0ddaa331568c', 100);

But the sum changed for both rows instead of the only one with service id Image

CodePudding user response：

The immediate cause for the error message is the missing WHERE clause as instructed by Edouard. Plus, prevent expensive empty updates like:

UPDATE payment p
SET    sum = ds.sum_price
FROM  (
   SELECT sum(d.price) AS sum_price
   FROM   done_services d
   WHERE  d.service_id = NEW.service_id
   ) ds
WHERE  p.service_id = sum_price
AND    p.sum IS DISTINCT FROM ds.sum_price;

In addition to fixing the prime error, this prevents empty updates that would not change the sum, but still write a new row version at full cost.

But the whole idea is questionable.

Keeping a sum from many rows up to date via trigger is expensive and error prone. Did you cover DELETE and INSERT accordingly? What about TRUNCATE? What about concurrent write access? Race conditions, deadlocks?

To get get the current sum for a set that can change dynamically, the superior solution is typically not to save that sum in the table at all. Use a VIEW or MATERIALIZED VIEW instead.
Or, to get the sum for a single or few payments, use a function:

CREATE OR REPLACE FUNCTION f_payment_sum(_service_id int)
  RETURNS numeric
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT sum(d.price)
FROM   done_services d
WHERE  d.service_id = _service_id;
$func$

Updating a row based on a value from another table?

CodePudding user response：

just missing something in your UPDATE statement :

UPDATE payment
   SET sum = (select sum(price) from done_services where service_id = new.service_id)
 WHERE service_id = new.service_id ;

Next time please create a dbfiddle with your data model, sample of data and queries.