Is it possible to use the SQL BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION when embedding SQL Queries into an application with mutiple calls to the SQL for Table Updates. For example I have the following code:
Q.SQL.ADD(<UPDATE A RECORD>);
Q.ExecSQL;
Q.Close;
Q.SQL.Clear;
Q.SQL.ADD(<Select Some Data>);
Q.Open;
Set Some Variables
Q.Close;
Q.SQL.Clear;
Q.SQL.ADD(<UPDATE A RECORD>);
Q.ExecSQL;
What I would like to do is if the second update fails I want to roll back the first transaction.
If I set a unique notation for the BEGIN, COMMIT, ROLLBACK so as to specify what is being committed or rolled back, is it feasible.
i.e. before the first Update specify BEGIN TRANSACTION_A then after the last update specify COMMIT TRANSACTION_A
I hope that makes sense. If I was doing this in a SQL Stored Procedure then I would be able to specify this at the start and end of the procedure, but I have had to break the code down into manageable chunks due to process blocks and deadlocks on a heavy loaded SQL Server.