Rollback won't undo all changes.

Hi there,

Have a situation in my paradox database where I need to rollback single row inserts on 4 different tables if the insert fails on one. They all have indexes so this should be permitted I think. Here is the code I am using any idea why its not working? i.e. if there is an exception on the second insert the first still remains.

with OpTransactionsSQL do
begin
MASTDATA.VideoLinkDB.StartTransaction;
try
MYSQL := Format('INSERT INTO TRANSACTIONS(TRANSACTION_ID, MEMBER_ID, PRODUCT_ID, RENTAL_DATE, RENTAL_TIME, DUE_DATE, DUE_TIME) VALUES(%d, %d, %d, %s, %s, %s, %s)',[NEXTTRANSIDINT,CUSTIDINT,PRODIDINT,'"'+CURRDATE+'"','"'+CURRTIME+'"','"'+RETDATE+'"','"'+RETTIME+'"']);
Close;
SQL.Clear;
SQL.Add(MYSQL);
ExecSQL;
Close;
OpTransactionsSQL.DataSource := OpTransactionsDS;
MYSQL := Format('INSERT INTO OPERATOR_TRANSACTIONS(TRANSACTION_ID, OPERATOR_RENTAL_ID) VALUES(%d, %d)',[NEXTTRANSIDINT,OPIDINT]);
Close;
SQL.Clear;
SQL.Add(MYSQL);
ExecSQL;
Close;
MASTDATA.VideoLinkDB.Commit;
except
begin
MASTDATA.VideoLinkDB.Rollback; raise;
end
end;

Is it anything to do with using ExecSQl i.e. Passthrough SQL opposed to local transactions not really sure how this works.

Thanks
Niall
Trying to get a rollback on an exception to prevent