I wanted to jot some notes down about using transactions and @@trancount - mostly, I personally like checking for if I should begin a transaction or not at the beginning of stored procedures. I know this comes from me being a software developer and thinking the database guy/gal handles all that, and then learning more about the database side of things and realizing database coding isnt always reviewed that closely by a DBA. So, it came time for me to learn. Now that I switched jobs, I dont have all the code blocks to copy/paste - so this blog post will help with that! :)
Lets start with the basics:
A transaction is a sequence of operations performed in it's own "bubble" from other processes and/or transactions occuring. Once a transaction is completed, the effects are permanent in the database.
Now, that is a simple explanation, as with everything, there are many things that go into it - such as how transactions affect each other because the data that is modified in 1 transaction is the same data needed for another transaction, so the "bubble" a transaction is in isnt completely isolated - or how if a transaction is rolled back, there is no permanent effect because nothing was done, etc. I am going to talk about some of the ways of starting, monitoring, and ending a transaction, as well as nested transactions, and possibly uncommitable transactions.
To start a transaction, the syntax is BEGIN TRANSACTION (or BEGIN TRAN). No matter what, this is pretty much straight forward. To end a transaction, you would either use COMMIT TRANSACTION, or ROLLBACK TRANSACTION, depending on if you want to make the changes you made, or not make the desired changes, respectively. That, in and of itself, is all you need to start utilizing transactions now - but I will keep going with more juicy info. :)