---------- Original Message -----------
From: "hanszorn2000" <hanszorn@...>

> > > I am wondering if there are any known issues with queries within a

trigger, when that query accesses the table on which the trigger fires (before
insert, so no race condition).

> > > I am experiencing unpredictable and even wrong results in such a case, at

least when using FB 2.0 and 2.5. With 2.1 I have not experienced this, so far.

> No problem! This is a simplified version of a trigger that fires
> Before Insert on table ARTICLE:
>
> declare LOCATION integer;
> declare ARTICLEID varchar(20);
> BEGIN
> select first 1 NEWLOCATION, ARTICLEID from ARTICLE
> where ARTICLE.CUSTID = NEW.CUSTID
> and ARTICLE.ISACTIVE = 'F'
> and ARTICLE.NEWLOCATION is not null
> order by NEWLOCATION
> into :LOCATION, :ARTICLEID;
>
> NEW.NEWLOCATION = LOCATION;
> end
>
> This then (sometimes) assigns values of NEWLOCATION for Articles with
> ISACTIVE = 'T'.

------- End of Original Message -------

Here's my reversed-engineered documentation for the order of operations of
Firebird triggers (in the context of other stuff going on):

http://pseudotheos.com/view_object.php?object_id=1619

In your case, I'd be worried about:
- default value of :LOCATION when "first 1" finds none
- concurrent transactions (you're sure the trigger would have known those
articles were active?)
- are you ever bulk-inserting articles that are all for the same customer? (a
bulk-insert operation may do its job in any order, but the trigger for each row
as it happens, weird things could happen)

-Philip