>
> 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
Could you please show the definition of table "budget".
Not sure what you're trying to achieve by these SELECT INTO ...
statements.
Regards, Christoph
