Re: Update columns in same table from update trigger?

От: Sim Zacks
Тема: Re: Update columns in same table from update trigger?
Дата: ,
Msg-id: 4E2BEBAD.3080906@compulab.co.il
(см: обсуждение, исходный текст)
Ответ на: Update columns in same table from update trigger?  (Pablo Romero Abiti)
Список: 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 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!
You have 2 choices:
1) Use a before insert trigger and if the row exists, change it to an update.
2) Use a rule

Sim

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

От: Chris Travers
Дата:
Сообщение: Re: Implementing "thick"/"fat" databases
От: Chris Travers
Дата:
Сообщение: Re: Implementing "thick"/"fat" databases