How to use transactions (begin transaction, commit transaction)?

Question

I have seen transaction usage in some cases but never really understood in which situations they should be used. How and when transactions should be used (begin transaction statement)? I have read that Microsoft do not suggest to use transaction statements (commit, rollback) inside a trigger and stored procedure.

Solution 1

Transactions can be used in conjunction with error handling in stored procedures or SQL scripts when inserting or manipulating data to make sure everything is consistent.

For example, if you have a stored procedure that inserts records into a parent table and a child table, you would want to make sure that the parent record gets inserted first; if it fails, you can rollback your changes so you don't have an orphaned child record.

Erland Sommarskog has a great article on how to use error handling in SQL Server.

Finally, where has Microsoft suggested to not use transactions in stored procedures? I would think that stored procedures would be an ideal place to use them.

Solution 2

If I was transferring moey from one account to another, I'd want it to be 100% successful or 100% fail:

UPDATE PersonalAccount SET Balance = Balance - 100 WHERE Username = 'gbn'
--what if the server caught fire here?
UPDATE SavingsAccount SET Balance = Balance + 100 WHERE Username = 'gbn'

As for what you read, complete rubbish except no need to use BEGIN TRAN in a trigger.

An example from "Nested stored procedures containing TRY CATCH ROLLBACK pattern?"

Comments

[juur]

Thanks, maybe my statement about was too straight: there is a warning that using commit or rollback inside a procedure or trigger may cause errors: msdn.microsoft.com/en-us/library/ms187844(v=SQL.100).aspx