Tuesday, 15 June 2004, 10:10 PM
Hi Helen,

okay, I started a simple app from scratch that implements the
required behaviour. The jist of the app is as follows:

- Main screen with browse grid linked to a TIB_Query that has its
IB_Transaction property set to "tranBrowse".
- A second TIB_Query, "sqlEdit" that is linked to the transaction, "Tran"
- A third TIB_Query, "sqlEditDetail" that has its master source set
to "sqlEdit" and is linked to the same transaction, "Tran".
- Uppon Adding a new record, the following code is executed:

dmMain.Tran.StartTransaction;
try
dmMain.sqlEdit.MasterSource := nil;
if not dmMain.sqlEdit.Prepared then
dmMain.sqlEdit.Prepare;
dmMain.sqlEdit.Append;
try
frmEdit := TfrmEdit.Create(Self);
try
frmEdit.Mode := 0;
frmEdit.ShowModal;
finally
frmEdit.Free;
end;
finally
dmMain.sqlEdit.Close;
end;
except
dmMain.Tran.Rollback;
raise;
end;

- In the OnCreate event in TfrmEdit, the "sqlEditDetail" query is
opened with sqlEditDetail.Open;
- There is a grid in TfrmEdit that is linked to the sqlEditDetail
query.
- To Cancel an addition of a record, Tran.Rollback is called from
the TfrmEdit form.

If I DON'T use the sqlEditDetail query, then the rollbacks
appear to work correctly and the number sequence stays unbroken.
However, as soon as I add a record to that Detail grid, then
Rollback the transaction, the number sequence gets broken. No
records are added, so the rollback occurs correctly. However, it
appears the sqlEditDetail query causes the sqlEdit query to
perform a Post and hence cause the BEFORE INSERT trigger to fire
thus causing the Generator to increment by one.

I have verified this behaviour by adding a manual sqlEdit.Post
before a Rollback and the same behaviour occurs.

So, the next step seems to be to either prevent the sqlEditDetail
dataset from calling Post on sqlEdit, or to move the BEFORE INSERT
trigger somewhere else where it wont be fired until the
transaction is committed.

Any suggestions?
Do the DMLCacheFlags have anything to do with preventing a post
being done until just before a commit?

I'm really at a loss with this one, but I'm sure this scenario
isn't unique...heck, every business invoices at least! :(

If I ever get it sorted I'll be submitting this example app :)

Any help appreciated...from anyone.

thanks
Mark