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