How can I create a trigger that updates data from another table in postgresql?

Question

I have 2 tables "sellings" and "products". The table "produts" has an id column and an amount column. I want to create a trigger that when I insert a new sell in "sellings" it removes 1 from "amount" in "products". How can I do it?

My trigger function:

CREATE OR REPLACE FUNCTION public.sell()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    VOLATILE
    COST 100
AS $BODY$
BEGIN
   NEW."amount" := product.amount - 1;
   RETURN NEW;
END
$BODY$;

Nabil · Accepted Answer · 2022-01-22 22:33:01Z

1

Here a link to documentation.

CREATE OR REPLACE TRIGGER  update_product_on_selling AFTER INSERT ON  sellings EXECUTE  public.sell();

Here an extract from the documentation

CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

answered Jan 22, 2022 at 22:33

Nabil

1,3088 silver badges12 bronze badges

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

3 Comments

Theresa Harmony Over a year ago

but how can i do so when i insert data in a table it updates a diferent table?

Nabil Over a year ago

You need to write an update SQL query in your function . This link might help you

Theresa Harmony Over a year ago

it worked!! thank you

Collectives™ on Stack Overflow

How can I create a trigger that updates data from another table in postgresql?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related