Postgres trigger function

postgresql, triggers

Solution

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

Secondly you don't need to "update" the table, just assign the new value to the 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();

Problem

I need help in Postgres triggers. I have table with 2 columns: ``` sold boolean; id_shop int; ``` It stores if item is sold, or at which shop its located at. I need a trigger, if I change the "sold" to true, then it also changes the `id_shop` to `NULL` (It can't be in shop if sold...) I tried different ways, but it doesn't work or gives an error on update 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(); ```

Original source