SQLQueryMaster.Open; // need to open Master query so that it is visible toThat's not true. Your detail record is a part of your master record (same id in master / detail). The master table ia already open. This procedure doesn't do anything.
// detail query
You are doing to much. After post this is only what you need
procedure TdmData.SQLQueryMasterAfterPost(DataSet: TDataSet); begin SQLQueryMaster.ApplyUpdates(); SQLTransaction1.Commit; end; procedure TdmData.SQLQueryDetailAfterPost(DataSet: TDataSet); begin SQLQueryDetail.ApplyUpdates(); SQLTransaction1.Commit; end; QuoteSQLQueryMaster.Open; // need to open Master query so that it is visible toThat's not true. Your detail record is a part of your master record (same id in master / detail). The master table ia already open. This procedure doesn't do anything.
// detail query
I think you save problems with the transactions. Use separate transactions or use option 'read committed'
Where did you get that information of posting a record?
Don't do a commit. Do a commitretaining.Code: [Select]SQLTransaction1.Commit;
I have spent days now on this without success but I feel it is something very basic. Have I got the code in the right place - the SQLQuery AfterPost event?Yes, you have it in the right place. So there must be a problem elsewhere in your code. The reason for a stripped down version is that you can place the complete test project here. (With a project > publish project and zip the resulting directory)
CommitRetaining commits and reopens a new transaction automatically which for read only select query is pointless and wastes server resources.If you want to commit something you're not dealing with "read only" select queries :D
the transaction is directly activated again when you browse the dataset.
Are you saying that if for example a onScroll event happens that the transaction is opened again? I hope this is not the case.Yes, I just tested it with a small project (see attached).
Any SQL Statement starts a transaction in Oracle.http://stackoverflow.com/a/4488944/1037511
Error: attempt to implicitly start a transaction on Connection "SQLite3Connection1", transaction "SQLTransaction1".
Operation cannot be performed on an active dataset
So you would always have a transaction open, even for just reading.Yes you must open a transaction for each query or set of queries. It is best practice to close it when your finished using it as open idle transactions do use database server resources. Databases engines usually consider long running idle transactions to be a client side bug. I'm guessing that a onScroll event triggers a database refresh which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.
... which means that I'm going to have to put Commit's into the afterScroll events to prevent these idle transactions.Or .Rollback. No need to commit if you haven't changed anything.