A transaction is used to check whether all SQL statements get executed successfully or not. A Single transaction starts with a particular statement and ends after the completion of all the SQL statements executed successfully. If all SQL statements fail then the transaction will also fail.
A single transaction gives a result either success or failure. The transaction is also got failed when we execute the incomplete SQL statements.
Generally, we use a transaction where we want to undo/rollback the changes when an error occurs due to one of the SQL statement.
For example:
We have a stored procedure for inserting a data into Employee and Department table. Our assumption should be that If any one of the insert statement fails then no data will be inserted in any one of the tables.
CREATE PROCEDURE [dbo].[InsertEmployeeAndDepartment]
@FirstName varchar(50),
@LastName varchar(50),
@Age smallint,
@Active bit,
@Salary money,
@Designation,
@Department
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN
BEGIN TRY
INSERT INTO Employee
(FirstName, LastName, Age, Active, Salary)
VALUES
(@FirstName, @LastName, @Age, @Active, @Salary)
DECLARE @CurrentId int
SET @CurrentId = SCOPE_IDENTITY()
INSERT INTO Department
(Designation, Department, EmpID)
VALUES
(@Designation, @Department, @CurrentId )
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
Conclusion:
In above example, we create a transaction using "BEGIN TRAN' statement. We are also using a BEGIN TRY statement outside the INSERT statements to throw an errors. We use a COMMIT TRANSACTION statement to check whether data is saved into the database permanently or not. If any error arises in any one of the INSERT statements then that error will go in catch block to executes the ROLLBACK TRANSACTION as this ROLLBACK transaction will rollback/undo all the change done in the database.
0 Comment(s)