Re: Re : Update columns in same table from update trigger?

On 23 Jul 2011, at 16:49, Pablo Romero Abiti wrote:

> 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?

Ah of course, there is no matching row for the update that you expect to fire the trigger, so it never gets fired (no update takes place). I hadn't realised that from your examples.

You can't fire a trigger on non-existant data.

> 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.
>
>
>
>
>
>
>
>

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4e2bdf4512091401616986!