ROLLBACK [WORK] is used to mark the end of a transaction, in effect backing out of all changes made to the database in that transaction.
No special privileges are required to issue the ROLLBACK [WORK] command.
A transaction begins with the execution of either a data definition (DDL), data manipulation (DML), or SET TRANSACTION statement. A transaction is terminated by a COMMIT [WORK] or ROLLBACK [WORK] command. A SET TRANSACTION statement can also be used to both end a transaction with an implicit ROLLBACK and start a new one. Only the results of SQL commands executed within a single, uncommitted transaction are affected by the ROLLBACK command.
When the ROLLBACK command is issued, all database changes caused by CREATE, ALTER, DROP, GRANT, REVOKE, INSERT, UPDATE, and DELETE commands within the transaction are undone.
A ROLLBACK is issued automatically if the user is disconnected from the server by any means. This would include the enforcement of exclusive mode from ndlm via the -u switch. Refer to the SAND CDBMS Tools Reference Guide for detailed information about ndlm command line parameters.
Note that ROLLBACK and ROLLBACK WORK perform identically.
Using the .EXIT system command to end an nisqlm session will automatically ROLLBACK any changes made to the database since the session began or since the last COMMIT/ROLLBACK command. If the user wishes to make changes permanent, the COMMIT command should be used prior to the .EXIT. (Refer to the SAND CDBMS Interactive SQL Utility (nisqlm) chapter in the SAND CDBMS Tools Reference Guide for more information about the .EXIT system command.)
INSERT INTO products VALUES (283, 24.99, 'widget');
In this example, a row is inserted into the products table, followed by a ROLLBACK. The ROLLBACK command effectively cancels the immediately preceding INSERT and any other uncommitted database-altering statements in the transaction.