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

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

I suppose there's the possibility of index corruption. You could test that by
making sure the optimizer can't use the index:

and (ARTICLE.ISACTIVE || '' = 'F')

or something similar. The only time I've seen an index return incorrect results
was when I indexed a COMPUTED BY expression that depended on CURRENT_DATE. (A
terrible idea, by the way, but I was just messing around.) There are index-
related bugs in JIRA, but they're relatively rare, and it'd be odd for it to be
there in 2.0, not 2.1, and be back in 2.5...

For testing purposes, could you add .ISACTIVE to the list of return-columns
inside your trigger, test its value right after the select, and raise an
exception if it's 'T' when it shouldn't be?

You could also send us the non-simplified version of the trigger, as it could be
a problem that only appears in the complex version, possibly unrelated to your
core logic. A typo, even.

-Philip