Transactions in SQL Server 2000

Continued From Previous Page....

Now let's test the transaction again, but this time lets make it fail. Change the values in the table as shown in the figure below.

(Figure 4: Shows the changes made in the records, to make the transaction fail this time)

Here I have again changed the values back as they were, except for one and that is the last record whose value has been left to $75 instead of changing it back to $50. Now here is the point behind this, when we execute the stored procedure again this time, the transaction would fail because according to the condition that we have placed, there must exist at least three records having prices equal to $50 within the table for the second update statement to execute, but here we have only two. Let's execute the stored procedure again from the Query Analyzer and see what do we get? Look at the following figure for results.

(Figure 5: Shows the Unsuccessful transaction results)

You would be surprised to see that the first statement shows "1 row affected", and this is the update statement we have used to update the price of the $45 book to $60. While the next statement says that "Unable to update the records!", which is the customized statement given by us incase of a rollback scenario. Why don't we go and see the records in the table to have an idea what has actually happened. Look at the figure below!

(Figure 6: No changes observed)

Well then! nothing changed!. The reason is that as I told in the beginning of the article that a transaction works like a single unit, and this units execution has no intermediate state. Incase if the first update statement would have executed then half of the transaction would have occurred while the other half would have failed due to the condition that we have imposed for its execution. In this way we would have obtained an intermediate state where half of the required process had completed itself whereas the other half had failed. But due to the usage of the transactions, we bounded both the statements as a single unit and in success, both execute, while in failure none gets executed at all.

Conclusion

This article gave a detailed discussion upon the Transactions with respect to Microsoft SQL Server. We saw how transaction can be implemented and how they work through an example that was implemented and tested totally in SQL Server environment.

Programming transactions in databases is a vast topic. Not all of it could be covered within a single article like this. This article was just to give you an introduction and an idea of what transactions are. If you wish to know more about Transactions and other SQL Server 2000 features I would recommend studying of the book "Mastering Sql Server 2000 by Sybex", an excellent resource for intermediate and advance level learners.