LS

table A (Aid)
table B (Bid, Aid)
table C (Cid, Aid)

When deleting a record in A (Aid='a') I want :
1 the records in table C that contain Aid='a' also to be deleted
2 the records in table B that contain Aid='a' to get the value NULL

Only if 1 and 2 succeed I want the record in A (Aid='a') to be deleted. So I
figure I must do it in the onbeforedelete event of table A (where I can
Abort). But what's the proper way: a transaction in a sql script or ... An
example would be very helpfull. Thanks in advance.

Regards, Harry
dbIsam 3.30, D6

Harry

At least for me, transactions *ALWAYS* is the best way to change more than
on table.

Database.StartTransaction;
try
   with Query do begin
       Sql.Text := 'delete from C where aid="a"';
       ExecSql;
       Sql.Text := 'update B set aid=null where aid="a"';
       ExecSql;
       Sql.Text := 'delete from A where aid="a"';
       ExecSql;
   end;
   Database.Commit;
except
   Database.RollBack;
   raise;
end;

or you can do it inside a SQL scripts like:

with Query do begin
   Sql.Clear;
   Sql.Add('delete from C where aid="a"');
   Sql.Add(';');
   Sql.Add('update B set aid=null where aid="a"');
   Sql.Add(';');
   Sql.Add('delete from A where aid="a"');
   ExecSql;
end;

This SQL script will be executed inside a transaction escope (called
internally by DBISAM)

Eduardo

Harry,

<< Only if 1 and 2 succeed I want the record in A (Aid='a') to be deleted.
So I figure I must do it in the onbeforedelete event of table A (where I can
Abort). But what's the proper way: a transaction in a sql script or ... >>

Just use a transaction around the entire set by starting the transaction in
the BeforeDelete and updating the other tables and committing/rolling back
the transaction in the AfterDelete.  Also, be sure to rollback the
transaction in the OnDeleteError event handler.

--
Tim Young
Elevate Software
www.elevatesoft.com