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));
>
> 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.
>
> Roger Vellacott
> Passfield Data Systems Ltd
>