From: | Pablo Romero Abiti <pabiti(at)yahoo(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Update columns in same table from update trigger? |
Date: | 2011-07-22 20:02:46 |
Message-ID: | 1311364966.80332.YahooMailNeo@web161612.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Radosław Smogura | 2011-07-22 21:05:07 | Re: Why do I have reading from the swap partition? |
Previous Message | Scott Marlowe | 2011-07-22 19:24:59 | Re: Why do I have reading from the swap partition? |