commit changes of joined tables

[wkd24154]

Hello everybody,

how is it possible to commit cached updates if the query goes to more than one table?
Here a little example:

select tab1.rowid, tab2.rowid, tab1.id, tab2.tab1_id
from tab1, tab2
where tab1.id = tab2.tab1_id

Can someone give me a solution?

 

[Marco Kalter]

Do you also want to update columns fro both tables? In that case you must use an OnApplyRecord event handler or a view with an instead of trigger.

If ony one table is updated, simply set the UpdatingTable property.

 

[wkd24154]

It is right that I want to commit each column of the table. So I tried to create an OnApplyRecord event but it doesn`t work. The problem is, that I create the DataSet at runtime. So how can I create an OnApplyRecord trigger for this event at runtime? Could you send me a little example?

 

[Marco Kalter]

procedure TMainForm.RunTimeOnApplyRecord(Sender: TOracleDataSet;
  Action: Char; var Applied: Boolean; var NewRowId: String);
begin
  ...
end;

procedure TMainForm.ButtonClick(Sender: TObject);
var MyDataSet: TOracleDataSet;
begin
  MyDataSet := TOracleDataSet.Create(nil);
  MyDataSet.OnApplyRecord := RunTimeOnApplyRecord;
  ...
end;

 

[wkd24154]

And how can I commit the table tab1 in the OnApplyRecord event if I use a outer join select statement like this:

select tab1.rowid, tab2.rowid, tab1.id, tab2.tab1_id
from tab1, tab2
where tab1.id (+) = tab2.tab1_id

 

[Marco Kalter]

You should never commit within an OnApplyRecord event handler. The transaction is either managed by the TOracleDataSet when a record is posted, or is is managed by the application when calling TOracleSession.ApplyUpdates, CancelUpdates, or CommitUpdates.

Furthermore (I'm not sure if this is relevant) you do not commit a table, but a transaction of a session.

 

[wkd24154]

Maybe I said it misunderstandly.

I want to commit all changes of a dataset with outer join select statement. Could someone give me a simple testcase?