Transaction
is a set of T-SQL statements that are executed together as a unit like as a
single T-SQL statement. If all of these T-SQL statements executed successfully,
then a transaction is committed and the changes made by T-SQL statements
permanently saved to database. If any of these T-SQL statements within a
transaction fail, then the complete transaction is cancelled/ rolled back.
We
use transaction in that case, when we try to modify more than one tables/views
that are related to one another. Transactions affect SQL Server performance
greatly. Since When a transaction is initiated then it locks all the tables
data that are used in the transaction. Hence during transaction life cycle no
one can modify these tables’ data that are used by the transaction. The reason
behind the locking of the data is to maintain Data Integrity.
Types
of Transactions
1.
Implicit Transaction
Implicit transactions are
maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE),
DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under
the implicit transaction. If there is an error occurs within these statements
individually, SQL Server will roll back the complete statement.
2.
Explicit Transaction
Explicit transactions are
defined by programmers. In Explicit transaction we include the DML statements
that need to be execute as a unit. Since SELECT statements doesn’t modify data.
Hence generally we don’t include Select statement in a transaction.
CREATE
TABLE Department
(
DeptID int PRIMARY KEY,
DeptName varchar(50) NULL,
Location varchar(100) NULL,
)
GO
-----------------------------------------------------------------------------
CREATE
TABLE Employee
(
EmpID int PRIMARY KEY,
Name varchar(50) NULL,
Salary int NULL,
Address varchar(100) NULL,
DeptID int foreign Key references
Department(DeptID)
)
-------------------------------------------------------------------------------------------
--Now Insert
data
INSERT INTO
Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
GO
INSERT INTO
Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
SELECT *
FROM Department
SELECT *
FROM Employee
-----------------------------------------------------------------------------------------------
BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO
Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
INSERT INTO
Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRANSACTION
trans
END
END TRY
BEGIN CATCH
print
'Error Occured'
IF
@@TRANCOUNT >
0
BEGIN
ROLLBACK TRANSACTION
trans
END
END CATCH
--------------------------------------------------------------------------------
--Now
Select data to see transaction affects
SELECT *
FROM Employee
SELECT *
FROM Department
--------------------------------------------------------------------------------
BEGIN TRY
INSERT INTO
Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
IF @@TRANCOUNT
> 0
BEGIN
SAVE TRANSACTION
trans;
END
INSERT INTO
Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')
INSERT INTO
Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT
> 0
BEGIN
COMMIT TRANSACTION
trans
END
END TRY
BEGIN CATCH
print
'Error Occured'
IF @@TRANCOUNT
> 0
BEGIN
ROLLBACK TRANSACTION
trans
END
END CATCH
-----------------------------------------------------------------------------------------------
SELECT *
FROM Employee
SELECT *
FROM Department
