Master/Detail and cachedupdates

[colucci]

I'm trying out sdac and I have a few questions for you:

I Have two tables:

Table Master
ID_Master : Int => AutoInc and primary key
Name : varchar

Table Detail
ID_Detail : Int => AutoInc and primary key
ID_Master : Int => fk to master table
Name : varchar

So, In my delphi application I have two tmsquery components:

Master:

SQL = select * from master
Insert = INSERT INTO master(name) VALUES (:name) SET :id_master = SCOPE_IDENTITY()

Detail

SQL = select * from detail
Insert = INSERT INTO detail (id_master, name) VALUES (:id_master, :name) SET :id_detail = SCOPE_IDENTITY()

My detail query has these propertys enabled: CachedUpdates, Options.ReturnParams.
My master query has only this property enabled: Options.ReturnParams.

I Wrote these events:

procedure TForm1.detailBeforeUpdateExecute(Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
if stInsert in StatementTypes then begin
Params.ParamByName('Id_Detail').ParamType := ptInputOutput;
Params.ParamByName('Id_Master').AsInteger := masterid_master.Value;
end;
end;

procedure TForm1.FormCreate(Sender: TObject);
begin
master.Active := True;
detail.Active := True;
end;

procedure TForm1.masterAfterPost(DataSet: TDataSet);
begin
detail.ApplyUpdates;
end;

procedure TForm1.masterBeforeUpdateExecute(Sender: TCustomMSDataSet; StatementTypes: TStatementTypes; Params: TMSParams);
begin
if stInsert in StatementTypes then begin
Params.ParamByName('Id_Master').ParamType := ptInputOutput;
end;
end;


Everything works fine. But if I configure the detail query mastersource property (doing a master/detail) when I post the master query, nothing happens with the detail table and the records I inserted in the detail query are not posted to the sql server and the event BeforeUpdateExecute of the detail query is not triggered. What am I doing wrong?

[Antaeus]

I think that you insert a record in the master table, post it, and then insert records in the detail dataset.
The ApplyUpdates in this case is called before a record is inserted into the detail dataset. Therefore detailBeforeUpdateExecute is not raised.
You should reconsider logic of your application.

[colucci]

Ok .. but I'd like to insert both master and detail at the same time, posting the detail when user posts the master .. is that possible?

[Antaeus]

The record in the master table should be posted before inserting records in the detail dataset in order to fill the ID_Detail field of the Detail table with the correct value.
I can suggest using transactions. The scenario may the following:
1) Start transaction (TMSConnection.StartTransaction).
2) Insert and post a master record.
3) Insert and post detail records.
4) Commit or rollback changes (TMSConnection.Commit, (TMSConnection.Rollback).

For more information see the SDAC help.

[colucci]

That is not what I want. I want user to add a record in the master and then insert records in the detail. After that, the user posts the master and the detail is posted in sequence.
I figured out how to do that. I just dont use autoinc fields so I can generate my own ids.
Thanks anyway.