I've found an issue with my use of TDatabase transactions in my Delphi code (against a SQL Server database). I use syntax in the order :
DB1.StartTransaction;
TRY
with Query1 do
begin
// UPDATE TableName
// SET FieldID =
FieldID
// WHERE IDX =IDX
Active := False;
Params[0] := pParam;
ExecSQL;
end;
// Try to commit the workload
DB1.Commit;
EXCEPT
// Rollback the changes if problem
DB1.Rollback;
END; // of TRY ... EXCEPT
What I have seen is that, when stepping through the code, the Commit will be executed but (due to the PRIMARY KEY constraint against the table) the update will NOT be performed, however the Rollback in the EXCEPT will not be executed - I would have thought that if the PRIMARY KEY blocks the update then the EXCEPT section of my code should be executed and the transaction would be rolled back. However this is not the case, although the Commit is executed, the update does not (as it cannot) take place.
Anyone have any thoughts on this ?
Thanks in advance
Steve
DB1.StartTransaction;
TRY
with Query1 do
begin
// UPDATE TableName
// SET FieldID =
FieldID // WHERE IDX =
IDX Active := False;
Params[0] := pParam;
ExecSQL;
end;
// Try to commit the workload
DB1.Commit;
EXCEPT
// Rollback the changes if problem
DB1.Rollback;
END; // of TRY ... EXCEPT
What I have seen is that, when stepping through the code, the Commit will be executed but (due to the PRIMARY KEY constraint against the table) the update will NOT be performed, however the Rollback in the EXCEPT will not be executed - I would have thought that if the PRIMARY KEY blocks the update then the EXCEPT section of my code should be executed and the transaction would be rolled back. However this is not the case, although the Commit is executed, the update does not (as it cannot) take place.
Anyone have any thoughts on this ?
Thanks in advance
Steve