Delving into the realm of Microsoft SQL (MS SQL) transactions, two vital operations stand out: COMMIT and ROLLBACK. These functions are integral to maintaining data integrity and consistency when interacting with the database. Let's explore these operations with practical examples.
First, let's understand the workflow of a transaction. A transaction starts when you modify the data, and it's not finalized until you commit it. Rollback, on the other hand, is used to undo unfinalized transactions, thereby saving the database from unwanted changes.
Understanding the COMMIT Operation in MS SQL
The COMMIT operation is used to finalize a transaction, making all changes permanent. Once committed, the data is saved, and the transaction is rolled back to an idle state, ready for the next set of operations.
Here's a simple example. Consider a Payslip updation process in the Contoso University database. The updated payslip data needs to be committed to apply the changes permanently.
Commit Example in MS SQL
To commit a transaction, use the following SQL command:
BEGIN TRANSACTION
-- Your SQL operations here...
COMMIT
For example, in Contoso University, an HR manager might update payslip data as follows:
BEGIN TRANSACTION
UPDATE Employees
SET Salary = Salary * 1.10
WHERE Department = 'IT'
COMMIT
This will update the salary of all employees in the IT department by 10%. The COMMIT command makes this update permanent.
Implementing COMMIT in a Stored Procedure
In MS SQL, COMMIT can also be used within stored procedures. This is useful when dealing with complex multi-step data operations to ensure data consistency.
For instance, an HR manager might want to create a stored procedure for updating employees' payslips, incrementally applying a 10% salary increase over 3 years.
CREATE PROCEDURE UpdatePayslips(@increment: DECIMAL(5, 2))
AS
BEGIN
BEGIN TRANSACTION
UPDATE Employees SET Salary = Salary * (1 + @increment)
WHERE Department = 'IT'
COMMIT
END
In this stored procedure, @increment is a parameter determining the percentage increase. The COMMIT operation at the end makes sure the updated payslips are saved.
Understanding the ROLLBACK Operation in MS SQL
ROLLBACK is used to discard unfinalized transactions, or, in other words, to undo any changes made during a transaction. Rollback is important to prevent unwanted changes from being committed to the database.
Imagine an HR manager accidentally updates the IT department's salary with a wrong percentage. To undo this, they can use the ROLLBACK operation.
Rollback Example in MS SQL
Use the ROLLBACK statement to undo changes made in the current transaction:
BEGIN TRANSACTION
-- Your SQL operations here...
ROLLBACK
For example, to reverse an erroneous 10% salary increase in the IT department, the HR manager can use the following SQL:
BEGIN TRANSACTION
UPDATE Employees SET Salary = Salary / 1.10
WHERE Department = 'IT'
ROLLBACK
This will reset the salaries of all IT department employees to their original values, undoing the erroneous 10% increase.
Handling Exceptions with ROLLBACK
In MS SQL, ROLLBACK can be used to handle exceptions. If an error occurs and you don't want the transaction to proceed, you can use ROLLBACK to discard the unfinalized changes.
For instance, an HR manager wants to update payslips only if the new salary value is above the current one. If the new value is not higher, the update should be discarded. This can be achieved using a TRY...CATCH block:
BEGIN TRANSACTION
BEGIN TRY
UPDATE Employees
SET Salary = CASE WHEN NewSalary > Salary THEN NewSalary ELSE Salary END
WHERE Department = 'IT'
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY(view_error) AS ErrorSeverity
,ERROR_STATE(view_error) AS ErrorState
,ERROR_PROCEDURE(view_error) AS ErrorProcedure
,ERROR_LINE(view_error) AS ErrorLine
,ERROR_MESSAGE(view_error) AS ErrorMessage;
ROLLBACK
END CATCH
In this example, if the new salary is not higher than the current one, an error will occur, and the ROLLBACK operation will discard the transaction.
Mastering the art of COMMIT and ROLLBACK in MS SQL transactions can vastly improve data consistency and integrity. By committing validated transactions and rolling back invalid ones, you can ensure your database reflects only the changes you intended to make.