From: | jclaudio(at)capitol(dot)fr |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | problem with function trigger |
Date: | 2004-01-13 13:42:37 |
Message-ID: | OF2ACB9FBF.6F44ED00-ONC1256E1A.004B487C-C1256E1A.004B5022@capitol.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hi I'm trying to update a table column with a pl/pgsql function and a
trigger.
But I didn't managed to make it work so far.
Here's my function code :
CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '
DECLARE
id_line integer;
quantity integer;
single_price real;
total_cost real;
amort integer;
month integer;
impact real;
BEGIN
SELECT INTO id_line id_line_table FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO single_price single_price_previ FROM table WHERE id_line_table
= NEW.id_line;
SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO quantity quantity_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO month month_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO impact impact_previ FROM table WHERE id_line_table =
NEW.id_line;
IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0;
ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort);
ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:=
total_cost;
ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12
- month)*(total_cost/(amort*12)));
END IF;
END IF;
END IF;
END IF;
IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN
UPDATE table SET impact_previ = impact WHERE id_line_table =
NEW.id_line;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget
FOR EACH ROW EXECUTE PROCEDURE calcul_impact();
I always get the error :
Error SQL :
ERROR: record "new" has no field named "id_ligne"
Has anyone an idea about what's wrong ?
thanks for answering me
From | Date | Subject | |
---|---|---|---|
Next Message | beyaRecords - The home Urban music | 2004-01-13 14:05:08 | Problems with postgresql 7.4.1 configuration - URGENT |
Previous Message | jclaudio | 2004-01-13 11:00:01 | problem with function trigger |