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 <pabiti@yahoo.com>)
Список pgsql-general

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 по дате отправления:

Предыдущее
От: Alban Hertroys
Дата:
Сообщение: Re: Re : Update columns in same table from update trigger?
Следующее
От: Gavin Flower
Дата:
Сообщение: Re: Implementing "thick"/"fat" databases