Valid in: SQL, ESQL, DBProc
The Rollback statement rolls back the current transaction.
Note: This statement has additional considerations when used in a distributed environment. For more information, see the Ingres Star User Guide.
The Rollback statement has the following format:
[EXEC SQL] ROLLBACK [WORK] [TO savepoint_name];
The Rollback statement aborts part or all of the current transaction. If rollback is issued without the optional to clause, the statement terminates the transaction and rolls back any changes made by the transaction.
If the to savepoint_name clause is included, the transaction is not terminated and only those changes made after the specified savepoint are rolled back. Processing resumes with the statement following the rollback to savepointname statement. If autocommit is enabled, the rollback statement has no effect.
Only the rollback statement without the optional to clause can be used in database procedures, and only in procedures which are directly executed. A database procedure that is invoked by a rule cannot contain either version of the rollback statement.
If a database event registration is removed (using the remove dbevent statement), and the transaction is subsequently rolled backed, the database event registration is not restored.
Note: The optional keyword work is included for compatibility with the ISO and ANSI SQL standards.
In addition to aborting all or part of the current transaction, an embedded Rollback statement:
The to savepoint_name clause cannot be included if there are open cursors in the transaction. Also, when a savepoint is specified in the rollback statement, the DBMS Server discards only those statements that were prepared after the specified savepoint's declaration.
Savepoint_name cannot be specified using a host language variable.
All users are permitted to use this statement.
If the Rollback statement is issued without the to savepoint option, the statement terminates the transaction and releases all locks held during the transaction. If the to savepoint_name option is included, no locks are released.
Executing a rollback undoes some or all of the work done by a transaction. The time required to do this is generally the same amount of time taken to perform the work.