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.
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]
SET NOCOUNT ON;
INSERT INTO Employee
(FirstName, LastName, Age, Active, Salary)
(@FirstName, @LastName, @Age, @Active, @Salary)
DECLARE @CurrentId int
SET @CurrentId = SCOPE_IDENTITY()
INSERT INTO Department
(Designation, Department, EmpID)
(@Designation, @Department, @CurrentId )
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.