I am having a little difficulty with transaction rollback on inserting a new record into a table. When I try inserting a new sales order item record, as detailed below, with the chosen stock record already locked by another user, then everything works more or less as I would expect and an EDB_ERROR_LOCKROW exception is raised. Except that when the transaction is rolled back, the sales order item table does not rollback to dsInsert but instead ends up in dsBrowse state. Am I wrong in thinking this should roll back to exactly the state it was in before the start of the transaction?

Any help would be much appreciated.

Many thanks,
Ian
 

Example Code ---

table.insert
Open form and enter data into new record
Press Save button, which runs the following function -

function TfmSalesOrderItemEdit.WriteOrderItemRecord: boolean;
var
 ElevateTables: TEDBStringsArray;
begin
 with dmAccounts, dmCustomer, edbdAccounts, taSalesOrderItem do
 begin
   if state in [dsInsert, dsEdit] then
   begin
     SetLength(ElevateTables,3);
     ElevateTables[0]:='acSalesOrderItem';
     ElevateTables[1]:='acStock';
     ElevateTables[2]:='acStockHistory';
     StartTransaction(ElevateTables);
     result:=false;
     try
       post; //post sales order item
       WriteStockAllocation;  //update stock allocation
       commit;
       result:=true;
     except
       on E: Exception do
       begin
         rollback;
         if (EEDBError(E).ErrorCode=EDB_ERROR_LOCKROW) then
           begin
             StayInForm:=true;
             acsErrMes(EEDBError(E).ErrorMsg);
             //exit;
           end else begin
             acsErrMes(EEDBError(E).ErrorMsg);
             abort;
           end;
         end else begin
           raise;
         end;
       end;
     end;
   end;
 end;
end;

Ian,

<< I am having a little difficulty with transaction rollback on inserting a
new record into a table. When I try inserting a new sales order item record,
as detailed below, with the chosen stock record already locked by another
user, then everything works more or less as I would expect and an
EDB_ERROR_LOCKROW exception is raised. Except that when the transaction is
rolled back, the sales order item table does not rollback to dsInsert but
instead ends up in dsBrowse state. Am I wrong in thinking this should roll
back to exactly the state it was in before the start of the transaction?  >>

Yes.  It's not possible for EDB to put the TDataSet descendants
(TEDBTable, TEDBQuery, TEDBScript, TEDBStoredProc) back into their previous
state.  If you think about it a bit, you could see why (hint: edit mode on a
record that no longer exists).

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com