Re: 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.idvaluewas 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:737,4e2a8d6912091302916282!