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