Using Begin Transaction command in SQL
In this post I will talk about Transaction structure in T-Sql.
“Transaction” is the smallest stack of processes that can not be divided into smaller pieces. It is used when a group of processes take place repeatedly and the processes are evaluated in series or in batches and all of them need to be handled properly. Even if only one process has been completed unsuccessfully, it is assumed that there has been no transaction due to this principle. However when all processes has been completed successfully it means that all data changes have been accepted in the transaction.
These processes can be inserting data into a table, updating a table or deleting data from a table. Since these processes are related one to another and they are consecutive transactions, even one of them has not been completed successfully there may be a important data loss. To prevent these kind of problems all processes must wait until the last process has been completed successfully. In order to ensure consistency in the changes over the data during these operations, complementary qualities are required for each process. To provide these needs the term “transaction” has emerged. When developing with database applications including these kind of processes in transaction blocks helps us to perform more secure operations .
When all processes in a transaction have been completed successfully transaction commit command runs up and changes are reflected in the database. If there is an error during transaction process transaction rollback command runs up. With rollback command all processes are rollbacked and nothing changes in the database.
Here is an example of how to use transaction in T-SQL;
Create a sample table like below:
CREATE TABLE Persons ( LastName varchar(255), FirstName varchar(255), City varchar(255), IsDeleted bit );
Insert some data into the table you have just created:
INSERT INTO Persons (LastName, FirstName, City, IsDeleted) VALUES ('Gunen','Mehmet','Istanbul',0); INSERT INTO Persons (LastName, FirstName, City, IsDeleted) VALUES ('Messi','Leo','Buenos Aires',0); INSERT INTO Persons (LastName, FirstName, City, IsDeleted) VALUES ('Gaucho','Ronaldinho','Sao Paulo',0); INSERT INTO Persons (LastName, FirstName, City, IsDeleted) VALUES ('Sneijder','Westley','Amsterdam',0);
In the following transaction Commit Transaction command will not run and a rollback will be performed, because the condition is not met:
BEGIN TRAN UPDATE Persons SET FirstName = 'UpdatedName 1' WHERE LastName = 'Messi' UPDATE Persons SET FirstName = 'UpdatedName 2' WHERE City = 'Amsterdam' IF @@ROWCOUNT = 2 COMMIT TRAN ELSE ROLLBACK TRAN
But in the following transaction Commit Transaction command will run since the condition is met:
BEGIN TRAN UPDATE Persons SET FirstName = 'UpdatedName 1' WHERE LastName = 'Messi' UPDATE Persons SET FirstName = 'UpdatedName 2' WHERE City = 'Amsterdam' IF @@ROWCOUNT = 1 COMMIT TRAN ELSE ROLLBACK TRAN
After execution of above script our Persons table will look like below:
Beware with UpdatedName 1 and UpdatedName 2 values.
Hope that helps you understand transaction in SQL..