Subject Seeing uncommited new record of other table inside the trigger
Author Bambang P
Dear all,

I am new in this mailing list.

I hope somebody can give me some enlightment. Here is the case:

Say I have 3 tables in my database:

TR_HEADER: Transaction Header tables with fields:

ID: Record Id, Primary Key
TR_DATE: Transaction date
... other fields

TR_DETAIL: Transaction Detail tables with fields:
ID: Record Id, Primary Key
HEADER_ID: Foreign key to TR_HEADER
ACCOUNT_ID: Account Id
VALUE: transaction value
... other fields

MONTHLY_BALANCE: Accounts monthly balance with fields:
ACCOUNT_ID: Account Id
BALANCE: Balance of this account

The inserting is happens in the program application (Delphi + Zeos)
like this pseudocode:

Start Transaction;

insert into TR_HEADER (ID,TR_DATE,...) values

for each detail record:
insert into TR_DETAIL ( HEADER_ID,...) values (IdHeader,...);


Note: all of the above insert statements live on same connection.

I set an After Insert trigger on TR_DETAIL so whenever a record is
inserted into TR_DETAIL, the MONTHLY_BALANCE will be updated base on
the date of the transaction like this

Get the transaction date from TR_HEADER
select TR_DATE from TR_HEADER
where ID=New.HEADER_ID into :TrDate;

(code to update or insert MONTHLY_BALANCE based on TrDate
and the new fields).

However, the select statement above fails to get the value of the
newly (but uncommited) inserted record of TR_HEADER table.

I read in the documentation, that the trigger's code lives in the same
transaction context of the calling program, so I suppose the above
select statement should be able to see the new uncomitted inserted
record of TR_HEADER, but it is not.

Do I meet a bug or do I miss some thing?

Thanks and accept my apology in case my poor English fails describing
the problem well.

Bambang P