Originally you wrote:

> >I use firebird 0.9x ss on Redhat 7.2
> >
> >There is a before insert trigger which I use to update a particular
> >serial number and returns a value. At times it returns a null value
> >and I am forced to drop and recreate the trigger to get it working.
> >
> >any clues ?

I wrote:

> Triggers can't return values. So show us your trigger and explain what you
> are expecting.

At 07:36 AM 4/08/2004 +0000, you wrote:

>Here is goes..
>
>This code had to be re generated thrice during the last six months.
>
>SET TERM ^ ;
>
>ALTER TRIGGER DBTRINSERTRETURN
>ACTIVE BEFORE INSERT POSITION 0
>AS
> DECLARE VARIABLE MAXVALUE SMALLINT;
>BEGIN
> IF (EXISTS(SELECT SERIALNUMBER FROM SALESRETURNDETAILS WHERE
> SALESRETURNNUMBER = NEW.SALESRETURNNUMBER) )THEN
> SELECT MAX(SERIALNUMBER) FROM SALESRETURNDETAILS WHERE
> SALESRETURNNUMBER = NEW.SALESRETURNNUMBER INTO :MAXVALUE;
> ELSE
> MAXVALUE = 0;
> NEW.SERIALNUMBER = MAXVALUE + 1;
>END
>
>^
>
>SET TERM ; ^

Right, so your trigger doesn't return any values after all. :-)

Your problem?...OK, you have fallen into two logic traps here. The first
is that, because this is a trigger, the EXISTS() predicate will *always*
return true if there is anything other than null in new.salesreturnnumber
AND it is the first time this salesreturnnumber has been used. Yes, the
trigger can see its own row, so MAXVALUE will always be null in these
cases. Execution will branch into the ELSE block only in cases where
new.salesreturnnumber is null (because null doesn't match anything,
including null) or there is already at least one record with a matching
salesreturnnumber and a non-null serialnumber.

In other words, it isn't safe to make decisions in a Before Insert trigger
that depend on the wrong assumption that the query can't see its own row.

You're wasting your time "regenerating the code" to try to fix the
problem. The code logic is wrong and merely regenerating it will never
make it right.

The other piece of the double trap is a multi-user issue. In the cases
where you do actually manage to branch into the ELSE block, the max value
of serialnumber that your transactions sees can not be guaranteed to be the
same as what other transactions see. Another transaction could increment
the serialnumber on the same row *after* your transaction starts and you
will both post the same value, or your transaction will duplicate someone
else's value.

Don't use this way to increment serial numbers or keys in multi-user
systems, otherwise you're just going to keep getting duplicate serial numbers.

/heLen