SQL Server

Best practices to use exception handling with transaction in SQL Server

Only keeping the T-SQL scripts inside the Begin Transaction and Commit Transaction code doesn’t ensure that you have implemented the transaction.

Consider the following two tables

 

CREATE TABLE dbo.SimpleOrders(

orderid int IDENTITY(1,1) NOT NULL PRIMARY KEY,

custid int NOT NULL ,

empid int NOT NULL ,

orderdate datetime NOT NULL

);

GO

 

CREATE TABLE dbo.SimpleOrderDetails(

orderid int NOT NULL FOREIGN KEY REFERENCES dbo.SimpleOrders(orderid),

productid int NOT NULL ,

unitprice money NOT NULL,

qty smallint NOT NULL,

CONSTRAINT PK_OrderDetails PRIMARY KEY (orderid, productid)

);

GO

 

Here orderid column of dbo.SimpleOrderDetails is a Foreign Key to orderid column in dbo.SimpleOrders which is an identity column.

In the following script I am writing two insert statements within a transaction

 

BEGIN TRANSACTION

INSERT INTO dbo.SimpleOrders(custid, empid, orderdate)

VALUES (68,9,‘2006-07-15’);

INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty)

VALUES (99999,2,15.20,20);   –This command will fail

COMMIT TRANSACTION

 

The second insert statement will fail because of Foreign Key constraint. The value 99999 doesn’t exist in orderid column of SimpleOrders tables.

Now Question to you :- There were two insert statements in the above transaction. First statement was executed successfully but second failed.Will the first statement rollback?

If you think the answer is YES then it is not TRUE. Although second insert statement is failing but SQL server will still execute COMMIT TRANSACTION command written just below the second insert statement and first statement will get committed in the example discussed above.

To make sure first statement also rollbacks. I am rewriting the code using Exceptional Handling.

 

BEGIN TRY

BEGIN TRANSACTION

INSERT INTO dbo.SimpleOrders(custid, empid, orderdate)

VALUES (68,9,‘2006-07-15’);

INSERT INTO dbo.SimpleOrderDetails(orderid,productid,unitprice,qty)

VALUES (99999, 2,15.20,20);

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

SELECT ERROR_NUMBER() AS ErrNum, ERROR_MESSAGE() AS ErrMsg;

–You may log this data into ErrorLog Table for reviewing the error later

END CATCH;

 

When the error will occur in the TRY block, it will invoke the CATCH block and then the command ROLLBACK TRANSACTION within the CATCH block will rollback the transaction.

 

Leave a Reply