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

От: Alban Hertroys
Тема: Re: Re : Update columns in same table from update trigger?
Дата: ,
Msg-id: BED2C7B4-D18D-4EF6-8C7E-691424A9BF88@solfertje.student.utwente.nl
(см: обсуждение, исходный текст)
Ответ на: Re : Update columns in same table from update trigger?  (Pablo Romero Abiti)
Ответы: Re: Re : Update columns in same table from update trigger?  (Alban Hertroys)
Список: pgsql-general

Скрыть дерево обсуждения

Update columns in same table from update trigger?  (Pablo Romero Abiti, )
 Re: Update columns in same table from update trigger?  (Alban Hertroys, )
  Re : Update columns in same table from update trigger?  (Pablo Romero Abiti, )
   Re: Re : Update columns in same table from update trigger?  (Alban Hertroys, )
    Re: Re : Update columns in same table from update trigger?  (Alban Hertroys, )
     Re : Re : Update columns in same table from update trigger?  (Pablo Romero Abiti, )
 Re: Update columns in same table from update trigger?  (Sim Zacks, )

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
triggerwon't execute unless the value specified for idcolor in table warehouse does exist. Shouldn't the trigger
executeBEFORE 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
updatetakes place). I hadn't realised that from your examples. 

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

> De : Alban Hertroys <>
> À : Pablo Romero Abiti <>
> Cc : "" <>
> 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
tablewarehouse. 
> >
> > 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.
>
>
>
>
>
>
>
>

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!




В списке pgsql-general по дате сообщения:

От: Sanjay Rao
Дата:
Сообщение: pgpool HA not working
От: Chris Travers
Дата:
Сообщение: Re: Implementing "thick"/"fat" databases