Re: Update columns in same table from update trigger?

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!