Error Handling – TRY…CATCH
Error Handling block can be used to catch errors and commit or rollback the transaction accordingly as below:
Example: Error Handling with Transaction in SQL Server
BEGIN TRY
BEGIN TRANSACTION Trans_One
Insert into Customers (CustName,City,State,Country) Values ('Loganathan','Chennai','TN','India')
Insert into Orders Values (@@IDENTITY,'SANYO TV',1)
COMMIT TRANSACTION Trans_One
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION Trans_One
END CATCH
Below is the data updated after the above Insert statements
Output:
Try…Catch example
SQL Server Transaction
Multiple SQL queries in a group in SQL Server may need to execute and out of the SQL Queries in the group, some of the queries may fail. This could create data update issues and data consistency issues as a partial update could make a series of transactions incomplete. So, there is an SQL Statement called TRANSACTION with COMMIT and ROLLBACK, which can be used to handle incomplete or partial execution of queries in a Transaction. In this article, we will discuss the different Transaction commands with examples.
This article explores SQL Server Transactions, including syntax, error handling, and best practices. The model illustrates the key role played in data integrity, which is applied to financial transactions and inventory management. Whether you’re a developer or an administrator, understanding these principles is essential to building flexible database applications.