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:
BL_YEAR: Year
BL_MONTH: Month
ACCOUNT_ID: Account Id
BALANCE: Balance of this account

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

try
Start Transaction;

insert into TR_HEADER (ID,TR_DATE,...) values
(IdHeader,TransDate,...);

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

commit
except
Rollback;
end

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
http://bpranoto.tripod.com