If either the first or the second INSERT fails I want to rollback the whole transaction (and let the user know).
Using the code in the example 1 below the two inserts work OK.
However, if I run the code again but deliberately cause the first insert to fail by forcing a duplicate key error - see example 2 where I've changed the sql to 'INSERT INTO table1 (memberid, thedate, amount,subsID ) values (21, '2018-04-18', 50.5, 500); (where 500 is the subsID generated last time)
then the 'except' block is called and I get the duplicate key error message BUT the insert partly goes ahead initially and is not rolled back. Instead I get a new record inserted with the memberID in place and all other fields set to null.
(Although this is only visible in an open session in my DBMS. If I reconnect the DBMS then there is no partially inserted record there and the rollback seems to have happened)
Is my code the correct way to do this? if not, how should I correctly code this so that either both or neither of the inserts happens?
The PK of the table is a combination of memberid, thedate and an autoincrement field, subsID
Example 1;
Code: Select all
Procedure transfer;
var
MyConnection1 : TMyConnection;
sql : string;
begin
sql := 'INSERT INTO table1 (memberid, thedate, amount) values (21, '2018-04-18', 50.5); '
+'INSERT INTO table1 (memberid,thedate, amount) values (35, '2018-04-18', -50.5); ' ;
try
begin
MyConnection1.StartTransaction;
MyConnection1.ExecSQL(sql);
MyConnection1.Commit;
end
except
on E : Exception do
begin
MyConnection1.Rollback;
showmessage ('Exception class name = '+E.ClassName+ slinebreak
+ 'Exception message = '+E.Message);
end //on E
end;//try
Example 2;
Code: Select all
Procedure transfer2;
var
MyConnection1 : TMyConnection;
sql : string;
begin
sql := 'INSERT INTO table1 (memberid, thedate, amount, subsID) values (21, '2018-04-18', 50.5, 500); '
+'INSERT INTO table1 (memberid,thedate, amount) values (35, '2018-04-18', -50.5); ' ;
try
begin
MyConnection1.StartTransaction;
MyConnection1.ExecSQL(sql);
MyConnection1.Commit;
end
except
on E : Exception do
begin
MyConnection1.Rollback;
showmessage ('Exception class name = '+E.ClassName+ slinebreak
+ 'Exception message = '+E.Message);
end //on E
end;//try