You can use transactions in stored procedures and triggers just as with statement batches. If a transaction in a batch or stored procedure invokes another stored procedure or trigger containing a transaction, the second transaction is nested into the first one.
The first explicit or implicit (using chained mode) begin transaction starts the transaction in the batch, stored procedure, or trigger. Each subsequent begin transaction increments the nesting level. Each subsequent commit transaction decrements the nesting level until it reaches 0. Adaptive Server then commits the entire transaction. A rollback transaction aborts the entire transaction up to the first begin transaction regardless of the nesting level or the number of stored procedures and triggers it spans.
In stored procedures and triggers, the number of begin transaction statements must match the number of commit transaction statements. This also applies to stored procedures that use chained mode. The first statement that implicitly begins a transaction must also have a matching commit transaction.
rollback transaction statements in stored procedures do not affect subsequent statements in the procedure or batch that originally called the procedure. Adaptive Server executes subsequent statements in the stored procedure or batch. However, rollback transaction statements in triggers abort the batch so that subsequent statements are not executed.
rollback statements in triggers:
1) roll back the transaction,
2) complete subsequent statements in the trigger, and 3) abort the
batch so that subsequent statements in the batch are not executed.
For example, the following batch calls the stored procedure myproc, which includes a rollback transaction statement:
begin tran update titles set ... insert into titles ... execute myproc delete titles where ...
The update and insert statements are rolled back and the transaction is aborted. Adaptive Server continues the batch and executes the delete statement. However, if there is an insert trigger on a table that includes a rollback transaction, the entire batch is aborted and the delete is not executed. For example:
begin tran update authors set ... insert into authors ... delete authors where ...
Different transaction modes or isolation levels for stored procedures have certain requirements, which are described in “Transaction modes and stored procedures”. Triggers are not affected by the current transaction mode, since they are called as part of a data modification statement.
