|
Execute the following SQL Server T-SQL example
scripts in SSMS Query Editor to demonstrate the ROLLBACK of a transaction.
When ROLLBACK TRANSACTION is executed UPDATE(s), INSERT(s) and DELETE(s) do not take.
-- SQL rollback transaction - SQL rollback transaction on error
SELECT PurchaseOrderID, ShipMethodID
FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]
WHERE PurchaseOrderID = 3412
/*
PurchaseOrderID ShipMethodID
3412 1
*/
BEGIN TRANSACTION
-- SQL update
UPDATE [AdventureWorks].[Purchasing].[PurchaseOrderHeader]
SET [ShipMethodID] = 4
,[TaxAmt] = 200.0
WHERE PurchaseOrderID = 3412
/***** Data only changed tentatively in this session (connection) only *****/
/***** Other connections still see [ShipMethodID]: 1 *****/
SELECT PurchaseOrderID, ShipMethodID
FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]
WHERE PurchaseOrderID = 3412
/*
PurchaseOrderID ShipMethodID
3412 4
*/
-- COMMIT TRANSACTION would make the change final in the database
ROLLBACK TRANSACTION
-- Data in table unchanged after rollback
SELECT PurchaseOrderID, ShipMethodID
FROM [AdventureWorks].[Purchasing].[PurchaseOrderHeader]
WHERE PurchaseOrderID = 3412
/*
PurchaseOrderID ShipMethodID
3412 1
*/
------------
-- SQL Server rollback transaction
-- SQL Server rollback transaction on error
USE AdventureWorks;
SELECT ProductID, ProductName=Name, ListPrice
FROM Production.Product
WHERE ProductID = 800
/*
ProductID ProductName ListPrice
800 Road-550-W Yellow, 44 1120.49
*/
BEGIN TRANSACTION
UPDATE Production.Product SET ListPrice = ListPrice + 1.0
WHERE ProductID = 800
IF @@ERROR != 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
UPDATE Production.Product SET ProductID = 801
WHERE ProductID = 800
IF @@ERROR != 0
BEGIN
-- ROLLBACK occurs here for both UPDATEs
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
GO
/* Error message
Msg 8102, Level 16, State 1, Line 20
Cannot update identity column 'ProductID'.
*/
-- ListPrice update did not take due to ROLLBACK
SELECT ProductID, ProductName=Name, ListPrice
FROM Production.Product
WHERE ProductID = 800
/*
ProductID ProductName ListPrice
800 Road-550-W Yellow, 44 1120.49
*/
------------
|
