At 03:35 PM 13/08/2009, you wrote:

>Hi,
>
>I have a Trigger as follows
>
>CREATE TRIGGER BI_SALES_PER_WEEK FOR SALES ACTIVE AFTER INSERT OR UPDATE POSITION 2 AS
>declare variable AVG_PERWEEK NUMERIC(15,2);
>BEGIN
> select AVG_WEEKSALES from GET_WEEKLY_SALES(NEW.MACHID,NEW.SITEID)
> into :AVG_PERWEEK;
> UPDATE MACHSITE
> SET SALESPERWEEK = :AVG_PERWEEK
> WHERE MACHID = NEW.MACHID AND
> SITEID = NEW.SITEID;
>END
>
>I want to call a Stored Procedure named GET_WEEKLY_SALES which returns the AVG_WEEKSALES value. Is this the correct way to call a Stored Procedure from within a Trigger?

No. Write the inner procedure as an executable procedure that returns exactly one row of result (without FOR....DO...SUSPEND). Then invoke this procedure using the PSQL syntax for EXECUTE PROCEDURE:
...
BEGIN
execute procedure GET_WEEKLY_SALES(NEW.MACHID,NEW.SITEID)
returning_values(:AVG_PERWEEK);
...

>I have tested the Stored Procedure in Database Workbench and it gives the correct value, yet the Trigger comes up with Null.

Arithmetic involving a NULL operator returns NULL, regardless of other operators. If you're getting NULL, it's most likely you have not covered the NULL case in some of your calculations. You must test and cover all possible scenarios for your calculations. For example:

...
BEGIN
if (NEW.MACHID IS NOT NULL AND NEW.SITEID IS NOT NULL) then
begin
execute procedure GET_WEEKLY_SALES(NEW.MACHID,NEW.SITEID)
returning_values(:AVG_PERWEEK);
...
end
else
begin
-- do something different
end
...

And also make sure any calcs in the inner procedure are covering possible nulls in values referred to there, of course.

./heLen