Thanks Helen for pointing me in the right direction.....all works fine now.

regards

Bill Zwirs

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> 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
>