Hello,
my problem is… I don’t know, somehow stupid.
Datebase is InterBase.
Cached updates are on.
Supose, I have one table where storing organizations. In the same table, there are suborganisations. When I try to insert organisation it is not problem, ApplyUpdates Commit and done, database generate PrimaryKey. But I wont something else. I wont to put organizsation and suborganisations in the same time.
What I tried:
- 2 querys on same table and procedure in database that generate PK.
- 2 querys and 2 tables in the other there are trigger after insert that insert into orginal table and delete from it unnecesary data
Structure is like this
pk integer,
pk_sub integer,
name varchar(50);
if pk=pk_sub it means that it is main organisation. In other chases pk_sub must exist in column pk
OK:=True;
if (SpisakQuery.State in [dsInsert]) then
begin
nrProc.ParamByName('PARAM').AsString:='ORG_PK';
nrProc.ExecProc;
Broj:=nrProc.ParamByName('NR’).AsInteger;
end
else
BNr:=SpisakQueryORG_PK.AsInteger;
try
if (BQuery.UpdateStatus in [usModified, usInserted]) then
begin
if (AQuery.State in [dsInsert]) then
begin
AQueryORGANIZACIJA_PK.AsInteger:=Broj;
AQuery.Post;
BQuery.First;
while not BQuery.Eof do
begin
BQuery.Edit;
BQueryPK_sub.AsInteger:=BNr;
BQuery.Post;
BQuery.Next;
end;
end;
AQuery.Transaction.Databases[0].ApplyUpdates([AQuery, BQuery]);
end
else
begin
AQueryORG_PK.AsInteger:=Broj;
AQuery.Transaction.Databases[0].ApplyUpdates([AQuery]);
end;
except
on EIBInterBaseError do
begin
ShowMessage('Something is bad');
OK:=False;
end;
end;
if OK then
begin
AQuery.Transaction.Commit;//Retaining;
//temp:=AQueryORG_PK.AsString;
AQuery.Close;
AActionExecute(Sender);
AQuery.Locate('ORG_PK', Broj, []);
{Some action}
end;
The simplest solution is by using a transaction to roll back any changes. You start by creating the transaction, insert the master record with just the minimum required values and post it. Then put the record in editmode while you fill in other master data and add the detail records. Once all detail records are added you can commit the transaction or do a rollback in case of an error.
The master record MUST exist before you can add detail records.