Errors that affect data integrity can affect the state of implicit or explicit transactions:
Errors with severity levels of 19 or greater
Since these errors terminate the user connection to the server, any errors of level 19 or greater that occur while a user transaction is in progress abort the transaction and roll back all statements to the outermost begin transaction. Adaptive Server always rolls back any uncommitted transactions at the end of a session.
Errors in data modification commands that affect data integrity (see Table 19-5):
Arithmetic overflow and divide-by-zero errors (effects on transactions can be changed with the set arithabort arith_overflow command)
Permissions violations
Rules violations
Duplicate key violations
Table 19-4 summarizes how rollback affects Adaptive Server processing in several different contexts (such as within a transaction, stored procedure, or trigger):
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 19-5 summarizes how a rollback caused by a duplicate key error or a rules violation affects Adaptive Server processing in several different contexts:
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. |
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 trigger effects are not rolled back in this case. After the trigger completes, any remaining commands in the batch are not executed. Processing resumes at the next batch. |
Stored procedure in a transaction |
Same as above. |
