I seem to be having difficulty creating a trigger. I have creted the
function and tested it which seems to work fine: -
CREATE FUNCTION get_prod_cost_price (varchar, int8) RETURNS float AS '
DECLAREcost FLOAT;
BEGINSELECT cost_price INTO cost FROM product WHERE code = $1;
IF FOUND THEN UPDATE order_detail SET cost_price = cost WHERE order_detail_id=$2; RETURN cost;END IF;RETURN 0;
END;
' LANGUAGE 'plpgsql';
dvd=> select get_prod_cost_price ('DVD368', 10027);
get_prod_cost_price
------------------- 9.81
(1 row)
Now I try and create the approprate trigger and I get the following:-
CREATE TRIGGER tg_update_order_detail AFTER insert ON order_detail FOR EACH ROW EXECUTE PROCEDURE
get_prod_cost_price('product_id','order_detail_id');
ERROR: CreateTrigger: function get_prod_cost_price() does not exist
It is clear that it does exist so why does the trigger creation code not
find it?
Thanks in advance for any pointers.
Graham