Creating a trigger that can add value added in table1 to table2

user_12412882 0
Viewed 62

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:

PID amount
1 100
total_amount
100

If you add a new row to purchases:

PID amount
1 100
2 200

then update Total_Expense

total_amount
300

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!

triggers postgresql sql
4 Answers

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.

user_90693090

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.

user_23020320

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();
user_33993560

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;
pid amount
1 100.00
id total_amount
1 100.00 
insert into purchases values (2,200);
select * from purchases;
select * from total_expense;
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;
pid amount
2 200.00
id total_amount
1 200.00

db fiddle here

user_35576760
Related