Trigger function not working in postgresql

Question

I have tables as follows:

test

address	value
a1	50

table1

address	amount	id	hash
a1	50	2	se2

I am trying to add the amount to a1 when a new insertion of a1 is added to the test table i.e. if a new row is added to test i.e. address = a1 and value = 100 then, i want to update the amount in a1 on table1 table i.e. amount = 50 + 100 = 150 but the value does not get updated.

Here is my trigger implementation:

CREATE OR REPLACE FUNCTION address_trigger()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    UPDATE table1
    SET amount = amount + new.value
    WHERE NEW.address = table1.address;
    RETURN NEW;
end;
$$;

CREATE TRIGGER update_table1
    AFTER INSERT
    ON "test"
    FOR EACH ROW
EXECUTE PROCEDURE address_trigger();

I added the trigger function via query console and the trigger function shows on the database as update_table1 in intellij. then, i insert a new row to test manually INSERT INTO test(address, value) VALUES ('a1',100);. the row gets updated on test but the value is not updated on table1 on reloading. Could someone help me out, what am i doing wrong here?

Are you sure you are not seeing a search_path issue where there is another table1 that is being updated. Try schema qualifying table1. — Adrian Klaver
– Adrian Klaver, Commented Aug 25, 2021 at 18:37

Serg · Accepted Answer · 2021-08-25 17:03:40Z

1

It works ok for me. Create tables

create table "test"(
  address varchar(10),
  value int
);
create table table1 (
  address varchar(10),
  amount int
);

insert into table1 
   values('abc', 0);

Your trigger

CREATE OR REPLACE FUNCTION address_trigger()
    RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    UPDATE table1
    SET amount = amount + new.value
    WHERE NEW.address = table1.address;
    RETURN NEW;
end;
$$;

CREATE TRIGGER update_table1
    AFTER INSERT
    ON "test"
    FOR EACH ROW
EXECUTE PROCEDURE address_trigger();

Test it

insert into "test"
  values ('abc', 10),
         ('abc', 30);

select * 
from table1;

Output

address amount
abc 40

answered Aug 25, 2021 at 17:03

Serg

22.8k5 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Trigger function not working in postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related