The SET SESSION WITH ON_ERROR option specifies how transaction errors are handled in the current session.

To direct the DBMS Server to rollback the current transaction if an error occurs, specify ROLLBACK TRANSACTION.

To direct the DBMS Server to rollback only the current statement, specify ROLLBACK statement. This is the default.

To determine the current status of transaction error handling, issue the SELECT DBMSINFO('ON_ERROR_STATE') statement.

Specifying ROLLBACK TRANSACTION reduces logging overhead and can help performance. The performance gain is offset by the fact that, if an error occurs, the entire transaction is rolled back. The following errors always roll back the current transaction regardless of the current transaction error-handling setting:

To determine if a transaction was aborted as the result of a database statement error, issue the SELECT DBMSINFO('TRANSACTION_STATE') statement. If the error aborted the transaction, this statement returns 0, indicating that the application is currently not in a transaction.

The SET SESSION WITH ON_ERROR statement cannot be issued from within a database procedure or multi-statement transaction.