DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to ROLLBACK a transaction?

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

*/

------------


 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE