How can I create a trigger that updates data from another table in postgresql?
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$;
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