Transactions in Transact SQL
A transaction can be defined as a set of instructions that work as a unit, so if one part fails the whole process fails in its entirety, reversing all the changes made in the database.
To declare a transaction, we start with the following syntax:
The shortened sentence will also be valid:
Therefore a transaction validates all the statements it contains as a single set or unit of work. If all the statements work, the changes to the database are committed and made permanent. If any statement fails, all changes must be rolled back.
Therefore, it is very common for transactions to internally contain error handling structures such as
TRY...CATH, to commit or revert changes to the database. If the code fails, the error will be caught in the
CATCH and we will proceed to revert all the changes to the database. If all the
TRY instructions work correctly, the changes will be saved.
For more information regarding error control and handling, visit the error management section.
The control of saving and overlaying the changes, respectively, will be done using the following instructions:
COMMIT TRANSACTION: commits and makes database changes permanent if the transaction completes successfully.
ROLLBACK TRANSACTION:: undoes and reverts the changes of a transaction when an error occurs during it, until the start of the transaction or until a return point within it.
SAVE TRANSACTION: Sets a savepoint within a transaction..
To understand better how a transaction works and the concepts of
ROLLBACK, suppose we want to add two new
customers within a transaction:
BEGIN TRANSACTION; INSERT INTO Customers VALUES ('Gerald', 'Villegas', '1195 White Avenue', NULL); INSERT INTO Customers VALUES ('Margaret', NULL, '3191 Wescam Court', NULL); COMMIT TRANSACTION;
If we execute the code above, it returns the following error message:
Cannot insert the value NULL into column ‘lastName’, table ‘store.dbo.Customers’; column does not allow nulls. INSERT fails.
However, if we consult our
Customers table, we can see that the first record referring to: Gerald Villegas has been correctly inserted:
|1019||Gerald||Villegas||1195 White Avenue||NULL|
From this example, we deduce that we are not using transaction correctly, since we are not taking into account the errors to undo the changes with the
In the following example, we are going to see an error management and the
ROLLBACK statement, so that in case the insertion of a record fails, the entire process fails.
BEGIN TRAN; BEGIN TRY INSERT INTO Customers VALUES ('Robert', 'Austin', '339 Tuna Street', NULL); INSERT INTO Customers VALUES ('Margaret', NULL, '3191 Wescam Court', NULL); COMMIT TRAN; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; ROLLBACK TRAN END CATCH;
|515||16||2||NULL||4||Cannot insert the value NULL into column ‘lastName’, table ‘store.dbo.Customers’; column does not allow nulls. INSERT fails.|
In this case, although the first
INSERT INTO of the registry: Robert Austin works correctly, when the second one fails we capture the error in the
CATCH and do
ROLLBACK of the transaction. Therefore, if we consult the
Customers table, we can verify that no record has been inserted.