The COMMIT WORK and ROLLBACK WORK Statements

Whenever a c-treeSQL statement is executed by an ESQL program, there is an active transaction associated with that ESQL program. The execution of the first ESQL executable statement in an ESQL program starts a transaction. All subsequent c-treeSQL statements are executed as part of this transaction until the transaction is committed or rolled back. After this, the first execution of a c-treeSQL statement starts a new transaction.

The primary statements that are used for transaction management are COMMIT WORK and ROLLBACK WORK.

The COMMIT WORK statement is used to make permanent, the changes made on a database. The following example shows the usage of the COMMIT WORK statement:

...

EXEC SQL

 UPDATE orders SET qty = 2000 WHERE order_no = 1001 ;

EXEC SQL COMMIT WORK ;

...

The normal termination of a transaction occurs with the successful execution of the COMMIT WORK statement.

In contrast, the ROLLBACK WORK statement means abnormal termination of a transaction. If a transaction executes the ROLLBACK WORK statement, all changes made to the database in that transaction are canceled. The usage of the ROLLBACK WORK statement is similar to the COMMIT WORK statement and is shown below:

...

EXEC SQL

 UPDATE orders

 SET qty = 2000

 WHERE order_no = 1001 ;

EXEC SQL ROLLBACK WORK ;

...

For more information on transaction statements refer to "Transaction Management in ESQL".