Error Management in SQL Using T-SQL, Begin Transaction Commit and RollBack.

Error in more than one transaction or statement in sql can be handled using Transaction in T-SQL.
example:
begin TRANsaction
IF(@Running=1)
BEGIN
UPDATE tblFiscalyearMaster SET
Running=0
,ModifiedBy = @CreatedBy
,ModifiedDate =    GETDATE()
END

INSERT INTO tblFiscalyearMaster(FiscalyearID,FiscalyearName,FiscalyearCode,StartDate,EndDate,Running,Remarks,CreatedBy)
VALUES(@FiscalyearID,@FiscalyearName,@FiscalyearCode,@StartDate,@EndDate,@Running,@Remarks,@CreatedBy)

IF(@@ERROR<>0)
begin
 ROLLBACK
return
end

COMMIT
end

In this Example,
Begin Transaction is the starting of the transaction and all the statements and queries are executed between  begin TRANsaction and IF(@@ERROR<>0).
If statement check for error in the statements in between. if found  ROLLBACK Rolls back to the previous state cancelling all the changes coused by the in between queries.

Finally , if no errors are found COMMIT statement finalizes the Transaction.
This, is an easy method of achieving consistency of data or error management in sql.

SQL Server Transactions Management

A 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.

Transactions Example

1.   CREATE TABLE Department

2.  (

3.   DeptID int PRIMARY KEY,

4.   DeptName varchar(50) NULL,

5.   Location varchar(100) NULL,

6.   )

7.  GO

8.   CREATE TABLE Employee

9.  (

10. EmpID int PRIMARY KEY,

11. Name varchar(50) NULL,

12. Salary int NULL,

13. Address varchar(100) NULL,

14. DeptID int foreign Key references Department(DeptID)

15.)

1.   --Now Insert data

2.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')

3.  GO

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)

5.  SELECT * FROM Department

6.  SELECT * FROM Employee

1.   BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

5.   IF @@TRANCOUNT > 0

6.   BEGIN COMMIT TRANSACTION trans

7.   END

8.  END TRY

9.  BEGIN CATCH

10. print 'Error Occured'

11. IF @@TRANCOUNT > 0

12. BEGIN ROLLBACK TRANSACTION trans

13. END

14.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department

1.   --Transaction with Save Point BEGIN TRANSACTION trans

2.  BEGIN TRY

3.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')

4.  IF @@TRANCOUNT > 0

5.   BEGIN SAVE TRANSACTION trans;

6.   END

7.  INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi')

8.  INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)

9.  IF @@TRANCOUNT > 0

10. BEGIN COMMIT TRANSACTION trans

11. END

12.END TRY

13.BEGIN CATCH

14. print 'Error Occured'

15.IF @@TRANCOUNT > 0

16. BEGIN ROLLBACK TRANSACTION trans

17. END

18.END CATCH

1.   --Now Select data to see transaction affects

2.  SELECT * FROM Employee

3.  SELECT * FROM Department