---------- Original Message ----------- From: "hanszorn2000" <[email protected]> > > > > > > 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
