SQL : TRANSACTION

Transaction processing is a concept in SQL that allows you to execute a query or rollback the changes if something goes wrong.  A way of enforcing the data integrity of the database.  As such, you can only rollback INSERT, UPDATE, and DELETE.  Not that there's any use in rolling back a SELECT statement because there's no change in data.

The following is how you would wrap a transaction around a DELETE statement:

BEGIN TRANSACTION
DELETE Products WHERE ProductID = 87
COMMIT TRANSACTION

The above query will only execute if there are no errors, if there's an error the transaction will be rolled back. That's it, that's the whole concept of what a transaction is, if there are no errors then you should get the following message.

(1 row(s) affected)

If you are dealing with multiple statements then you can use the SAVE TRANSACTION, SAVE TRANSACTION allows you to create a placeholder so that you can rollback a transaction at a checkpoint.



For example if you were to INSERT a new order you would need to insert a new record into the Customers table and then the Orders table and then the OrderDetails table. You wouldn't want to rollback the whole transaction if something goes wrong. You might want to record the customer who tries to order your product, but couldn't and then have your customer representative do a follow up to complete the transaction if something goes wrong.

Here is how you would do a partial rollback:

BEGIN TRANSACTION
INSERT INTO Customers(CustomerID,CompanyName)
VALUES ('ACME','ACME Company')
SAVE TRANSACTION StartOrder
INSERT INTO Orders(OrderID,CustomerID)
VALUES (10999,'ACME')
IF @@Error <> 0 ROLLBACK TRANSACTION StartOrder
INSERT INTO [Order Details](OrderID,ProductID,UnitPrice,Quantity,Discount)
VALUES (10999,14,23.25,1,0)
COMMIT TRANSACTION

The above query rolls back the query if there's an error. Notice the IF @@Error condition.

=