Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Transaction in SQL

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 295
    Comment on it

    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)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: