Обсуждение: 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!

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 <dalroi@solfertje.student.utwente.nl>
> À : Pablo Romero Abiti <pabiti@yahoo.com>
> Cc : "pgsql-general@postgresql.org" <pgsql-general@postgresql.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
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!

On 24 Jul 2011, at 11:00, Alban Hertroys wrote:

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


I just realised the above statement is a little inexact: you can't fire row-level triggers on non-existant data.

You can still fire statement-level triggers on them, but with those you won't have information about which row(s) were
involved,so it won't help your case. 

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,4e2be09d12091863320979!

On 07/22/2011 11:02 PM, Pablo Romero Abiti wrote:

Here's what I want to do:

I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is equivalent to idcol1

Table: color_eq

idcol1  idcol2

1          1

2          2

2          3

Table: warehouse

idcol    qty

1        10

2        20

if I execute "update warehouse set qty=10 where idcolor=3", I want the trigger to search table color_eq for idcol2=3, picks its corresponding idcol1 and update the table warehouse with idcol1.

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;
      if a is null then
         a=old.idcolor;
      end if;

      new.idcolor=a;
      return new;
  END;
' LANGUAGE 'plpgsql' VOLATILE;

  CREATE TRIGGER update_warehouse_trigger
  before UPDATE
  ON warehouse
  FOR EACH ROW
  EXECUTE PROCEDURE update_warehouse();

Any help would be greatly appreciated!