
SQL delete detail records
Quote:
> begin
> close;
> SQL.clear;
> SQL.Add('delete from table2 where table2."field" = :field);
> prepare;
> paramByName('field').asstring :=
>mastertable.fieldbyname('field').asstring;
Another thing you may need to be concerned with is the transaction life. What
if the master does not delete? Do you still want to delete the detail records?
I like routines like:
with masterQry do begin
SQL.Clear;
{ I don't use parameters if I have to create the SQL statement anyway - just
make it part of the statement }
SQL.Add( 'delete from mastertbl where MyID = ''5''');
MyDbase.StartTransaction
try
execSQL;
if RowsAffected = 1 then begin
with detailQry do begin
SQL.Clear;
SQL.Add('delete from detailtbl where ParentID=''5''')
execSQL;
{ no need to check for rowsaffected - if there were no details - who cares
if it failed }
end; {with detailQry}
MyDBase.Commit;
else
raise exception.create('No master with ID=''5''');
except
on E: Exception do begin
MyDBase.Rollback;
MessageDlg( 'Could not delete' + #10 + E.Message, mtWarning, [mbOK], 0
);
end; {on Exception }
end; { except }
HTH - Lance