Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE purchases (
  pid integer primary key,
  amount numeric(16,2)
);

CREATE TABLE total_expense (
  id integer primary key,
  total_amount numeric(16,2)
);


CREATE OR REPLACE FUNCTION update_sum() RETURNS TRIGGER AS 
$$
DECLARE
  delta numeric(16,2);
BEGIN

   IF (TG_OP = 'DELETE') THEN
     delta = -OLD.amount;
   ELSIF (TG_OP = 'UPDATE') THEN
     delta = NEW.amount - OLD.amount;
   ELSE -- (TG_OP = 'INSERT') 
     delta = NEW.amount;
   END IF;


  INSERT INTO total_expense AS te VALUES (1, delta)
    ON CONFLICT (id) DO UPDATE SET
      total_amount = te.total_amount + EXCLUDED.total_amount;

  RETURN NULL;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER updateSum  AFTER INSERT OR UPDATE OR DELETE ON purchases
FOR EACH ROW
EXECUTE PROCEDURE update_sum();

insert into purchases values (1,100);
select * from purchases;
select * from total_expense;
1 rows affected



pid
amount


1
100.00




id
total_amount


1
100.00


insert into purchases values (2,200);
select * from purchases;
select * from total_expense;
1 rows affected



pid
amount


1
100.00

2
200.00




id
total_amount


1
300.00


delete from purchases where pid = 1;
select * from purchases;
select * from total_expense;
1 rows affected



pid
amount


2
200.00




id
total_amount


1
200.00