CODEWITHSUNDEEP
sqlpostgresqltriggers
user16123142
user16123142

Reputation:

Trigger function not working in postgresql

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?

Upvotes: 0

Views: 2315

Answers (1)

Serg
Serg

Reputation: 22811

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

Upvotes: 1

Related Questions