Menu
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.
579
Transaction ControlBEGIN TRANSACTION: It is a command that indicates the beginning of each transaction. COMMIT: It is a command used to save the changes permanently in the database. ROLLBACK: It is a command used to cancel all modifications and goes into their previous state.
START TRANSACTION or BEGIN start a new transaction. COMMIT commits the current transaction, making its changes permanent. ROLLBACK rolls back the current transaction, canceling its changes. SET autocommit disables or enables the default autocommit mode for the current session.
BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.
The COMMIT command is the transactional command used to save changes invoked by a transaction to the database. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command. The syntax for the COMMIT command is as follows. COMMIT; Example.
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.
194
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?"
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
