delete and Transactions

In chained transaction mode, each delete statement implicitly begins a new transaction if no transaction is currently active.

Use commit to complete any deletes, or use rollback to undo the changes. For example:

delete from sales where date < ’01/01/06’
if exists (select stor_id
   from stores
   where stor_id not in 
    (select stor_id from sales))
      rollback transaction
else
      commit transaction

This batch begins a transaction (using the chained transaction mode) and deletes rows with dates earlier than Jan. 1, 2006 from the sales table. If it deletes all sales entries associated with a store, it rolls back all the changes to sales and ends the transaction. Otherwise, it commits the deletions and ends the transaction. For more information about the chained mode, see the Transact-SQL Users Guide.