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