Transaction
is the collection of T-SQL statements which executes in single unit.
Transaction begins with a specific T-SQL statement and ends when all T-SQL
statements execute successfully. If any statements fail then transaction fails.
Transaction has only two types of results success or failed.
If
transactions statements execute successfully then transaction is committed and
changes saved successfully in database. If any statement failed then complete
transaction become failed and rollback all statements execution.
When we use Transaction in SQL
Server
If
more than one table/view are related to each other and if we make any changes
on any table/view and this change should be on related table also, here we have
to use transaction.
Use
transaction carefully and use it when needed because when transaction begins it
locks all tables which are used in T-SQL Statement for data isolation. During
transaction execution period no one can perform any operation on used tables in
transaction due to region of maintaining data integrity.
Transaction
use the following statements
- Begin transaction
- Commit Transaction
- Rollback Transaction
Example:
Suppose
we have two table Users and UserProfile as bellow. Execute
the following script
CREATE TABLE Users
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(100),
EMAIL VARCHAR(100),
PWD VARCHAR(100),
CREATED_DATE DATETIME
)
CREATE TABLE UserProfile
(
ID BIGINT PRIMARY KEY,
USERID BIGINT foreign Key references Users(ID),
STREET VARCHAR(100),
CITY VARCHAR(100),
STATE VARCHAR(100),
COUNTRY VARCHAR(100),
PHONE VARCHAR(100),
CAREATED_DATE DATETIME,
MODIFIED_DATE DATETIME
)
INSERT INTO [dbo].[Users](ID,NAME,EMAIL,PWD,CREATED_DATE)
VALUES(1,'Codefari','info@codefari.com','codefari@123',GETDATE())
INSERT INTO [dbo].[UserProfile](ID,USERID,STREET,CITY,STATE,COUNTRY,PHONE,CAREATED_DATE,MODIFIED_DATE)
VALUES(1,1,'G-234','EAST DELHI','DELHI','INDIA','9089987767',GETDATE(),GETDATE())
SELECT * FROM Users
SELECT * FROM UserProfile
|
Result:
Now
we will try to insert data in both tables using transaction.
Users
table have a column ID which is primary key and it is reference key in
UserProfle table. UserProfile table ID is also primary key. In following script
I will put existing UserProfile .ID value and it will generate an error and
transaction should be failed and return same as above result. It means no any
changes in Users table also.
BEGIN
BEGIN TRANSACTION trn
BEGIN TRY
--Insert data in Users Table
INSERT INTO [dbo].[Users](ID,NAME,EMAIL,PWD,CREATED_DATE)
VALUES(2,'Dilip','dilip@gmail.com','dilip@123',GETDATE())
--Insert data in UserProfile table where ID is already
exist
INSERT INTO [dbo].[UserProfile](ID,USERID,STREET,CITY,
STATE,COUNTRY,PHONE,CAREATED_DATE,MODIFIED_DATE)
VALUES(1,2,'G-234','EAST DELHI','DELHI','INDIA',
'9089987767',GETDATE(),GETDATE())
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
SELECT * FROM Users
SELECT * FROM UserProfile
|
Result:
In
above script I put the UserDetails.Id value 1 so it’s generate an error and
rollback transaction now in following script I changed the unique value with 2
and execute again.
BEGIN
BEGIN TRANSACTION trn
BEGIN
TRY
--Insert
data in Users Table
INSERT INTO [dbo].[Users](ID,NAME,EMAIL,PWD,CREATED_DATE)
VALUES(2,'Dilip','dilip@gmail.com','dilip@123',GETDATE())
--Insert
data in UserProfile table where ID is already exist
INSERT INTO [dbo].[UserProfile](ID,USERID,STREET,CITY,
STATE,COUNTRY,PHONE,CAREATED_DATE,MODIFIED_DATE)
VALUES(2,2,'G-234','EAST DELHI','DELHI','INDIA',
'9089987767',GETDATE(),GETDATE())
COMMIT TRANSACTION
END
TRY
BEGIN
CATCH
ROLLBACK TRANSACTION
END
CATCH
END
SELECT * FROM Users
SELECT * FROM UserProfile
|
Result:
No comments:
Post a Comment
Please do not enter any spam link in the comment box.