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.