Helen, thanks for your response.

Problem solved I think. The "unreliable" field needed to be excluded
from the update SQL on a client query. The value updated by the
trigger was being inadvertently overwritten by the client app.

This is not the first time this gotcha has got me.


Roger Vellacott
Passfield Data Systems Ltd.



--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...>
wrote:

>
> At 06:57 PM 13/10/2007, you wrote:
> >Typo in previous example.
> >
> >Table B should be defined as
> >
> >CREATE TABLE TABLE_B
> >(BID INTEGER,
> >BF1 INTEGER,
> >BF3 INTEGER)
> >
> >Roger Vellacott
> >
> > >
> > > We are getting some unreliable results when one table updates
> >another
> > > table using a computed field in a trigger. Simultaneous updates
> > > using non-computed fields seem to work ok.
> > >
> > > For example
> > >
> > > CREATE TABLE TABLE_A
> > > (AID INTEGER,
> > > BID INTEGER,
> > > AF1 INTEGER,
> > > AF2 INTEGER,
> > > ACOMP COMPUTED BY (AF1 + AF2));
>
> At the Before Update stage, the value of ACOMP would be the sum of
> OLD.AF1 + OLD.AF2, wouldn't it?
>
> > >
> > > CREATE TABLE TABLE_B
> > > (BID INTEGER,
> > > BF1 INTEGER,
> > > BCOMP INTEGER)
> > >
> > > CREATE TRIGGER TABLE_A_BU FOR TABLE_A BEFORE UPDATE
> > > AS
> > > BEGIN
> > > UPDATE TABLE_B SET
> > > BF1 = BF1 - OLD.AF1,
> > > BF3 = BF3 - OLD.ACOMP
> > > WHERE BID = OLD.BID;
> > > UPDATE TABLE_B SET
> > > BF1 = BF1 + NEW.AF1,
> > > BF3 = BF3 + NEW.ACOMP
> > > WHERE BID = NEW.BID;
> > > END
> > >
> > > TableB.BF1 is reliable.
> > > TableB.BF3 is not reliable.
> > >
> > > This is not the result of Nulls. The above example is greatly
> > > simplified, and the real code protects all values using

coalesce -

> > > e.g. BF1 = COALESCE(BF1,0) + COALESCE(NEW.AF1,0).
> > >
> > > I would be interested if anyone has experience of this, or

whether

> > > there is a general rule that one should not embed computed

fields

> > > within triggers.
>
> Not a problem if you follow the correct sequence of

events. "Before"

> events happen *before* the new record version is written; "After"
> events happen *after*. Use Before triggers for self-adjustments,
> use After triggers for actions on other tables.
>
> ./heLen
>