At 05:34 AM 19/10/2007, you wrote:

> delphi 7.1 with interbase express 7.08 application or firebird
>itself.
>a detail table is updating a value in
>the master table. the field happens to be an accrual or summation
>floating-point field. if i insert a new record in the detail table,
>its triggers will automatically update the accrual value in the field
>of the master table. using the VARS table, i have been able to find
>out that when the detail table has a new insert, it is posted, and
>then hard-committed, then say an unrelated field is changed and posted
>in the master table, everything works fine.


>the times that the
>accrual field is not properly updated is when the hard-commit is not
>performed before changing the unrelated field in the master table. if
>i simply post the insert in the detail table, then immediately change
>the unrelated field in the master, then the accrual field is not
>updated appropriately.

>however, the when the hard-commit is not
>performed the NEW value in the master table reverts to the previous
>instead of the current value.

The trigger (on the detail record) fires once
when THAT record is inserted and updates the
master at that point (creates a new record
version for master). Previous record version is
"parked", no longer accessible by your transaction.

Meanwhile, you make another update to the master
"to an unrelated field", as you put it. In your
Delphi application, you are looking at a copy of
the extinct record version (the one that has
already been replaced by the detail's trigger,
i.e. contains the old summary value).

In Fb/IB, there is no field-level updating: as
soon as you call Post from your Delphi app, your
Delphi component sends a statement that updates
all the fields using the current values in the
Delphi dataset's buffer - including this old
summary field value. Another new record version
is created on disk and the recversion created by
the detail's trigger gets moved out. That's how
the value written by the detail's trigger gets blitzed.

>upon inspection of the VARS table, which
>receives messages from the triggers, it is apparent that the triggers
>are firing just fine.

> just guessing, but perhaps in the detail trigger
>reading the current value from the master table using a SELECT is
>reading only the committed values instead of the value in the current
>transaction. it is only by doing the hard-commit prior to changing
>the unrelated field in the master will the proper value be read
>because the hard-commit causes it to read the value off the hard-drive.

Don't be confused about "commit". Commit
(whether "soft" or "hard") causes your record
versions (till now seen only by your transaction)
to be made permanent and thus visible to other
transactions that start after that commit (or, in
the case of other transactions that are in Read
Committed isolation, visible next time those transactions touch that record).

"Soft" commit is CommitRetaining, that is widely
used in Delphi (and usually in the wrong way, causing problems).

"Hard" commit is a clean COMMIT call.

Commit is a transaction-level operation.

The Post method of Delphi writes the values of
the current row buffer to an entire new record
version on disk (as described earlier) EVERY TIME IT IS CALLED.

>ok, that said, i hope that made sense and can anyone offer any
>suggestions. perhaps i need to change some settings in the trigger
>configuration.

If your application workflow has to allow users
to update the master record after operations have
been posted from the detail records then take
that update right out of the detail record's insert trigger.

Review your design. When you build in hard
dependencies like this, overlapping transactions
can walk over one another's changes if
transaction management is either not clearly
understood by the programmer or not well
supported by the data access layer. What's more,
you can't prevent someone using some utility to
totally corrupt the value that *you* intended to
be maintained only by summing the details.

If you absolutely MUST accrue the detail values
into the master as hard data (not recommended!)
then do it with an After Update trigger on the
master. Don't take that as a solution to
the problem of database consistency: it merely
addresses the problem you are complaining
about. Don't let that summary field be updatable
from any client application - make it read-only
and write custom UpdateSQL to prevent the
application trying to update it. Just be aware
that, if your system depends on this summary
total anywhere, then you have a design flaw that
will not pass any external software quality assurance consistency tests.

Did you know you can define a COMPUTED BY field
on your master table using a correlated subquery
on the related detail records? If you have to
have this field at all, at least it will be
consistent on the database side. (Firebird
throws an exception if a request attempts to
update a COMPUTED BY field.) It won't solve the
consistency problems inherent on the client side
where the application workflow is not responsive
to its own changes....understand well that IBX
does not provide any mechanism to refresh a
master record in the buffer when your detail
record's triggers modify the real record at the
server. You have to take care of that yourself by
refreshing, i.e., closing the master dataset and re-opening it.

This isn't a Delphi forum. If you need to
understand more about how IBX works, various
Borland Team B members have published how-tos
around the web. You could try googling "Marco Cantù" as a start....

^ heLen