Postgres launch function

Question

I need help with Postgres triggers.

I have a table with two columns:

sold boolean; id_shop int; 

It is stored if the goods are sold or in which its store is located.

I need a trigger if I changed the "sold" to true, then it also changed the id_shop value to NULL (it cannot be in the store if it is sold ...)

I tried different ways, but it does not work or gives an error while updating cmd ...

 create or replace function pardota_masina_veikals() RETURNS trigger AS $pardota_masina$ begin IF NEW.sold=true THEN update masinas SET id_shop=null WHERE id=NEW.id; END IF; RETURN NEW; END; $pardota_masina$ LANGUAGE plpgsql; CREATE TRIGGER pardota_masina_nevar_but_veikala AFTER INSERT OR UPDATE ON masinas FOR EACH ROW EXECUTE PROCEDURE pardota_masina_veikals(); 

Answer 1

First of all, you need a trigger before if you want to change the value of the updated row (or inserted)

Secondly, you do not need to “update” the table, just assign a new value to a new row:

 create or replace function pardota_masina_veikals() RETURNS trigger AS $pardota_masina$ begin IF NEW.sold=true THEN NEW.id_shop = NULL; END IF; RETURN NEW; END; $pardota_masina$ LANGUAGE plpgsql; CREATE TRIGGER pardota_masina_nevar_but_veikala BEFORE INSERT OR UPDATE ON masinas FOR EACH ROW EXECUTE PROCEDURE pardota_masina_veikals();