Re : Update columns in same table from update trigger?

Hi Alban, thank's for your reply. I already changed it as you wrote, but I'm still having the problem that the trigger won't execute unless the value specified for idcolor in table warehouse does exist. Shouldn't the trigger execute BEFORE the update process?

Regards,
Pablo

________________________________
De : Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl>
À : Pablo Romero Abiti <pabiti(at)yahoo(dot)com>
Cc : "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Envoyé le : Samedi 23 Juillet 2011 3h59
Objet : Re: [GENERAL] Update columns in same table from update trigger?

On 22 Jul 2011, at 22:02, Pablo Romero Abiti wrote:

> The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse.
>
> Here's my code:
>
> CREATE OR REPLACE FUNCTION update_warehouse() returns "trigger" AS '
>    declare idcolmaestro float:=0;
>    BEGIN
>      select into a idcolor1 from color_eq where idcolor2=old.idcolor;

You didn't declare 'a'. Perhaps you meant to use idcolmaestro?

>      if a is null then

Try 'if NOT FOUND' here instead.

>          a=old.idcolor;
>      end if;
>
>      new.idcolor=a;
>      return new;
>  END;
> ' LANGUAGE 'plpgsql' VOLATILE;

I'd probably change this code a bit so that the assignment to idcolor only takes place if a value with idcolor2=old.idvalue was found:

    if FOUND then
        new.idcolor := a;
    endif

    return new;

That saves a few unnecessary CPU cycles.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.

!DSPAM:1288,4e2a8d7112091115917480!