problem with function trigger - Mailing list pgsql-sql
| From | jclaudio@capitol.fr |
|---|---|
| Subject | problem with function trigger |
| Date | |
| Msg-id | OFA0F7ADE2.0E9D945D-ONC1256E1A.003C5030-C1256E1A.003C6D4D@capitol.fr Whole thread Raw |
| List | pgsql-sql |
<br /><font face="sans-serif" size="2">Hi I'm trying to update a table column with a pl/pgsql function and a trigger. </font><br/><font face="sans-serif" size="2">But I didn't managed to make it work so far.</font><br /><br /><font face="sans-serif"size="2">Here's my function code :</font><br /><br /><font face="sans-serif" size="2">CREATE FUNCTION public.calcul_impact()RETURNS opaque AS '</font><br /><br /><font face="sans-serif" size="2">DECLARE </font><br /><font face="sans-serif"size="2">id_line integer;</font><br /><font face="sans-serif" size="2">quantity integer;</font><br /><fontface="sans-serif" size="2">single_price real; </font><br /><font face="sans-serif" size="2">total_cost real; </font><br/><font face="sans-serif" size="2">amort integer;</font><br /><font face="sans-serif" size="2">month integer;</font><br/><font face="sans-serif" size="2">impact real;</font><br /><br /><font face="sans-serif" size="2">BEGIN</font><br /><br /><font face="sans-serif" size="2">SELECT INTO id_line id_line_table FROM table WHERE id_line_table= NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO single_price single_price_previ FROMtable WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO total_cost total_cost_previFROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECT INTO quantityquantity_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECTINTO amort amortis FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECTINTO month month_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><font face="sans-serif" size="2">SELECTINTO impact impact_previ FROM table WHERE id_line_table = NEW.id_line;</font><br /><br /><font face="sans-serif"size="2">IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0; </font><br /><font face="sans-serif"size="2">ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort); </font><br /><font face="sans-serif"size="2"> ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:= total_cost; </font><br/><font face="sans-serif" size="2"> ELSE IF(quantity < 12 AND single_price > 500) THEN impact:=((12 - month)*(total_cost/(amort*12))); </font><br /><font face="sans-serif" size="2"> END IF;</font><br/><font face="sans-serif" size="2"> END IF;</font><br /><font face="sans-serif" size="2"> END IF;</font><br/><font face="sans-serif" size="2">END IF;</font><br /><br /><font face="sans-serif" size="2">IF (TG_OP =''INSERT''OR TG_OP=''UPDATE'') THEN </font><br /><font face="sans-serif" size="2"> UPDATE table SET impact_previ= impact WHERE id_line_table = NEW.id_line; </font><br /><font face="sans-serif" size="2">END IF; </font><br/><br /><font face="sans-serif" size="2">RETURN NEW; </font><br /><br /><font face="sans-serif" size="2">END;</font><br/><br /><font face="sans-serif" size="2">' LANGUAGE 'plpgsql';</font><br /><br /><font face="sans-serif"size="2">CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget FOR EACH ROW EXECUTE PROCEDUREcalcul_impact();</font><br /><br /><font face="sans-serif" size="2">I always get the error :</font><br /><br /><fontface="sans-serif" size="2">Error SQL :</font><br /><font face="sans-serif" size="2">ERROR: record "new" has no fieldnamed "id_ligne"</font><br /><br /><font face="sans-serif" size="2">Has anyone an idea about what's wrong ?</font><br/><br /><font face="sans-serif" size="2">thanks for answering me</font>