I have two tables: Purchases(pid, amount), Total_Expense(total_amount) // stores the total $ amount of all purchases.

I want to create a trigger that after a value has been added/deleted/updated to purchases, it will make the total_amount change to the new value. Ex:

If you add a new row to purchases:

then update Total_Expense

I've tried:

CREATE OR REPLACE FUNCTION update_sum() RETURNS TRIGGER AS 
$$BEGIN
UPDATE total_expense SET total_amount = total_amount   new.amount;
RETURN NULL;
END
$$
LANGUAGE plpgsql;

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

This will add the values into purchases but not insert values into total_expense.

I am having a lot of issues with creating my first trigger so any help would be nice. Thanks!

CodePudding user response：

I tried to make your code work so here is what I came up with.

create or replace function update_sum() returns trigger as $$
begin
update total_expense set total_amount = (select sum(amount) from purchases);
return new;
end;
$$
language plpgsql;

create or replace trigger mytrigger
after insert or delete or update on purchases
for each row
execute procedure update_amount();

Note that if total_expense table is empty then nothing will change since there will be nothing to update.

Also this solution is not efficient. Because it's running a select operation on each row. A more advanced solution could be developed that could have different functions for update and delete cases but I haven't written triggers and functions for a long time I've forgotten them.

CodePudding user response：

Depending on the operation that fired the trigger the update amount differs. TRUNCATE shall be handled separately.

create or replace function update_sum() returns trigger language plpgsql as 
$$
declare 
  delta numeric;
begin
  case TG_OP
    when 'UPDATE' then delta := new.amount - old.amount;
    when 'INSERT' then delta := new.amount;
    when 'DELETE' then delta := 0 - old.amount;
  end case;
  update total_expense set total_amount = total_amount   delta; 
  return null;
end;
$$;

However I fully support the opinion of @Jeremy.

CodePudding user response：

You need to implement different behavior for different operations:

CREATE OR REPLACE FUNCTION update_sum() RETURNS TRIGGER AS 
$$BEGIN
 IF (TG_OP = 'DELETE') THEN
    UPDATE total_expense SET total_amount = total_amount - OLD.amount;
 ELSIF (TG_OP = 'UPDATE') THEN
    UPDATE total_expense SET total_amount = total_amount - OLD.amount   NEW.amount;
 ELSIF (TG_OP = 'INSERT') THEN
    UPDATE total_expense SET total_amount = total_amount   NEW.amount;
 END IF;
RETURN NULL;
END
$$
LANGUAGE plpgsql;

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

CodePudding user response：

ON CONFLICT Clause handles "insert if record is not there, update if record is there" situation. Beware updating the same record in concurrent transactions will cause lots of lock. I won't recommend a database design like this. Instead you can use views or materialized views.

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;

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

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

db fiddle here