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!
