Ad

How Can I Create A Trigger That Updates Data From Another Table In Postgresql?

- 1 answer

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$;
Ad

Answer

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
Ad
source: stackoverflow.com
Ad