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!
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