[
From 4GuysFromRolla]
Last week's article, Maintaining Database
Consistency with Transactions, looked at what, exactly, transactions are and how they can be used to guarantee consistency
in a database when issuing commands that modify multiple tables. Specifically, transactions ensure that a set of modifying
statements are atomic, namely that either all steps succeed or all steps fail. Transactions guarantee
atomicity across query errors - such as trying to delete a record that cannot be deleted due to a foreign key constraint, or
attempting to insert a string value into a numeric field in a database table - as well as catastrophic errors, such as power failures,
hard drive crashes, and so on. In short, when wrapping multiple modifying commands within a transaction the database is
guaranteed to be in one of two states: either the state before the batch of commands was issued, or the state after
all commands have completed - in other words, there's no 'in-between' state.
In this week's article we'll continue our look at transactions, examining how to create, commit, and rollback transactions
strictly through stored procedures. After examining the T-SQL syntax for working with transactions we'll discuss
when one would opt to use transactions directly in stored procedures versus using them in the ADO.NET layer. Read on to
learn more!
The article continues at
http://www.4guysfromrolla.com/webtech/080305-1.shtml