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