|
<< Click to Display Table of Contents >> SQL Transactions |
  
|
|
<< Click to Display Table of Contents >> SQL Transactions |
|
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.
Transactions are relevant only to SQL statements adding/updating/deleting contents of the database.
Transactions ensures execution of SQL statements as well as SQL scripts (several SQL statements executed sequentially in one process) may be rolled back, even in case of execution error in just one of the SQL statements.
To envoke 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 the ROLLBACK in the 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.
•Also, 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.
If one or more elements of the statement(s) between START TRANSACTION; and COMMIT; cannot execute, whatever because of e.g. data type imcompatibility, syntax errors or record locking, the execution will be terminated by automatic addition of a ROLLBACK.
The screen print 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 error evoked ROLLBACK response.
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, and due to the TRANSACTION COMMIT clause, all INSERT statements will gracefully be abolished.
