Errors and transaction rollbacks

Data integrity errors can affect the state of implicit or explicit transactions:

Table 23-2 summarizes how rollback affects Adaptive Server processing in several different contexts.

Table 23-2: How rollback affects processing

Context

Effects of rollback

Transaction only

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, rollback affects all of those batches.

Any commands issued after the rollback are executed.

Stored procedure only

None.

Stored procedure in a transaction

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, rollback affects all those batches.

Any commands issued after the rollback are executed.

Stored procedure produces error message 266: Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing.

Trigger only

Trigger completes, but trigger effects are rolled back.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Trigger in a transaction

Trigger completes, but trigger effects are rolled back.

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, rollback affects all those batches.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Nested trigger

Inner trigger completes, but all trigger effects are rolled back.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Nested trigger in a transaction

Inner trigger completes, but all trigger effects are rolled back.

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, rollback affects all those batches.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

In stored procedures and triggers, the number of begin transaction statements must match the number of commit statements. A procedure or trigger that contains unpaired begin/commit statements produces a warning message when it is executed. This also applies to stored procedures that use chained mode: the first statement that implicitly begins a transaction must have a matching commit.

With duplicate key errors and rules violations, the trigger completes (unless there is also a return statement), and statements such as print, raiserror, or remote procedure calls are performed. Then, the trigger and the rest of the transaction are rolled back, and the rest of the batch is aborted. Remote procedure calls executed from inside a normal SQL transaction (not using the DB-Library two-phase commit) are not rolled back by a rollback statement.

Table 23-3 summarizes how a rollback caused by a duplicate key error or a rules violation affects Adaptive Server processing in several different contexts.

Table 23-3: Rollbacks caused by duplicate key errors/rules violations

Context

Effects of data modification errors during transactions

Transaction only

Current command is aborted. Previous commands are not rolled back, and subsequent commands are executed.

Transaction within a stored procedure

Same as above.

Stored procedure in a transaction

Same as above.

Trigger only

Trigger completes, but trigger effects are rolled back.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Trigger in a transaction

Trigger completes, but trigger effects are rolled back.

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, the rollback affects all of those batches.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Nested trigger

Inner trigger completes, but all trigger effects are rolled back.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Nested trigger in a transaction

Inner trigger completes, but all trigger effects are rolled back.

All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, the rollback affects all of those batches.

Any remaining commands in the batch are not executed. Processing resumes at the next batch.

Trigger with rollback followed by an error in the transaction

Trigger effects are rolled back. All data modifications since the start of the transaction are rolled back. If a transaction spans multiple batches, the rollback affects all of those batches.

Trigger continues and gets duplicate key or rules error. Normally, the trigger rolls back effects and continues, but in this case, trigger effects are not rolled back.

After the trigger completes, any remaining commands in the batch are not executed. Processing resumes at the next batch.