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.
CREATE TABLE [Employee](
[EmpNo] [int] IDENTITY(1,1)PRIMARY KEY,
[EmpName] [varchar](50) NOT NULL,
[Salary] [decimal](18, 0) NOT NULL,
[DeptName] [varchar](50) NOT NULL,
[Designation] [varchar](50) NOT NULL
)
SELECT * FROM [Employee]
CREATE TABLE [PRODUCT]
(
[PRODUCT_ID] [bigint] IDENTITY(1,1) PRIMARY KEY,
[PRODUCT_NAME] [varchar](200) NULL,
[PRICE] [decimal](10, 2) NULL
)
SELECT * FROM [PRODUCT]
BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO [dbo].[Employee]([EmpName],[Salary],[DeptName],[Designation])VALUES('Manoj kumar',20000,'IT','Programmer')
INSERT INTO [PRODUCT]([PRODUCT_ID],[PRODUCT_NAME],[PRICE])VALUES(2,'Mocro Max',200)
-- here we appears error because we insert identity key value
-- first statetment successfully but second statetment not successful so transaction ROLLBACK
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 [PRODUCT]
Liked By
Write Answer
How To Use Transactions SQL Server
Join MindStick Community
You have need login or register for voting of answers or question.
Anonymous User
09-Mar-2016Transaction 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.