--- In firebird-support@yahoogroups.com, "unordained" <unordained_00@...> wrote:

> >
> > 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

Like I wrote the example is simplified. Handling of that situation was omitted.

> - concurrent transactions (you're sure the trigger would have known those
> articles were active?)

The problrm also occurs with only one user (in our test environment).

> - 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)

We sometimes do. But as it also happens with single inserts, I'll worry about those later.

Hans