I would like to do the following.

1. Start transaction
2. Do changes in tables in transaction
3. Check if there are table changes in transaction
4. If there are table changes ask for commit or rollback
5. Start a new transaction

The question is how can I check if tables are changed in a transaction?

Mike

>I would like to do the following.
>
>1. Start transaction
>2. Do changes in tables in transaction
>3. Check if there are table changes in transaction
>4. If there are table changes ask for commit or rollback
>5. Start a new transaction
>
>The question is how can I check if tables are changed in a transaction?

I'm slightly puzzled here. Your step 2 indicates that changes are under your control so you should know if changes are made or not. You may need to set a flag but that should be all.

Roy Lambert

There are multiple tables.

Should a flag be set in the OnUpdateRecord event?

Mike

>There are multiple tables.
>
>Should a flag be set in the OnUpdateRecord event?

I've never used that event myself, and looking at the manual

The OnUpdateRecord event is fired when the IProvider support in ElevateDB is attempting to apply an
update from a TClientDataSet component. Write an event handler for this event to intercept an update
before it is applied automatically by ElevateDB. This will allow you to provide custom processing for
situations where the standard update processing is not sufficient.

and looking at the parameters for the event, and a few comments on these newsgroups, I'm not sure its a good one since it looks as though you have to do your own updating then.

I assume you're using clientdatasets (which I never have) and I don't know the sequence of events there. My preference would be the Before or After Post events but you'll have to experiment to see if they're fired.

Roy Lambert

Roy,

Thanks for the information.

I don't use clientdatasets so will check if BeforePost/AfterPost events can handle this.

Mike


I'm going to ask a more fundamental question - why do you care if changes are made or not? Once you start a transaction you really only have two choices to finish it - COMMIT or ROLLBACK. ROLLBACK is generally only called if there's been an error otherwise you call COMMIT.


Roy Lambert

The user which is using the application must have the ability to save or cancel all changes (in all tables).

Mike


>The user which is using the application must have the ability to save or cancel all changes (in all tables).

Personal view: you're going about this the wrong way.

If, within the transaction, you ask the user wether or not they want to commit then the tables involved in the transaction are locked until the user makes a selection. This may be a second or after a cup of coffee and a gab.

My approach would be

1. Determine if there are changes to be made.
2. Ask the user if they want to make those changes
3. If the answer if YES then
    3.1. Start transaction
    3.2. Do changes in tables in transaction
    3.3. Commit or rollback if error
    3.4 If error and rollback notify the user
4. Rinse and repeat

Transactions should be kept as short as possibile and you should never have user input as part of a transaction. On the other hand if you want to lock all other users out whilst this user makes a decision then your approach is fine.

Stock control is the only instance that comes to mind where you may want to do that and there are better ways there.

Roy Lambert

Mike,

<< I would like to do the following. >>

As Roy indicated, you don't want to keep transactions active for very long because of the way that transaction locks work:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Transactions

(Locking During a Transaction)

This means that with multiple users/sessions, you'll end up with locking issues if you have long-running transactions.  Instead, you should only start a transaction *right before* a Post operation in a BeforePost event handler, and then commit/rollback the transaction in the AfterPost/AfterPostError event handlers.  If you're updating more than one table, then you should always handle the transactions at the *lowest* level detail table.

Here's how a master-detail setup with two tables would work in terms of posts:

procedure TMyDataModule.CustomerItemsBeforeDelete(DataSet: TDataSet);
begin
  CustomerOrders.CheckBrowseMode;
  Customers.CheckBrowseMode;
  // Validate deletion, if necessary
  MyDatabase.StartTransaction;
  BeforeQty:=CustomerItemsQuantity.AsInteger;
  BeforePurchaseTotal:=CustomerItemsPurchaseTotal.AsCurrency;
  BeforeShipping:=CustomerItemsShipping.AsCurrency;
end;

procedure TMyDataModule.CustomerItemsAfterDelete(DataSet: TDataSet);
begin
  try
     Customers.DisableControls;
     CustomerOrders.DisableControls;
     try
        // Update any aggregate customer information here
        CustomerOrders.Edit;
        CustomerOrdersPurchaseTotal.AsCurrency:=(CustomerOrdersPurchaseTotal.AsCurrency-
                                                 BeforePurchaseTotal);
        CustomerOrdersShippingTotal.AsCurrency:=(CustomerOrdersShippingTotal.AsCurrency-
                                                 BeforeShipping);
        CustomerOrdersOrderTotal.AsCurrency:=(CustomerOrdersPurchaseTotal.AsCurrency+
                                              CustomerOrdersShippingTotal.AsCurrency);
        CustomerOrdersBalanceDue.AsCurrency:=(CustomerOrdersOrderTotal.AsCurrency-
                                              CustomerOrdersAmountPaid.AsCurrency);
        CustomerOrders.Post;
        MyDatabase.Commit;
     finally
        CustomerOrders.EnableControls;
        Customers.EnableControls;
     end;
  except
     MyDatabase.Rollback;
     raise;
  end;
end;

procedure TMyDataModule.CustomerItemsBeforeInsert(DataSet: TDataSet);
begin
  CustomerOrders.CheckBrowseMode;
  Customers.CheckBrowseMode;
  if (CustomerOrders.RecordCount=0) then
     begin
     MessageDlg('You must add an order first before adding order items',mtError,[mbOk],0);
     Abort;
     end;
end;

procedure TMyDataModule.CustomerItemsPostError(DataSet: TDataSet;
 E: EDatabaseError; var Action: TDataAction);
begin
  MyDatabase.Rollback;
  Action:=daFail;
end;

procedure TMyDataModule.CustomerItemsBeforePost(DataSet: TDataSet);
begin
  MyDatabase.StartTransaction;
  try
     if (not ProductCatalog.FindKey([CustomerItemsProductID.AsString])) then
        begin
        MessageDlg('Product ID specified is invalid',
                   mtError,[mbOk],0);
        Abort;
        end;
     if (not CustomerItemsOfferID.IsNull) then
        begin
        if (not ProductOffers.FindKey([CustomerItemsProductID.AsString,
                                       CustomerItemsOfferID.AsString])) then
           begin
           MessageDlg('Offer ID specified is invalid',
                      mtError,[mbOk],0);
           Abort;
           end;
        end;
     WasInsertingLine:=(CustomerItems.State=dsInsert);
     if WasInsertingLine then
        CustomerItemsLineNo.AsInteger:=CustomerOrdersNextLineNumber.AsInteger;
     if (not WasInsertingLine) then
        begin
        BeforeQty:=CustomerItemsQuantity.OldValue;
        BeforePurchaseTotal:=CustomerItemsPurchaseTotal.OldValue;
        BeforeShipping:=CustomerItemsShipping.OldValue;
        end
     else
        begin
        BeforeQty:=0;
        BeforePurchaseTotal:=0;
        BeforeShipping:=0;
        end;
  except
     MyDatabase.Rollback;
     raise;
  end;
end;

procedure TMyDataModule.CustomerItemsAfterPost(DataSet: TDataSet);
begin
  try
     Customers.DisableControls;
     CustomerOrders.DisableControls;
     try
        // Update any aggregate customer information here
        CustomerOrders.Edit;
        if WasInsertingLine then
           CustomerOrdersNextLineNumber.AsInteger:=(CustomerOrdersNextLineNumber.AsInteger+1);
        CustomerOrdersPurchaseTotal.AsCurrency:=(CustomerOrdersPurchaseTotal.AsCurrency-
                                                 BeforePurchaseTotal);
        CustomerOrdersPurchaseTotal.AsCurrency:=(CustomerOrdersPurchaseTotal.AsCurrency+
                                                 CustomerItemsPurchaseTotal.AsCurrency);
        CustomerOrdersShippingTotal.AsCurrency:=(CustomerOrdersShippingTotal.AsCurrency-
                                                 BeforeShipping);
        CustomerOrdersShippingTotal.AsCurrency:=(CustomerOrdersShippingTotal.AsCurrency+
                                                 CustomerItemsShipping.AsCurrency);
        CustomerOrdersOrderTotal.AsCurrency:=(CustomerOrdersPurchaseTotal.AsCurrency+
                                              CustomerOrdersShippingTotal.AsCurrency);
        CustomerOrdersBalanceDue.AsCurrency:=(CustomerOrdersOrderTotal.AsCurrency-
                                              CustomerOrdersAmountPaid.AsCurrency);
        if WasInsertingLine then
           CustomerOrdersAmountPaid.AsCurrency:=(CustomerOrdersAmountPaid.AsCurrency+
                                                 (CustomerItemsPurchaseTotal.AsCurrency+
                                                  CustomerItemsShipping.AsCurrency));
        CustomerOrders.Post;
        MyDatabase.Commit;
     finally
        CustomerOrders.EnableControls;
        Customers.EnableControls;
     end;
  except
     MyDatabase.Rollback;
     raise;
  end;
end;

Tim Young
Elevate Software
www.elevatesoft.com

Forgot to mention:  the OnDeleteError event handler should be pointed to the OnPostError event handler.  They both do the same thing.

Tim Young
Elevate Software
www.elevatesoft.com