At 09:27 AM 26/05/2003 +0200, Wojtek wrote:

>Sorry, this is next try to solve my problem.
>I have problem in bad calculations inside trigger.
>We use:
>Server : Linux RedHat 7.2, IB 6.0.1, on 1.2GHz PIII, 512MB RAM, 2xHDD SCSI,
>GDB size about 600MB, 15 connected users, many transactions every hour,
>
>trigger after insert have to update cost of item table m9003 with data from
>same table and table to which insert is pending,
>so if it can't calculate properly all is wrong, tragedy etc.
>
>trigger body (partial):
>
>CREATE TRIGGER NG90035 FOR G90035
>AFTER INSERT
>POSITION 0
>AS
>DECLARE VARIABLE STANW DECIMAL(18,3);
>DECLARE VARIABLE STANI DECIMAL(18,3);
>DECLARE VARIABLE CENANOWA DECIMAL(18,3);
>DECLARE VARIABLE CENASTARA DECIMAL(18,3);
>BEGIN
>IF (NEW.STRONA=1) THEN
>BEGIN
> SELECT SP_WI+ON_WI-ON_MI,(SP_WI+ON_WI-ON_MI)*CENA,CENA FROM M9003
>WHERE
> M9003.INDEKS=NEW.INDEKS INTO :STANI,:STANW,:CENASTARA;
> IF (STANI+NEW.ILOSC>0.0) THEN
>
>CENANOWA=(STANW+NEW.ILOSC*NEW.CENA)/(STANI+NEW.ILOSC); /*
>here!!!*/
> ELSE
> CENANOWA=NEW.CENA;
> UPDATE M9003 SET ON_WI=ON_WI+NEW.ILOSC,CENA=:CENANOWA WHERE
>M9003.INDEKS=NEW.INDEKS;
>END
>..............
>rest is not interesting
>
>So:
>in line /* here!!!*/
>calculation of variable CENANOWA failed - after calculation
>NEW.ILOSC*NEW.CENA is equal to 0, really it isn't true becouse if I post
>this values into control table - everytime are not 0 both (they can't be 0).
>Values are Null protected.
>Trigger works good few days. In one day it fails (calculation new.ilosc *
>new.cena ) and from this time fails often. Backup od GDB, computer restart
>(server) helps, so it works good next few days (or few hours).
>
>I do everything what I can imagine, change trigger text on many ways and
>nothing can solve problem.
>
>Any suggestions very welcome, here, or private szef@...

This is almost certainly a precision problem involving rounding on results
which are less than 1. Tell us more about all of the *database* data types
involved in the calculation, and someone will be able to show you why you
are getting zero for the results.

Also, it is not clear from your code whether, in the dividend expression,
you expect the addition or the multiplication to be performed first. Add
some brackets to make this clear.

Are you getting division-by-zero errors? If not, then in what way do the
calculations "fail"?

heLen