Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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();
share|improve this question

1 Answer

up vote 4 down vote accepted

First of all you need an 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();
share|improve this answer
Thanks, it worked – Martins Svirksts May 9 '12 at 20:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.