|
<< Click to Display Table of Contents >> Transactions as error trap |
  
|
|
<< Click to Display Table of Contents >> Transactions as error trap |
|
As well as in non-SQL data processings, the AbsoluteSQL also handles transactions via user designed SQL statements if including the paired START TRANSACTION; and COMMIT; statements.
Including the COMMIT; into the SQL script may be feasible, if the you know the procedure is proven right, and the transaction is needed only to trap inconvertible source data errors triggering the auto ROLLBACK procedure.
To invoke a transaction the general algorithm is like this:
START TRANSACTION;
< some one or several SQL DML statements >
COMMIT;
Simple SELECT statements are placed after the COMMIT statement.
•Do NOT include SQL structure defining statements (SQL DDL) between the START TRANSACTION; and COMMIT; statements.
If illegal structure defining statements are detected AbsoluteSQL will automatically add and execute the ROLLBACK statement.
•Do NOT include the ROLLBACK in the same SQL, as this should be executed only conditionally in case of SQL execution error and in case some TRANSACTION state is detected by the AbsoluteSQL when executing the script.
•If updates are executed without errors, updates may still be reverted by running a ROLLBACK manually launched from a separate SQL statement tab.
If one or more elements of the statement(s) between START TRANSACTION; and COMMIT; cannot execute, whatever because of e.g. data type incompatibility, syntax errors or record locking, the execution will be terminated by automatic addition of a ROLLBACK.
The screen print above displays SQL script as well as the ROLLBACK feed back dialog after execution error of the SQL script.
Even though the DELETE FROM [ICD-8]; is a valid statement, the DELETE FROM ICD10 WHERE ICD10code LIKE 'A%'; statement being invalid triggers the execution error, and all SQL statements between the START TRANSACTION; and COMMIT; are rolled back. Thus all deletions from the [ICD-8] table are undone by the ROLLBACK response to the error evoked.
Transactions are thus useful safe guarding against unwanted results of SQL statements if not all of the statements in a script can be executed without executions errors.
In the following example, the third INSERT statement obviously raises an error (too many values), and due to the TRANSACTION COMMIT clause, all INSERT statements will gracefully be abolished.
